Skip to content

JDBC

JDBC connection pools, driver and DataSource classes per database, and local versus XA transactions.

2 min read

JDBC Connection Pool

Resource Type:

  • java.sql.Driver
  • javax.sql.DataSource
  • javax.sql.ConnectionPoolDataSource
  • javax.sql.XADataSource

Datasource Classname:

Driver Classname:

Driver and DataSource class

Implements Class
java.sql.Driver org.postgresql.Driver
javax.sql.DataSource org.postgresql.ds.PGSimpleDataSource
javax.sql.ConnectionPoolDataSource org.postgresql.ds.PGConnectionPoolDataSource
javax.sql.XADataSource org.postgresql.xa.PGXADataSource

JDBC Driver Mapping

Database JDBC driver XA driver JDBC Url Reference
db2 com.ibm.db2.jcc.DBDriver com.ibm.db2.jcc.DB2XADataSource jdbc:db2://localhost:50000/MYDB:user=dbadm;password=dbadm;
derby org.apache.derby.jdbc.ClientDriver org.apache.derby.jdbc.ClientXADataSource jdbc:derby://localhost:1527/myDB, jdbc:derby:memory:myDB;create=true
h2 org.h2.Driver org.h2.jdbcx.JdbcDataSource jdbc:h2:tcp://localhost/~/test, jdbc:h2:mem:myDB
mariadb org.mariadb.jdbc.Driver org.mariadb.jdbc.MySQLDataSource jdbc:mariadb://localhost:3306/test
mssql com.microsoft.sqlserver.jdbc.SQLServerDriver com.microsoft.sqlserver.jdbc.SQLServerXADataSource jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks
mysql com.mysql.cj.jdbc.Driver com.mysql.cj.jdbc.MysqlXADataSource jdbc:mysql://localhost:3306/test
oracle oracle.jdbc.driver.OracleDriver oracle.jdbc.xa.client.OracleXADataSource jdbc:oracle:thin:@localhost:1521/ORCL_SVC
postgresql org.postgresql.Driver org.postgresql.xa.PGXADataSource jdbc:postgresql://localhost/test

Data Source

mysql

  • Local Transaction:
    • datasource-classname: com.mysql.cj.jdbc.MysqlDataSource
    • res-type: javax.sql.DataSource
    • driver-class: com.mysql.cj.jdbc.Driver
  • XA Transaction:
    • datasource-classname: com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
    • res-type: javax.sql.XADataSource
    • driver-class: com.mysql.cj.jdbc.MysqlXADataSource

Example:

<jdbc-connection-pool
datasource-classname="com.mysql.cj.jdbc.MysqlDataSource"
name="app_mysql_pool"
res-type="javax.sql.DataSource">
<property name="user" value="dbuser"/>
<property name="password" value="dbpassword"/>
<property name="allowPublicKeyRetrieval" value="true"/>
<property name="useSSL" value="false"/>
<property name="url" value="jdbc:mysql://localhost:3306/dbmaster?zeroDateTimeBehavior=convertToNull"/>
</jdbc-connection-pool>

oracle

  • Local Transaction:
    • datasource-classname: oracle.jdbc.pool.OracleDataSource
    • res-type: javax.sql.DataSource
  • XA Transaction:
    • datasource-classname: oracle.jdbc.xa.client.OracleXADataSource
    • res-type: javax.sql.XADataSource

Example:

<jdbc-connection-pool
datasource-classname="oracle.jdbc.pool.OracleDataSource"
name="app_oracle_pool"
res-type="javax.sql.DataSource">
<property name="user" value="dbuser"></property>
<property name="password" value="dbpassword"></property>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:ORCL"></property>
</jdbc-connection-pool>

mssql

  • Local Transaction:
    • datasource-classname: com.microsoft.sqlserver.jdbc.SQLServerDataSource
    • res-type: javax.sql.DataSource
  • XA Transaction:
    • datasource-classname: com.microsoft.sqlserver.jdbc.SQLServerXADataSource
    • res-type: javax.sql.XADataSource

Example:

<jdbc-connection-pool
datasource-classname="com.microsoft.sqlserver.jdbc.SQLServerDataSource"
name="app_mssql_pool"
res-type="javax.sql.DataSource">
<property name="User" value="${ENV=MY_APP_DATABASE_USER}"></property>
<property name="Password" value="${ENV=MY_APP_DATABASE_PASS}"></property>
<property name="DatabaseName" value="${ENV=MY_APP_DATABASE_NAME}"></property>
<property name="ServerName" value="${ENV=MY_APP_DATABASE_SERVER}"></property>
<property name="PortNumber" value="1433"></property>
</jdbc-connection-pool>

postgres

  • Local Transaction:
    • datasource-classname: org.postgresql.ds.PGSimpleDataSource
    • res-type: javax.sql.DataSource
  • XA Transaction:
    • datasource-classname: org.postgresql.xa.PGXADataSource
    • res-type: javax.sql.XADataSource

Example:

<jdbc-connection-pool
datasource-classname="org.postgresql.xa.PGXADataSource"
name="app_postgresql_pool"
res-type="javax.sql.XADataSource">
<property name="user" value="postgres"/>
<property name="password" value="postgres"/>
<property name="URL" value="jdbc:postgresql://localhost:5432/postgres"/>
<property name="driverClass" value="org.postgresql.Driver"/>
</jdbc-connection-pool>

derby

Client: org.apache.derby.jdbc.ClientDataSource, org.apache.derby.jdbc.ClientXADataSource Embedded: org.apache.derby.jdbc.EmbeddedDataSource, org.apache.derby.jdbc.EmbeddedXADataSource

  • Local Transaction:
    • datasource-classname: org.apache.derby.jdbc.ClientDataSource
    • res-type: javax.sql.DataSource
  • XA Transaction:
    • datasource-classname: org.apache.derby.jdbc.ClientXADataSource
    • res-type: javax.sql.XADataSource

Example:

<jdbc-connection-pool
datasource-classname="org.apache.derby.jdbc.ClientXADataSource"
name="app_derby_pool"
res-type="javax.sql.XADataSource">
<property name="portNumber" value="1527"/>
<property name="password" value="APP"/>
<property name="user" value="APP"/>
<property name="serverName" value="localhost"/>
<property name="databaseName" value="sun-appserv-samples"/>
<property name="connectionAttributes" value="; create=true"/>
</jdbc-connection-pool>

Q&A

What is difference between local, xa and no transactions?

  • Local (Non-XA) Transaction - means in a single transaction we can do multiple operations on a single database. To be clearer all these multiple operations use the same Adapter connection explicit commit required.
  • NO Transaction - Auto commit all transactions.
  • XA Transaction - means in a single transaction we can do multiple operations on different databases. To be clearer all these multiple operations use different Adapter connection. Generally used for distributed database.

An XA transaction involves a coordinating transaction manager, with one or more databases (or other resources, like JMS) all involved in a single global transaction. Non-XA transactions have no transaction coordinator, and a single resource is doing all its transaction work itself.

Use No transaction for select and local transaction for any DML (insert, delete, update) operations to handle the transaction boundary (via start, commit and rollback).

Related articles

1 min read

SQL

SQL joins explained — inner, left, right and full outer — with examples.

  • Databases
1 min read

Database Migration

Migrating a MySQL database to PostgreSQL with pgloader, step by step.

  • Databases
3 min read

Jakarta

Jakarta EE namespaces, project settings and what changed in Jakarta EE 10 and 11.

  • Java & Jakarta EE

Type to search the whole site.