Home > Database Programming > An Introduction to Bitronix JTA Transaction Manager with MySQL

An Introduction to Bitronix JTA Transaction Manager with MySQL

The Java Transaction API (JTA) allows applications to perform distributed transactions —to access and update systems having multiple transactional resources: databases, message queues, custom resource, or resources accessed from multiple processes, perhaps on multiple hosts, as participants in a single transaction.

The purpose of this article is to provide a high-level overview of Bitronix JTA Transaction Manager with mySQL XA datasource.

Steps involve creating a very basic JTA application using Bitronixs Transaction Manager (BTM) are as follows:

  • Step 1 – System Setup: You need to perform predefine set of operations before writing code.
    1. Make sure you have latest version of Java install on your system
    2. Install MySQL 5.1 or higher version. If can download MySQL from their web site.
    3. Download list of JAR files mentioned below:
      1. btm-2.1.0.jar (essential library for BTM)
      2. slf4j-api-1.5.10.jar
      3. slf4j-jdk14-1.5.10.jar
      4. geronimo-jta_1.1_spec-1.1.1.jar
      5. mysql-connector-java-5.1.10-bin.jar
  • Step 2 – MySQL Setup: The following steps need to perform in order to run your application:
    1. Create a database using the given command:
      					CREATE DATABASE javatest;
      				
    2. Create a user using the following command:
      					CREATE USER 'javauser'@'%' IDENTIFIED BY 'javadude';
      				
    3. Create a student table in javatest database like mentioned below:
      				CREATE TABLE student 
      				( 
      				  roll_number INT, 
      				  name VARCHAR(20), 
      				  class VARCHAR(5), 
      				  section VARCHAR(5)
      				);
      				
  • Step 3 – Setup project: The example I have shown here is developed in Eclipse IDE. Steps required to setup project in Eclipse are pretty straightforward:
    1. Create a new Java project.
    2. Add all the Downloaded JAR files mentioned above in its classpath.
  • Step 4 – Write code: Now as you are done with all these steps finally you are ready to write code and test the application:
    1. Create a Java class BTMWithMySQL:
      package jtatest;
      
      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import bitronix.tm.BitronixTransactionManager;
      import bitronix.tm.TransactionManagerServices;
      import bitronix.tm.resource.jdbc.PoolingDataSource;
      
      /**
       * @author Bikash
       *
       */
      public class BTMWithMySQL {
      
      	private static final String DATABASE = "javatest";
      	private static final String USER_NAME = "javauser";
      	private static final String PASSWORD = "javadude";
      	
      	private static final String INSERT_QUERY = "insert into student(roll_number,name,class,section) values (?,?,?,?)";
      	private static final String SELECT_QUERY = "select * from student";
      
      	public static void main(String[] args) {
      		
      		PoolingDataSource mySQLDS = new PoolingDataSource();
      		mySQLDS.setClassName("com.mysql.jdbc.jdbc2.optional.MysqlXADataSource");
      		mySQLDS.setUniqueName("mySqlBtm");
      		mySQLDS.setMaxPoolSize(3);
      		mySQLDS.getDriverProperties().setProperty("databaseName", DATABASE);
      		mySQLDS.init();
      		
      		BitronixTransactionManager btm = TransactionManagerServices.getTransactionManager();
      		
      		try		{
      			btm.begin();
      			Connection connection = mySQLDS.getConnection(USER_NAME, PASSWORD);
      			
      			PreparedStatement pstmt = connection.prepareStatement(INSERT_QUERY);
      			for(int index = 1; index <= 5; index++) {
      				pstmt.setInt(1,index);
      				pstmt.setString(2, "student_" + index);
      				pstmt.setString(3, "" + (4 + index));
      				pstmt.setString(4, "A");
      				pstmt.executeUpdate();
      			}
      			pstmt.close();
      			
      			connection.close();
      			
      			btm.commit();
      			
      		} catch (Exception ex) {
      			ex.printStackTrace();
                  try {
      				btm.rollback();
      			} catch (Exception e) {
      				e.printStackTrace();
      			} 
      		}
      		
      		try {
      			btm.begin();
      			Connection connection = mySQLDS.getConnection(USER_NAME, PASSWORD);
      			PreparedStatement pstmt = connection.prepareStatement(SELECT_QUERY);
      			ResultSet rs = pstmt.executeQuery();
      			while(rs.next()){
      				System.out.println("Student: " + rs.getInt("roll_number") + " " + 
      						rs.getString("name") + " " + rs.getString("class") 
      						+ " " + rs.getString("section"));
      			}
      			rs.close();
      			pstmt.close();
      			connection.close();
      			btm.commit();
      		}
      		catch (Exception ex) {
      			ex.printStackTrace();
      			try {
      				btm.rollback();
      			} catch (Exception e) {
      				e.printStackTrace();
      			} 
      		}
      		mySQLDS.close();
      		btm.shutdown();
      	}
      }
      

      Here you can see I have used com.mysql.jdbc.jdbc2.optional.MysqlXADataSource for datasource. But this example is limited to single database and single table. By using BitronixTransactionManager you can perform similar operations on multiple databases.

      Before each insert or select operation you need to begin transaction by invoking btm.begin() and at the end of it you commit your transaction by invoking btm.commit().

      Once you perform all your works you have to shutdown Bitronix Transaction Manager by calling btm.shutdown() as cited in the example above.

    2. Run your code to see how it works.

