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

JDBC Driver Mapping

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

<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:

1
2
3
4
5
6
7
8

<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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

<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:

1
2
3
4
5
6
7
8
9

<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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

<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).