Handling Transaction with Multiple Database using Spring 3.0 and Hibernate 3.0 Without XA Driver

Problem: We are having two different database like MySQL and ORACLE and we want to access both the database in a single request and want to maintain ACID properties. If there is failure in any of these during request processing all transaction should be rolled back otherwise all transactions should commit. We have to use Spring 3.0 and Hibernate 3.0.

Solution: When I first started to think about this type of transaction management I thought about 2 phase commit which we use in distributed transaction management. In 2 phase commit protocol there will be one resource manager for each database and a single transaction manager who will coordinate with all resource mangers. In first phase Transaction manager will ask to all participating resource manger to be ready and if all reply with OK response then in second phase it ask them to do commit. If during this phase any resource manager reject it the whole transaction will be rolled back. If you need more to dig look at the following e-book.java-transaction-design-strategies.

To implement 2 phase commit protocol in J2EE we need XA database drivers for each database vendor and JTA implementation which will work as transaction manager.

I have tried to implement a transaction strategy without using XA database drivers and JTA. This implementation is not a 2 phase commit protocol. It has tried to take the advantage of Spring declarative transaction mechanism.

In this mechanism if we are using declarative transaction and anything happens wrong Spring roll back all the transaction. 

Design with Spring 3.0 and Hibernate 3.5: In this design we will create 2 session factories and two transaction mangers. This strategy can be applied up to n databases. We will follow the below steps to implement with MySQL and ORACLE.

Step 1: Create two Hibernate session factory one for MySQL and other for ORACLE.

Step 2: Create two transaction manager one for MySQL and other for ORACLE.

Step 3: Create two transaction advice and apply these two advices on a single point cut.

Implementation Example: In this example there are two table first MS_ITEM which is in MySQL database and other is OR_PURCHASE which is in ORACLE. There are two DAO classes each for their one table with having their corresponding hibernate session factory which is being injected by Spring. 

There is one service class PurchaseServiceImpl which is having instances of both the dao and they will be injected in this object by Spring. PurchaseServiceImpl is having one method which is inserting rows in both the databases and not doing any transaction handling.

All the transaction handing is managed by Spring declarative transaction management using two transaction mangers.

This example uses both xml as well as annotations to plug the components. There is one service class which is having one method who will interact with both the databases and if any think wrong happens in this method all transaction will be rolled back.

Below picture shows the directory structure classes and xml files I have used.

Item.java:

package org.paandav;


import java.io.Serializable;


public class Item implements Serializable
{
private static final long serialVersionUID = 1L;
private Long id;
private String itemName;
private Long version;
public Long getVersion(){
return version;
}
public void setVersion(Long version){
this.version = version;
}
public Long getId(){
return id;
}
public void setId(Long id){
this.id = id;
}
public String getItemName(){
return itemName;
}
public void setItemName(String itemName){
this.itemName = itemName;
}
}


Purchase.java:

package org.paandav;


public class Purchase
{
private static final long serialVersionUID = 1L;
private Long id;
private String itemName;
private Long iteNoReference;
private Long version;


public Long getId() {
return id;
}
public void setId(Long id){
this.id = id;
}
public String getItemName(){
return itemName;
}
public void setItemName(String itemName){
this.itemName = itemName;
}
public Long getVersion() {
return version;
}
public void setVersion(Long version){
this.version = version;
}
public Long getIteNoReference(){
return iteNoReference;
}
public void setIteNoReference(Long iteNoReference){
this.iteNoReference = iteNoReference;
}
}

Item.hbm.xml:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC

        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="org.paandav">

<class name="Item" table="MS_ITEM" optimistic-lock="version">

<id name="id" column="id">

<generator class="native" />

</id>

<version name="version" type="long" />

<property name="itemName" length="10" />

</class>

</hibernate-mapping>

Purchase.hbm.xml:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC

        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="org.paandav">

<class name="Purchase" table="OR_PURCHASE" optimistic-lock="version">

<id name="id" column="id">

<generator class="native" />

</id>

<version name="version" type="long" />

<property name="itemName" length="10" />

<property name="iteNoReference" />

</class>

</hibernate-mapping>

ItemDAOIfc.java:

package org.paandav;

public interface ItemDAOIfc

{

public Item save(Item it) throws Exception;

}

ItemDAOImpl.java:

package org.paandav;

import javax.annotation.Resource;

import org.hibernate.SessionFactory;

public class ItemDAOImpl implements ItemDAOIfc

{

SessionFactory mysqlSessfac;

@Resource(name = "mysqlSessionFactory")

public void setMysqlSessfac(SessionFactory mysqlSessfac)

{

this.mysqlSessfac = mysqlSessfac;

}

@Override

public Item save(Item it) throws Exception

{

mysqlSessfac.getCurrentSession().save(it);

return it;

}

}

PurchaseDAOIfc.java:

package org.paandav;

public interface PurchaseDAOIfc

{

public Purchase save(Purchase pr) throws Exception;

}

PurchaseDAOImpl.java:

package org.paandav;

import javax.annotation.Resource;

import org.hibernate.SessionFactory;

public class PurchaseDAOImpl implements PurchaseDAOIfc

{

SessionFactory oracleSessfac;

@Resource(name = "oracleSessionFactory")

public void setOracleSessfac(SessionFactory oracleSessfac)

{

this.oracleSessfac = oracleSessfac;

}

@Override

public Purchase save(Purchase pr) throws Exception

{

oracleSessfac.getCurrentSession().save(pr);

return pr;

}

}

