Hibernate using multiple databases

Question

Someone know how to add a another datasource in hibernate configuration and how to configure Spring to that datasource its autoinject in my respective DAO?

This is my code with one datasource, that run perfectly, but i don't know how add another datasource. I want to add another data source that is a database with different tables than the actual database.

HIBERNATE CONF

 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>com.mysql.jdbc.Driver</value>
        </property>
        <property name="url" value="jdbc:mysql://localhost/personal"/>
        <property name="username" value="root"/>
        <property name="password" value="mysql"/>
    </bean>


    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource">
            <ref local="dataSource"/>
        </property>
        <property name="packagesToScan">
            <list>
                <value>com.app.personal.model</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
                <prop key="hibernate.show_sql">false</prop>
            </props>
        </property>
    </bean>

    <tx:annotation-driven/>
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory">
            <ref local="sessionFactory"/>
        </property>
    </bean>

DAO EXAMPLE

@Repository
public class ModuloDAOHibernate extends HibernateTemplate implements ModuloDAO {

    @Autowired
    public ModuloDAOHibernate(SessionFactory sessionFactory) {
        super(sessionFactory);
    }

    public List<Modulo> getAllGrupoModuloDAO() {
        Criteria criteriaList = this.getSession().createCriteria(Modulo.class);
        criteriaList.addOrder(Order.asc("orden"));
        return criteriaList.list();
    }

Answer 1

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.