| = XA DataSource Configuration |
| :index-group: Datasource |
| :jbake-date: 2019-07-12 |
| :jbake-type: page |
| :jbake-status: published |
| |
| == 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 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> |
| ``` |