PurchaseServiceIfc.java:

package org.paandav;

public interface PurchaseServiceIfc

{

String purchaseItem(Item[] items, Purchase[] pr) throws Exception;

}

PurchaseServiceImpl.java:

package org.paandav;

import javax.annotation.Resource;

public class PurchaseServiceImpl implements PurchaseServiceIfc

{

ItemDAOIfc itemDao;

PurchaseDAOIfc purDao;

@Resource(name = "purDAO")

public void setPurDao(PurchaseDAOIfc purDao){

this.purDao = purDao;

}

@Resource(name = "itemDAO")

public void setItemDao(ItemDAOIfc itemDao){

this.itemDao = itemDao;

}

@Override

public String purchaseItem(Item[] items, Purchase[] pr) throws Exception{

//Insert in MySQL

for (Item i : items){

itemDao.save(i);

}

//Insert in ORACLE

for (Purchase p : pr){

purDao.save(p);

}

return "GOT IT";

}

}

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>

<!--

Application context definition for Online Exam

-->

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"

xmlns:jdbc="http://www.springframework.org/schema/jdbc"

xsi:schemaLocation="

            http://www.springframework.org/schema/beans

            http://www.springframework.org/schema/beans/spring-beans.xsd

            http://www.springframework.org/schema/aop

            http://www.springframework.org/schema/aop/spring-aop.xsd

            http://www.springframework.org/schema/tx

            http://www.springframework.org/schema/tx/spring-tx-3.0.xsd

            http://www.springframework.org/schema/jdbc

            http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd

            http://www.springframework.org/schema/context

            http://www.springframework.org/schema/context/spring-context-3.0.xsd">

<context:annotation-config />

<aop:aspectj-autoproxy />

<!-- Hibernate plugin code -->

<bean id="mysqlSessionFactory"

class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">

<property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration" />

<property name="configLocation" value="classpath:mysql_hibernate.cfg.xml" />

</bean>

<bean id="oracleSessionFactory"

class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">

<property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration" />

<property name="configLocation" value="classpath:oracle_hibernate.cfg.xml" />

</bean>

<!-- Transaction managers -->

<bean id="mySqlTxManager"

class="org.springframework.orm.hibernate3.HibernateTransactionManager">

<property name="sessionFactory" ref="mysqlSessionFactory" />

</bean>

<bean id="oracleTxManager"

class="org.springframework.orm.hibernate3.HibernateTransactionManager">

<property name="sessionFactory" ref="oracleSessionFactory" />

</bean>

<aop:config>

<aop:pointcut id="purchaseBeanMethods"

expression="execution(* org.paandav.PurchaseServiceIfc.*(..))" />

<aop:advisor advice-ref="mysqlTxAdvice" pointcut-ref="purchaseBeanMethods" />

<aop:advisor advice-ref="oracleTxAdvice" pointcut-ref="purchaseBeanMethods" />

</aop:config>

<tx:advice id="mysqlTxAdvice" transaction-manager="mySqlTxManager">

<tx:attributes>

<tx:method name="*" propagation="REQUIRED" />

</tx:attributes>

</tx:advice>

<tx:advice id="oracleTxAdvice" transaction-manager="oracleTxManager">

<tx:attributes>

<tx:method name="*" propagation="REQUIRED" />

</tx:attributes>

</tx:advice>

<!-- Service -->

<bean id="purchaseBean" class="org.paandav.PurchaseServiceImpl" />

<!-- DAO -->

<bean id="itemDAO" class="org.paandav.ItemDAOImpl"></bean>

<bean id="purDAO" class="org.paandav.PurchaseDAOImpl"></bean>

</beans>

Test Java File:

package org.paandav;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MultipleDataBaseConfiguration

{

/**

* @param args

*/

public static void main(String[] args) throws Exception

{

ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(

"applicationContext.xml");

PurchaseServiceIfc ps = (PurchaseServiceIfc) ctx

.getBean("purchaseBean");

Item[] items = new Item[2];

items[0] = new Item();

items[1] = new Item();

items[0].setItemName("MYSLITEMMM");

items[1].setItemName("ORACLEITEM");

Purchase[] prs = new Purchase[2];

prs[0] = new Purchase();

prs[1] = new Purchase();

prs[0].setItemName("PURCHASE1");

prs[1].setItemName("PURCHASE2");

System.out.println(ps.purchaseItem(items, prs));

}

}

Validation: Run the above MultipleDataBaseConfiguration file and you will see that all rows has been inserted inn MySQL and ORACLE database successfully.

Now change the test java file as

items[1].setItemName("ORACLEITEMMMMMMMMMMMMMMMMMMMMM");

because of above changes hibernate will throw an exception because length of column is 10 and again run. Now an exception will be thrown and no rows will be inserted in MySQL and ORACLE and this MySQL has thrown exception and ORACLE insertion has also been rolled back.

Now revert back your changes and change file as

prs[0].setItemName("PURCHASEEEEEEEEEEEEEEEEEEEEEEEEEEEEE1");

and tun the java program. This time MySQL insertions are successful but ORACLE will throw an exception and due to this exception MySQL insertions will be rolled back.

Conclusion: We can observe that we are able to manage the transactions without using XA database drivers for different databases.