How to use two databases/datasources in Spring and Hibernate

Question

I need to use two databases in my standalone application, one local HSQLDB and another remote MySQL. I use Spring 3.1.2, Hibernate 4.1.7. So far I managed to use only HSQLDB, this is how my DAO and configurations look like:

@Transactional
public abstract class HibernateDao<T, ID extends Serializable> implements Dao<T, ID>{

SessionFactory sessionFactoryHSQL;


public void setSessionFactoryHSQL(SessionFactory sessionFactory) {
    this.sessionFactoryHSQL = sessionFactory;
}

public ID save(T object) {
    return (ID) getCurrentSession().save(object);
}

public void persist(T object){
    getCurrentSession().persist(object);
}

public void update(T object) {
    getCurrentSession().update(object);
}

public void delete(T object) {
    getCurrentSession().delete(object);
}

public List<T> find(String query) {
    return getCurrentSession().createQuery(query).list();
}

public Session getCurrentSession(){
    return sessionFactoryHSQL.getCurrentSession();
}
}


<bean id="sessionFactoryHSQL" 
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">

    <property name="dataSource">
      <ref bean="dataSourceHSQL"/>
    </property>

    <property name="hibernateProperties">
       <props>
         <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
         <prop key="hibernate.show_sql">true</prop>
         <prop key="hibernate.hbm2ddl.auto">update</prop>
       </props>
    </property>

    <property name="annotatedClasses">
    <list>
        //annotated classes
    </list>
    </property>
    </bean>

<bean id="txManager" class=
    "org.springframework.orm.hibernate4.HibernateTransactionManager">
   <property name="sessionFactory" ref="sessionFactoryHSQL" />
</bean>
<tx:annotation-driven transaction-manager="txManagerHSQL" />

<bean id="configHSQL"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location">
        <value>properties/HSQL.properties</value>
    </property>
</bean>

<bean id="dataSourceHSQL" 
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
</bean>

I created separate beans for MySQL, such as sessionFactoryMySQL, dataSourceMySQL (I'm not going to paste their implementations as they are nearly the same as for HSQL). I managed to correctly inject sessionFactoryMySQL to DAO, but I can't use both sessionFactorys at the same time, for example I can't do following:

public void persist(T object) {
    sessionFactoryHSQL.getCurrentSession().persist(object);
    sessionFactoryMySQL.getCurrentSession().persist(object);
}

As I already discovered, it has something to do with transactions, but I can't properly configure transaction manager. I have been searching for solution for a long time but examples or tutorias was either not clear or did not refer to exaclty my case.

Sounds like you're trying to do two-phase commit? Does your application require that HSQL and MySQL persist calls commit/rollback together, or is it ok for MySQL to rollback but HSQL to commit? Knowing that will help restructure the design of your dao — Doug Ayers
– Doug Ayers, Commented Oct 16, 2012 at 2:08
Operations like persist, save, update, delete must hit both databases together, because data between them must be synchronized. In case of select statement, first should be MySQL but if the internet connection is broken then select should go to local HSQL. — polmarex
– polmarex, Commented Oct 16, 2012 at 9:58
I believe Spring's transaction manager can only be bound to one database. You may need to have 2 transaction manager beans, one for MySQL and one for HSQL, and use explicit transactions within your persist/update/delete methods rather than the @Transaction annotation at the class level. — Doug Ayers
– Doug Ayers, Commented Oct 18, 2012 at 3:59
Maybe check out this related question? stackoverflow.com/questions/8271950/… — Doug Ayers
– Doug Ayers, Commented Oct 18, 2012 at 4:05

Rahul Jain · Accepted Answer · 2014-07-21 06:14:48Z

2

You need to create two Session Factories for both database. Check the simple step by step example : Two Datasource in Spring/Hiberate

answered Jul 21, 2014 at 6:14

Rahul Jain

6685 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use two databases/datasources in Spring and Hibernate

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related