Multiple Entity Manager issue in Spring when using more than one datasource

Question

I have two entity managers in my applicationContext.xml which corresponds to two different databases. I can easily query database1 with entityManager1, but when I try to access database2 with entityManager2, I am not getting any results. I am using Spring+Hibernate+JPA.

Here is my ApplicationContext.xml

    <?xml version="1.0" encoding="UTF-8"?>

    <beans default-autowire="byName"
     xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns:tx="http://www.springframework.org/schema/tx"
     xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">

 <bean
  class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />


 <bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="jpaVendorAdapter">
   <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="database" value="INFORMIX" />
    <property name="showSql" value="true" />
   </bean>
  </property>
  <property name="persistenceUnitManager" ref="persistenceUnitManager" />
  <property name="persistenceUnitName" value="PU1" />
 </bean>



 <bean id="entityManagerFactory2"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource" ref="dataSource2" />
  <property name="jpaVendorAdapter">
   <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="database" value="INFORMIX" />
    <property name="showSql" value="true" />
   </bean>
  </property>
  <property name="persistenceUnitManager" ref="persistenceUnitManager" />
  <property name="persistenceUnitName" value="PU2" />
 </bean>



 <!-- Data Sources -->

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
  destroy-method="close">
  <property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver" />
  <property name="url"
   value="jdbc:db2://HOST_NAME:PORT_NO/DB_NAME:INFORMIXSERVER=SERVER_NAME;DELIMIDENT=y;" />
  <property name="username" value="username" />
  <property name="password" value="password" />
  <property name="minIdle" value="2" />
 </bean>

 <bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource"
  destroy-method="close">
  <property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver" />
  <property name="url"
   value="jdbc:db2://HOST_NAME:PORT_NO/DB_NAME2:INFORMIXSERVER=SERVER_NAME;DELIMIDENT=y;" />
  <property name="username" value="username" />
  <property name="password" value="password" />
  <property name="minIdle" value="2" />
 </bean>

 <bean
  class="org.springframework.beans.factory.config.MethodInvokingFactoryBean"
  lazy-init="false">
  <property name="targetObject" ref="dataSource" />
  <property name="targetMethod" value="addConnectionProperty" />
  <property name="arguments">
   <list>
    <value>characterEncoding</value>
    <value>UTF-8</value>
   </list>
  </property>
 </bean>

 <bean
  class="org.springframework.beans.factory.config.MethodInvokingFactoryBean"
  lazy-init="false">
  <property name="targetObject" ref="dataSource2" />
  <property name="targetMethod" value="addConnectionProperty" />
  <property name="arguments">
   <list>
    <value>characterEncoding</value>
    <value>UTF-8</value>
   </list>
  </property>
 </bean>

 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
  scope="prototype">
  <property name="dataSource" ref="dataSource" />
 </bean>



 <bean id="persistenceUnitManager"
  class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
  <property name="persistenceXmlLocations">
   <list>
    <value>classpath*:META-INF/persistence.xml</value>
    <value>classpath*:META-INF/persistence2.xml</value>
   </list>
  </property>
  <property name="dataSources">
   <map>
    <entry key="localDataSource" value-ref="dataSource" />
    <entry key="dataSource2" value-ref="dataSource2" />
   </map>
  </property>
  <property name="defaultDataSource" ref="dataSource" />
 </bean>



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

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

 <tx:annotation-driven transaction-manager="transactionManager" />
 <tx:annotation-driven transaction-manager="transactionManager2" />

 <!-- MORE Action and DAO beans -->


</beans>

This is my service layer code which works fine with enityManager1:

  @Transactional
    public class StatesDAO implements IStatesDAO {
 private EntityManager em;

 @PersistenceContext(unitName = "PU1")
 public void setEntityManager(EntityManager em) {
  this.em = em;
 }

 private EntityManager getEntityManager() {
  return em;
 }

 @SuppressWarnings("unchecked")
 public List<States> findAll() {
  logger.info("finding all States instances");
  try {
   final String queryString = "select model from States model";

   Query query = getEntityManager().createQuery(queryString);
   return query.getResultList();

  } catch (RuntimeException re) {
   throw re;
  }

 }

    }

