Preloader image

XA DataSource Configuration

Introduction

XA datasources are able to participate in global transactions involving more than one resource - for example, scenarios where a transaction needs to encompass connections to two different databases,, or a database and a JMS resource.

The global transaction manager will provide a two phase commit for all the resources enlisted in the transaction - if any of the commit operations fail, then all the resources in the global transaction will be rolled back.

JTA can still be used with non-XA datasources, however the datasource will use a local transaction as opposed to a global transaction.

JDBC drivers providing XA capabilities provide an implementation of javax.sql.XADataSource. This makes them a little more tricky to configure than non-XA datasources. The general technique is to configure an instance of the vendor-provided XADataSource implementation, and to then point the usual non-XA resource at this instance. Sounds complex? Let’s walk through an example. We’ll also provide tested example configs for a number of well known databases at the end of this document.

Example

In this example, we’ll look at MySQL. First off, download the MySQL driver from: https://dev.mysql.com/downloads/connector/j/. Once you have the .jar file, add it to the TomEE lib directory. This driver provides the com.mysql.cj.jdbc.MysqlXADataSource class. The properties that need to be configured vary between datasources, so we’ll need to get a list of the properties and work out the values to set.

From the TomEE bin directory, execute the following command: ./tomee.sh setters -c com.mysql.cj.jdbc.MysqlXADataSource on *nix systems, or

tomee.bat setters -c com.mysql.cj.jdbc.MysqlXADataSource on Windows systems.

This will give a complete list of paramaters that are available for the XA datasource. We’ll simply use the URL paramater for the datasource.

  <Resource id="demo/jdbc/XADataSourceXA" type="XADataSource" class-name="com.mysql.cj.jdbc.MysqlXADataSource">
    Url jdbc:mysql://192.168.37.202:3306/movie
  </Resource>

Next, we create a JtaManaged datasource as we normally would, and point it to the XA datasource using the XaDataSource attribute.

  <Resource id="demo/jdbc/XADataSource" type="DataSource">
    XaDataSource demo/jdbc/XADataSourceXA
    JdbcDriver  com.mysql.cj.jdbc.Driver
    JdbcUrl jdbc:mysql://192.168.37.202:3306/movie
    username root
    password my-secret-pw
    JtaManaged true
    InitialSize 10
    MaxActive 128
    MaxIdle 25
    MinIdle 10
    AccessToUnderlyingConnectionAllowed true
    TestOnBorrow false
    TestWhileIdle true
    TimeBetweenEvictionRuns 1 minute
    MaxWaitTime 0 seconds
    ValidationQuery select 1
  </Resource>

And finally, a non-JTA managed datasource as well:

  <Resource id="demo/jdbc/XADataSourceUnmanaged" type="DataSource">
    JdbcDriver com.mysql.cj.jdbc.Driver
    JdbcUrl jdbc:mysql://192.168.37.202:3306/movie
    UserName root
    password my-secret-pw
    JtaManaged false
    InitialSize 10
    MaxActive 100
    MaxIdle 50
    MinIdle 10
    AccessToUnderlyingConnectionAllowed true
    TestOnBorrow false
    TestWhileIdle true
    TimeBetweenEvictionRuns 1 minute
    MaxWaitTime 0 seconds
    ValidationQuery select 1
  </Resource>

Sample configs

