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!

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. — Gray, Dec 06 '11 at 17:48
The keyword you want is multitenancy. Have a look around and see if the various implementations cover you. also check out http://relation.to/Bloggers/MultitenancyInHibernate — Tassos Bassoukos, Dec 07 '11 at 09:17
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. https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#multitenacy — Night Owl, Mar 05 '16 at 02:15
@TassosBassoukos could you please answer of this question https://stackoverflow.com/questions/53297213/how-to-switch-database-on-runtime-in-springboot-and-springdatajpa — Irfan Nasim, Nov 14 '18 at 10:15

Michael W · Accepted Answer · 2016-03-07T15:03:27.647

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!

Can you share a transaction between those datasources that way? — Vedran, Sep 28 '12 at 08:04
To answer myself - yes you can, but you have to use JTA with XA enabled datasources. — Vedran, Dec 12 '12 at 09:45
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. — Wilson Freitas, Jul 19 '14 at 20:42

score 2 · Answer 2 · answered Dec 07 '11 at 17:34

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.

answered Dec 07 '11 at 17:34

Robert Petermeier

4,122
4
29
37

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. " – Michael W Dec 08 '11 at 10:50

score 0 · Answer 3 · answered Dec 09 '13 at 23:30

0

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.

answered Dec 09 '13 at 23:30

prarthan

11
1
3

Reading from multiple Db's with same Persistence Unit?

3 Answers3

Linked