Hibernate using multiple databases

I assume you have a set of DAOs that should use dataSource1 and appropriate sessionFactory1, while others should use different dataSouce2 and sessionFactory2 based on dataSource2. Of course you need to declare your second dataSource and other beans: simply copy the configuration you already have and change bean ids so they won't collide. Everything should be mirrored except <tx:annotation-driven/>:

<bean id="dataSource1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <!-- ... -->
</bean>

<bean id="sessionFactory1" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource1"/>
    <!-- ... -->
</bean>

<bean id="transactionManager1" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory1"/>
    <!-- ... -->
</bean>


<bean id="dataSource2" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <!-- ... -->
</bean>

<bean id="sessionFactory2" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource2"/>
    <!-- ... -->
</bean>

<bean id="transactionManager2" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory2"/>
    <!-- ... -->
</bean>

<tx:annotation-driven transaction-manager="transactionManager1"/>

And here comes the real problem: you now have two transaction managers bound to different session factories, which in turns are routed to different data sources. But @Transactional annotations will always use only one transaction manager - the one named transactionManager by default (note I explicitly pointed transactionManager1. This means that DAOs using second data source will participate in transactions started within first data source - this is obviously not what intended.

There are some workaround to this, like explicitly defining transaction manager name in @Transactional annotation (never tried it) or using TransactionTemplate, but as you can see problem should be well thought.

As for autowiring - if you autowire by name, name your fields the same as session factories or data sources ids and it should work - but is your smallest problem actually.


I had this same problem. I have solved this by creating: applicationContext.xml

<!-- dataSource properies -->
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location" value="classpath:dataSource.properties" />
</bean>

<!-- MySQL -->
<bean id="mySQLdataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${mySql.driverClassName}" />
    <property name="url" value="${mySql.url}" />
    <property name="username" value="${mySql.username}" />
    <property name="password" value="${mySql.password}" />
</bean>
<bean id="mySQLsessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="mySQLdataSource" />
    <property name="packagesToScan" value="com.victor.entity" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${mySql.dialect}</prop>
        </props>
    </property>
    <property name="configLocation">
        <value>classpath:hibernate.cfg.xml</value>
    </property>
</bean>

<bean id="mySQLtransactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="mySQLsessionFactory" />
</bean>
<tx:annotation-driven proxy-target-class="true"
    transaction-manager="mySQLtransactionManager" />

<!-- ORACLE -->
<bean id="oracleDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${oracle.driverClassName}" />
    <property name="url" value="${oracle.url}" />
    <property name="username" value="${oracle.username}" />
    <property name="password" value="${oracle.password}" />
</bean>
<bean id="oracleSessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="oracleDataSource" />
    <property name="packagesToScan" value="com.victor.entity" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${oracle.dialect}</prop>
        </props>
    </property>
    <property name="configLocation">
        <value>classpath:hibernate.cfg.xml</value>
    </property>
</bean>
<tx:annotation-driven proxy-target-class="true"
    transaction-manager="oracleTransactionManager" />
<bean id="oracleTransactionManager"
    class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="oracleSessionFactory" />
</bean>

in Dao injected sessionFactory with Qualifier annotation. In my case I had generic BaseEnity:

public abstract class BaseEntityDAOImpl<T extends BaseEntity> implements BaseEntityDAO<T> {

private Class<T> persistentClass;
@Autowired
@Qualifier("oracleSessionFactory")
SessionFactory sessionFactory;
}

and in service bean using annotation:

@Service
@Transactional(propagation = Propagation.REQUIRED, readOnly = true, value = "oracleTransactionManager")
public class UserService {

@Autowired
private UserDAO dao;
}

Averythings wokrs fine.


ok. I find another solution, and that is using the same method like this: add another dataSource and SessionFactory, next in the method of DAO that injject the sessionFactory add the @Qualifier anottation withe the property of the sessionFactory required, like this:

  @Autowired 
    public ProgramaSgteDAOHibernate(@Qualifier("sessionFactory3") SessionFactory sessionFactory) { 
     super(sessionFactory); 
    }