Oracle

  <Resource id="demo/jdbc/XADataSource" type="DataSource">
    XaDataSource demo/jdbc/XADataSourceXA
    JdbcDriver  oracle.jdbc.OracleDriver
    JdbcUrl jdbc:oracle:thin:@//192.168.37.214:1521/XE
    username system
    password my-cool-password
    JtaManaged true
    InitialSize 10
    MaxActive 128
    MaxIdle 25
    MinIdle 10
    AccessToUnderlyingConnectionAllowed true
    TestOnBorrow false
    TestWhileIdle true
    TimeBetweenEvictionRuns 1 minute
    MaxWaitTime 0 seconds
    PoolPreparedStatements true
    MaxOpenPreparedStatements 1024
    ValidationQuery select 1 from dual
  </Resource>

  <Resource id="demo/jdbc/XADataSourceXA" type="XADataSource" class-name="oracle.jdbc.xa.client.OracleXADataSource">
    Url jdbc:oracle:thin:@//192.168.37.214:1521/XE
  </Resource>

  <Resource id="demo/jdbc/XADataSourceUnmanaged" type="DataSource">
    JdbcDriver oracle.jdbc.OracleDriver
    JdbcUrl jdbc:oracle:thin:@//192.168.37.214:1521/XE
    UserName system
    password my-cool-password
    JtaManaged false
    InitialSize 10
    MaxActive 100
    MaxIdle 50
    MinIdle 10
    AccessToUnderlyingConnectionAllowed true
    TestOnBorrow false
    TestWhileIdle true
    TimeBetweenEvictionRuns 1 minute
    MaxWaitTime 0 seconds
    PoolPreparedStatements true
    MaxOpenPreparedStatements 1024
    ValidationQuery select 1 from dual
  </Resource>

Microsoft SQL Server

  <Resource id="demo/jdbc/XADataSource" type="DataSource">
    XaDataSource demo/jdbc/XADataSourceXA
    JdbcDriver  com.microsoft.sqlserver.jdbc.SQLServerDriver
    JdbcUrl jdbc:sqlserver://yourserver.database.windows.net:1433;database=test
    username sa
    password my-secret-pw1
    JtaManaged true
    InitialSize 10
    MaxActive 128
    MaxIdle 25
    MinIdle 10
    AccessToUnderlyingConnectionAllowed true
    TestOnBorrow false
    TestWhileIdle true
    TimeBetweenEvictionRuns 1 minute
    MaxWaitTime 0 seconds
    PoolPreparedStatements true
    MaxOpenPreparedStatements 1024
    ValidationQuery select 1
  </Resource>

  <Resource id="demo/jdbc/XADataSourceXA" type="XADataSource" class-name="com.microsoft.sqlserver.jdbc.SQLServerXADataSource">
    URL jdbc:sqlserver://yourserver.database.windows.net:1433;database=test
  </Resource>

  <Resource id="demo/jdbc/XADataSourceUnmanaged" type="DataSource">
    JdbcDriver com.microsoft.sqlserver.jdbc.SQLServerDriver
    JdbcUrl jdbc:sqlserver://yourserver.database.windows.net:1433;database=test
    UserName sa
    password my-secret-pw1
    JtaManaged false
    InitialSize 10
    MaxActive 100
    MaxIdle 50
    MinIdle 10
    AccessToUnderlyingConnectionAllowed true
    TestOnBorrow false
    TestWhileIdle true
    TimeBetweenEvictionRuns 1 minute
    MaxWaitTime 0 seconds
    PoolPreparedStatements true
    MaxOpenPreparedStatements 1024
    ValidationQuery select 1
  </Resource>

Please note that using XA with Microsoft SQL Server requires the MS DTC to be configured correctly, and sqljdbc_xa.dll to be installed. For instructions, please see this Microsoft article: https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions?view=sql-server-2017

MySQL

  <resources>
    <Resource id="demo/jdbc/XADataSource" type="DataSource">
      XaDataSource demo/jdbc/XADataSourceXA
      JdbcDriver  com.mysql.cj.jdbc.Driver
      JdbcUrl jdbc:mysql://192.168.37.202:3306/movie
      username root
      password my-secret-pw
      JtaManaged true
      InitialSize 10
      MaxActive 128
      MaxIdle 25
      MinIdle 10
      AccessToUnderlyingConnectionAllowed true
      TestOnBorrow false
      TestWhileIdle true
      TimeBetweenEvictionRuns 1 minute
      MaxWaitTime 0 seconds
      ValidationQuery select 1
    </Resource>

    <Resource id="demo/jdbc/XADataSourceXA" type="XADataSource" class-name="com.mysql.cj.jdbc.MysqlXADataSource">
      Url jdbc:mysql://192.168.37.202:3306/movie
    </Resource>

    <Resource id="demo/jdbc/XADataSourceUnmanaged" type="DataSource">
      JdbcDriver com.mysql.cj.jdbc.Driver
      JdbcUrl jdbc:mysql://192.168.37.202:3306/movie
      UserName root
      password my-secret-pw
      JtaManaged false
      InitialSize 10
      MaxActive 100
      MaxIdle 50
      MinIdle 10
      AccessToUnderlyingConnectionAllowed true
      TestOnBorrow false
      TestWhileIdle true
      TimeBetweenEvictionRuns 1 minute
      MaxWaitTime 0 seconds
      ValidationQuery select 1
    </Resource>
  </resources>