However it is a very basic example to Bitronix JTA Transaction, now you are free to explore your own. For more information you can visit official Bitronix site http://www.bitronix.be.

Advertisements
Categories: Database Programming
  1. zaher
    December 11, 2012 at 1:40 pm

    hi, thank you for this great tutorial, but it’s my first example with jta.
    i’m getting the following exception when i run the example:
    كانون الأول 11, 2012 3:34:26 م bitronix.tm.BitronixTransactionManager logVersion
    INFO: Bitronix Transaction Manager version 2.1.0
    كانون الأول 11, 2012 3:34:26 م bitronix.tm.Configuration buildServerIdArray
    WARNING: cannot get this JVM unique ID. Make sure it is configured and you only use ASCII characters. Will use IP address instead (unsafe for production usage!).
    كانون الأول 11, 2012 3:34:26 م bitronix.tm.Configuration buildServerIdArray
    INFO: JVM unique ID:
    كانون الأول 11, 2012 3:34:27 م bitronix.tm.recovery.Recoverer recoverAllResources
    WARNING: error running recovery on resource ‘mySqlBtm’, resource marked as failed (background recoverer will retry recovery)
    bitronix.tm.recovery.RecoveryException: cannot start recovery on a PoolingDataSource containing an XAPool of resource mySqlBtm with 0 connection(s) (0 still available)
    at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:227)
    at bitronix.tm.recovery.Recoverer.recover(Recoverer.java:253)
    at bitronix.tm.recovery.Recoverer.recoverAllResources(Recoverer.java:223)
    at bitronix.tm.recovery.Recoverer.run(Recoverer.java:138)
    at bitronix.tm.BitronixTransactionManager.(BitronixTransactionManager.java:64)
    at bitronix.tm.TransactionManagerServices.getTransactionManager(TransactionManagerServices.java:62)
    at jtatest.BTMWithMySQL.main(BTMWithMySQL.java:32)
    Caused by: java.sql.SQLException: Access denied for user ”@’localhost’ (using password: NO)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3996)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2142)
    at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:781)
    at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
    at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:439)
    at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:137)
    at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:107)
    at com.mysql.jdbc.jdbc2.optional.MysqlXADataSource.getXAConnection(MysqlXADataSource.java:47)
    at bitronix.tm.resource.jdbc.PoolingDataSource.createPooledConnection(PoolingDataSource.java:274)
    at bitronix.tm.resource.common.XAPool.createPooledObject(XAPool.java:283)
    at bitronix.tm.resource.common.XAPool.grow(XAPool.java:400)
    at bitronix.tm.resource.common.XAPool.getInPool(XAPool.java:379)
    at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:123)
    at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:223)
    … 6 more

    كانون الأول 11, 2012 3:34:27 م bitronix.tm.recovery.Recoverer run
    INFO: recovery committed 0 dangling transaction(s) and rolled back 0 aborted transaction(s) on 0 resource(s) [] (restricted to serverId ‘192.168.0.153’)
    كانون الأول 11, 2012 3:35:27 م bitronix.tm.recovery.Recoverer recoverAllResources
    WARNING: error running recovery on resource ‘mySqlBtm’, resource marked as failed (background recoverer will retry recovery)
    bitronix.tm.recovery.RecoveryException: cannot start recovery on a PoolingDataSource containing an XAPool of resource mySqlBtm with 0 connection(s) (0 still available) -failed-
    at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:227)
    at bitronix.tm.recovery.Recoverer.recover(Recoverer.java:253)
    at bitronix.tm.recovery.Recoverer.recoverAllResources(Recoverer.java:223)
    at bitronix.tm.recovery.Recoverer.run(Recoverer.java:138)
    at java.lang.Thread.run(Unknown Source)
    Caused by: bitronix.tm.internal.BitronixRuntimeException: incremental recovery failed when trying to acquire a connection from failed resource ‘mySqlBtm’
    at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:103)
    at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:223)
    … 4 more
    Caused by: bitronix.tm.recovery.RecoveryException: cannot start recovery on a PoolingDataSource containing an XAPool of resource mySqlBtm with 0 connection(s) (0 still available)
    at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:227)
    at bitronix.tm.recovery.IncrementalRecoverer.recover(IncrementalRecoverer.java:62)
    at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:100)
    … 5 more
    Caused by: java.sql.SQLException: Access denied for user ”@’localhost’ (using password: NO)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3996)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2142)
    at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:781)
    at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
    at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:439)
    at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:137)
    at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:107)
    at com.mysql.jdbc.jdbc2.optional.MysqlXADataSource.getXAConnection(MysqlXADataSource.java:47)
    at bitronix.tm.resource.jdbc.PoolingDataSource.createPooledConnection(PoolingDataSource.java:274)
    at bitronix.tm.resource.common.XAPool.createPooledObject(XAPool.java:283)
    at bitronix.tm.resource.common.XAPool.grow(XAPool.java:400)
    at bitronix.tm.resource.common.XAPool.getInPool(XAPool.java:379)
    at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:123)
    at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:223)
    … 7 more

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: