What is the common solution for multiple datasources?

1
votes

Greetings all In my spring application I will need to use hibernate with two different databases (PostgreSQL & MySQL) and I am not pretty good with configuration, so I need some guide about how to do so

I am using the following configuration for hibernate-postgresql

<bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">

        <property name="dataSource" ref="dataSource" />
        <property name="annotatedClasses">
            <list>
                <value>com.project.domain.myDomain</value>
            </list>

        </property>

        <property name="hibernateProperties">
            <value>
                hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
            </value>
        </property>


    </bean>


    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="org.postgresql.Driver" />

        <property name="url">
        <value>${db.url}</value> 
        </property>

        <property name="username" > 
        <value>${db.username}</value>
        </property>

        <property name="password">  
        <value>${db.password}</value>
        </property>

    </bean>

    <bean id="transactionManager"
        class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>

    <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>

    <bean
        class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

and in the DAO I make an autowire for the sessionFactory.

3
hibernatespringspring-mvc
Do you need to persist the same entity in both databases, or do you need to get some entities from one database, and some from the other database? Like, "Person" is on MySQL, while "Order" is on PostgreSQL? - jpkrohling
the second way, some entities in mysql and other entities in postgresql - Mahmoud Saleh
Do you need to make changes to both database in a single transaction, or do you not need that? - skaffman
no I don't need that, I will need separate transactions for each database. - Mahmoud Saleh
can you please help me with the configuration problem stackoverflow.com/questions/4724633/… - Mahmoud Saleh

3 Answers

2
votes

If you have two databases, and you want two-phase commit, you'd better be sure to use XA drivers for both.

1
votes

The only way is to have to Data Sources with their one stack (SessionFactory, Hibernate Template, ect.). Then you can inject the HIbernate Tempolate you want to use in you business classes (or both if you want to access both DB at same time).

here is an example DAO with explicit config ... 

<bean id="db2SessionFactory"
    class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
...
</bean>

<bean id="db1Dao" class="ch.sbb.uno.dao.hibernate.UnoHibernateDaoSupport" scope="prototype">
    <property name="sessionFactory" ref="db1SessionFactory" />  
</bean>

<bean id="db2Dao" class="ch.sbb.uno.dao.hibernate.UnoHibernateDaoSupport" scope="prototype">
    <property name="sessionFactory" ref="db2SessionFactory" />  
</bean>
0
votes

You probably need XA drivers.

See below for an example

http://docs.codehaus.org/display/BTM/Home