Reading from multiple Db's with same Persistence Unit?

Question

I need some help to configure several connection's to multiple db's using the same Persistence unit.

They all have the same schema. Therefore I want to use the same Persistence unit/ DAO's etc and dont want to have to setup 10 EntityManagers, 10 Persistence xml's etc. Is there a way to do this? Here is my current config:

  <persistence-unit name="PersistenceUnit-c1" transaction-type="RESOURCE_LOCAL">
        <properties>
            <property name="hibernate.show_sql" value="${hibernate-show-sql}"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.SybaseDialect" />
            <property name="hibernate.c3p0.min_size" value="${hibernate-c3p0-min-size}" />
            <property name="hibernate.c3p0.max_size" value="${hibernate-c3p0-max-size}" />
            <property name="hibernate.c3p0.timeout" value="${hibernate-c3p0-timeout}" />
            <property name="hibernate.c3p0.max_statements" value="${hibernate-c3p0-max-statements}" />
            <property name="hibernate.c3p0.idle_test_period" value="${hibernate-c3p0-idle-test-periods}" />     
        </properties>

        <class>com.domain.TktOrder</class>
        <exclude-unlisted-classes/>
    </persistence-unit>

I am also using Spring/hibernate to set up my context:

    <bean id="EntityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
    p:persistenceUnitName="PersistenceUnit-c1" 
    p:dataSource-ref="DataSource">
    <property name="persistenceXmlLocation" value="classpath:META-INF/persistence.xml" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
            p:showSql="${hibernate-show-sql}" 
            p:generateDdl="false" 
            p:databasePlatform="org.hibernate.dialect.SybaseDialect" />
    </property>
    <property name="loadTimeWeaver">
        <bean class="org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver" />
    </property>
</bean>

<bean id="DataSource" 
    class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" 
    p:driverClass="net.sourceforge.jtds.jdbc.Driver"
    p:jdbcUrl="jdbc:jtds:sybase://url.net:port;DatabaseName=db_1"
    p:user="user" 
    p:password="password"
    />

and finally I use:

@PersistenceContext(unitName="PersistenceUnit-c1")
public void setEntityManager(EntityManager entityManager)
{
    this.entityManager = entityManager;     

}

to inject my EntityManager into my DAO

How can I extend this model to be able to use db1 then change the data source and execute again for db2 etc?

Many thanks for any help in advance!

Concealment

Tough question. I'm not sure there is a standard way to do this. We wrote a lot of code to get the notion of a virtual database connection. – Kevel

The keyword you want is multitenancy. Have a look around and see if the various implementations cover you. also check out relation.to/Bloggers/MultitenancyInHibernate – Acceptor

As mentioned by @TassosBassoukos, mulit-tenancy is the term for this. One configuration allows a single app with 1 connection pool to serve multiple clients dynamically. A common db is used to identify the tenant and then based on the tenant's settings the connection is altered to use the tenant's db. I know links are not ideal but this link is to the "current" Hibernate documentation which should stay consistent over time or at least maintain a link to the information if the link changes. docs.jboss.org/hibernate/orm/current/userguide/html_single/… – Bona

@TassosBassoukos could you please answer of this question #53297713 – Orola

Answer 1

After a few attempts I have found a solution that seems to fit the bill.

Please first have a look at this: dynamic-datasource-routing

This uses a few custom classes which you will need and the key class is AbstractRoutingDataSource.

This reconfigures my datasource bean like so:

    <bean id="dataSource" class="com.domain.etc.etc.recon.utils.RoutingDataSource">
    <property name="targetDataSources">
        <map key-type="com.domain.etc.etc.recon.utils.DbType">
            <entry key="C1" value-ref="C1" />
            <entry key="C2" value-ref="C2" />
        </map>
    </property>
    <property name="defaultTargetDataSource" ref="C3" />
</bean>

Where Connection one C1, C2 look like:

    <bean id="parentDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"
    abstract="true">
    <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
    <property name="username" value="*******" />
    <property name="password" value="*******" />
</bean>

<bean id="C1" parent="parentDataSource">
    <property name="url" 
        value="jdbc:jtds:sybase://URL:PORT;DatabaseName=dbname" />
</bean>

<bean id="C2" parent="parentDataSource">
    <property name="url"
        value="jdbc:jtds:sybase://URL:PORT;DatabaseName=dbname2" />
</bean>

<bean id="C3" parent="parentDataSource">
    <property name="url"
        value="jdbc:jtds:sybase://URL:PORT;DatabaseName=dbname3" />
</bean>

you can inject this into the EntityManager as I have in the original Question;

    <bean id="EntityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
    p:persistenceUnitName="PersistenceUnit" 
    p:dataSource-ref="dataSource">
    <property name="persistenceXmlLocation" value="classpath:META-INF/persistence-.xml" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
            p:showSql="${hibernate-show-sql}" 
            p:generateDdl="false" 
            p:databasePlatform="org.hibernate.dialect.SybaseDialect" />
    </property>
    <property name="loadTimeWeaver">
        <bean class="org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver" />
    </property>
</bean>

After this you need to use your own implementation of the java classes in the link above to be able to switch between data sources. This is nothing more than renaming the classes to ones that are more meaning full to you. Linking the Enum up to C1,C2,C3 etc and finally pointing to your own dao to carry out the work.

Good Luck!

Concealment

Can you share a transaction between those datasources that way? – Icarian

To answer myself - yes you can, but you have to use JTA with XA enabled datasources. – Icarian

How about rollback from C1, C2, C3 in one time? – Finely

What about 2nd level cache? Does Hibernate keep a distinct cache for each datasource? If the cache is shared between data sources that would be a major problem. – Ovine

Answer 2

This might be a use case for Hibernate Shards. We started evaluating it a year ago but then dropped it because all of a sudden sharding wasn't needed after all. So I cannot provide any real experience with it, but I think it's worth a look.

Mediocre

Hi, Thanks for the answer. This definitley seems like a good solution for anyone using hibernate's SessionFactory implmentation. Unfortunatley shards does not support JPA yet: "2.4. Configuration Limitations Many of you will quickly realize that the configuration mechanism we've provided won't work if you're configuring your SessionFactory via JPA. It's true. We expect this deficiency to be addressed shortly. " – Concealment

Answer 3

Hibernate Shard is still evolving, if you need HQL, then it will not work for you, so if you can get away with just criteria query you should be ok.

Corrosive