My two persitence.xml files look like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
 xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
 <persistence-unit name="PU1" transaction-type="RESOURCE_LOCAL">
  <provider>org.hibernate.ejb.HibernatePersistence</provider>
  <class>com.jpa.entity.States</class>
 </persistence-unit>
</persistence>

and

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
 xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
 <persistence-unit name="PU2" transaction-type="RESOURCE_LOCAL">
  <provider>org.hibernate.ejb.HibernatePersistence</provider>
  <class>com.jpa.other.entity.States</class>
 </persistence-unit>
</persistence>

If I change my service layer (as shown below), I get no results. Basically the size of the list is zero:

@Transactional
public class StatesDAO implements IStatesDAO {
private EntityManager em;

@PersistenceContext(unitName = "PU2")
public void setEntityManager(EntityManager em) {
    this.em = em;
}

private EntityManager getEntityManager() {
    return em;
}

@SuppressWarnings("unchecked")
public List<com.jpa.other.entity.States> findAll() {
    logger.info("finding all States instances");
    try {
        final String queryString = "select model from States model";

        Query query = getEntityManager().createQuery(queryString);
        return query.getResultList();

    } catch (RuntimeException re) {
        throw re;
    }

}

}

So basically you can see is that I have two entities(States) with exactly same structure and in order to differentiate from each other I have put them into separate packages

According to my knowledge I am not doing anything crazy here but still it doesn't seem to be working. How is this problem caused and how can I solve this?

Follow-up: One thing I forgot to mention is that even though there are two different databases but the database server name is same. I don't know if this could be a useful information.So thought of sharing it.

This is the exception I am getting now:

16:24:44,732 INFO [STDOUT] Hibernate: select state0_.state as col_0_0_ from states state0_ 
16:24:44,753 WARN [JDBCExceptionReporter] SQL Warning: 36106, SQLState: 01I01 
16:24:44,753 WARN [JDBCExceptionReporter] IDS SQL Warning: SQLCODE=36106, SQLSTATE=01I01, SQLERRMC=0;819;informix;;IDS/NT32;1;1;0;819;0;, DRIVER=4.7.85

You're still injecting an EM for PU1 in the above sample, is it a typo? And why do you have two versions of the `State` actually? What's the point? — Pascal Thivent, Sep 17 '10 at 07:08
Hi Pascal, Yes! this is a typo. I tried with single State version too but still the same issue. The reason why two states because in one database the state table has one to many relationship and in the second database it does not. Even though the structure is same but constraints are different. — Sameer Malhotra, Sep 17 '10 at 14:45
`Yes! this is a typo` Ok. `I tried with single State version too but still the same issue` Oh, I just wanted to understand, but having 2 entities is not the root cause of the problem. `The reason why two states because in one database the state table has one to many relationship and in the second database it does not` Got it now. — Pascal Thivent, Sep 18 '10 at 12:42
Still, I don't know how to interpret the error that you get. Did you try to increase the logging level (to DEBUG) to see if you get more useful traces? — Pascal Thivent, Sep 18 '10 at 12:43
This is the only tracing I could get. I am using JCC driver for informix and it is not that good in giving the exact error. Now I am thinking of using spring's JDBC template. Just did a POC and seems to be working fine. Do you think its a possible workaround since I really have to get going? — Sameer Malhotra, Sep 20 '10 at 16:11

score 12 · Answer 1 · answered Jul 22 '11 at 21:06

I've hit the same exact issue, but with multiple Hibernate session factories: 2 DBs with the same structure, I didn't want to have 2 identical sets of DAOs, etc. While my experience was with Hibernate, I suspect you could use the same solution: Spring's AbstractRoutingDataSource. It allows you to configure your app to determine at runtime which data source to use, based on a value set on the ThreadLocal. See http://blog.springsource.com/2007/01/23/dynamic-datasource-routing/ for an introduction. What ends up happening is that the dataSource ref in your factory will point not at a hard-coded dataSource bean, but at the AbstractRoutingDataSource. To set the toggle per-thread, use an @Aspect to determine which DB to hit.

Multiple Entity Manager issue in Spring when using more than one datasource

1 Answers1

Linked