PostgreSQL

  <resources>
    <Resource id="demo/jdbc/XADataSource" type="DataSource">
      XaDataSource demo/jdbc/XADataSourceXA
      JdbcDriver org.postgresql.Driver
      JdbcUrl jdbc:postgresql://192.168.37.200:5432/movie
      username postgres
      password mysecretpassword
      JtaManaged true
      InitialSize 10
      MaxActive 128
      MaxIdle 25
      MinIdle 10
      AccessToUnderlyingConnectionAllowed true
      TestOnBorrow false
      TestWhileIdle true
      TimeBetweenEvictionRuns 1 minute
      MaxWaitTime 0 seconds
      PoolPreparedStatements true
      MaxOpenPreparedStatements 1024
      ValidationQuery select 1
    </Resource>

    <Resource id="demo/jdbc/XADataSourceXA" type="XADataSource" class-name="org.postgresql.xa.PGXADataSource">
      URL jdbc:postgresql://192.168.37.200:5432/movie
    </Resource>

    <Resource id="demo/jdbc/XADataSourceUnmanaged" type="DataSource">
      JdbcDriver org.postgresql.Driver
      JdbcUrl jdbc:postgresql://192.168.37.200:5432/movie
      UserName postgres
      password mysecretpassword
      JtaManaged false
      InitialSize 10
      MaxActive 100
      MaxIdle 50
      MinIdle 10
      AccessToUnderlyingConnectionAllowed true
      TestOnBorrow false
      TestWhileIdle true
      TimeBetweenEvictionRuns 1 minute
      MaxWaitTime 0 seconds
      PoolPreparedStatements true
      MaxOpenPreparedStatements 1024
      ValidationQuery select 1
    </Resource>
  </resources>

Derby

  <resources>
    <Resource id="movieDatabaseXA" type="javax.sql.XADataSource" class-name="org.apache.derby.jdbc.ClientXADataSource">
      DatabaseName=testdb
      CreateDatabase=create
      ServerName=localhost
      PortName=1527
      UserName=admin
      Password=admin
    </Resource>
    <Resource id="movieDatabase" type="DataSource">
      JdbcDriver org.apache.derby.jdbc.ClientDriver
      JdbcUrl  jdbc:derby://localhost:1527/testdb;create=true
      XaDataSource=movieDatabaseXA
      UserName=admin
      Password=admin
      ValidationQuery=values 1
      InitialSize=2
      MaxActive=128
      MaxIdle=25
      MinIdle=10
      TestWhileIdle=true
      TestOnBorrow=false
      TestOnReturn=false
      AccessToUnderlyingConnectionAllowed=true
      TimeBetweenEvictionRuns=1 minute
      MaxWaitTime=0 seconds
      JtaManaged=true
    </Resource>
    <Resource id="movieDatabaseUnmanaged" type="DataSource">
      JdbcDriver org.apache.derby.jdbc.ClientDriver
      JdbcUrl  jdbc:derby://localhost:1527/testdb;create=true
      UserName admin
      Password admin
      ValidationQuery=values 1
      InitialSize=2
      MaxActive=128
      MaxIdle=25
      MinIdle=10
      TestWhileIdle=true
      TestOnBorrow=false
      TestOnReturn=false
      AccessToUnderlyingConnectionAllowed=true
      TimeBetweenEvictionRuns=1 minute
      MaxWaitTime=0 seconds
      JtaManaged=false
    </Resource>
  </resources>