docs/configuring-datasources-xa.adoc - tomee - Git at Google

 = 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>
 ```
	= 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>
	```