Multiple database Schema with Spring+Hibernate+JPA

Question

I have same database schema database_1 and database_2 and want to configuration of these two database(database schema is same) and decide run time which database is use.
My persistence.xml is as below:

 <persistence-unit name="first" transaction-type="RESOURCE_LOCAL">
     <provider>org.hibernate.ejb.HibernatePersistence</provider>
     <class>com.model.AboutUs</class>
     <class>com.model.AccessCards</class>
           <properties>
        <property name="hibernate.dialect" value="com.util.customMySQLDialect" />
        <property name="hibernate.hbm2ddl.auto" value="none" />
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.cache.provider_class" value="org.hibernate.cache.HashtableCacheProvider" />
        <property name="hibernate.jdbc.batch_size" value="20"/>
        <property name="hibernate.cache.use_query_cache" value="true"/>
        <property name="hibernate.cache.use_second_level_cache" value="true"/>
          </properties>
</persistence-unit>

Persistence name second configure here as below :

<persistence-unit name="second" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>com.model.AboutUs</class>
    <class>com.model.AccessCards</class>
            <properties>
        <property name="hibernate.dialect" value="com.util.customMySQLDialect" />
        <property name="hibernate.hbm2ddl.auto" value="none" />
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.cache.provider_class" value="org.hibernate.cache.HashtableCacheProvider" />
        <property name="hibernate.jdbc.batch_size" value="20"/>
        <property name="hibernate.cache.use_query_cache" value="true"/>
        <property name="hibernate.cache.use_second_level_cache" value="true"/>
           </properties>
</persistence-unit>

Both persistence units load same classes . and now my database-configure.xml as where i configure persistence unit with data source. as below first persistence unit name configare with datasource name datasource as :

<bean id="transactionManager"   class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="persistenceUnitName" value="first" />
    <property name="persistenceXmlLocation" value="classpath*:META-INF/persistence.xml"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    </property>
</bean>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${datasource.driverClassName}" />
    <property name="jdbcUrl" value="${datasource.url}" />
    <property name="user" value="${datasource.username}" />
    <property name="password" value="${datasource.password}" />
    <property name="acquireIncrement" value="1" />
    <property name="acquireRetryAttempts" value="1" />
    <property name="maxIdleTime" value="300" />
    <property name="maxPoolSize" value="20" />
    <property name="minPoolSize" value="3" />
    <property name="maxStatements" value="300" />
    <property name="testConnectionOnCheckin" value="true" />        
</bean>

Now this code for configure second persistence unit name with data source name datasource5 as below :

    <bean id="transactionManager5" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory5" />
</bean>
   <bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource5" />
    <property name="persistenceUnitName" value="second" />
    <property name="persistenceXmlLocation" value="classpath*:META-INF/persistence.xml"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    </property>
</bean> 
<bean id="dataSource5"    class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${datasource.driverClassName}" />
    <property name="jdbcUrl" value="${datasource5.url}" />
    <property name="user" value="${datasource5.username}" />
    <property name="password" value="${datasource5.password}" />
    <property name="acquireIncrement" value="1" />
    <property name="acquireRetryAttempts" value="1" />
    <property name="maxIdleTime" value="300" />
    <property name="maxPoolSize" value="20" />
    <property name="minPoolSize" value="3" />
    <property name="maxStatements" value="300" />
    <property name="testConnectionOnCheckin" value="true" />        
</bean>

Now i use entity manager for save data in database and i am using mysql database . I got entity manager with persistence unit name first and second .

@PersistenceContext(unitName="first")
private EntityManager entityManager;


@PersistenceContext(unitName="second")
private EntityManager entityManager2;

Now entity manager save data in database using persist( object) method successfully . entityManager.persist(project);

and entityManager2 save data in database using persist( object ) method successfully (No Exception here). But data does not store in database datbase_2 .

entityManager2.persist(project);

If I change the order of persistence unit first and second in persistence.xml then entityManager2 save data in database and entitymanager does not save data in database .
Any one have idea how i can make multiple entitymanager for same database schema .

Gregory.K Gregory.K · Accepted Answer · 2016-12-04T00:00:34

There are a few limitations:

@Transactional corresponds to single TransactionManager.
JpaTransactionManager corresponds to single EntityManager(Factory).

Thus use separate methods with @Transactional annotation for each transaction manager:

@Transactional(transactionManager = "tmBeanName")

After that entity managers will work fine.

Also, some workaround exists to break the second limitation (DO NOT USE IT in this case):

It is possible to use single JtaTransactionManager instead of several JpaTransactionManager: distributed transaction will cover both entity managers and they will work fine too.

Multiple database Schema with Spring+Hibernate+JPA

1 Answers