title: Multiple Entity Manager issue in Spring when using more than one datasource
body: <p>I have two entity managers in my <code>applicationContext.xml</code> which corresponds to two different databases. I can easily query <code>database1</code> with <code>entityManager1</code>, but when I try to access <code>database2</code> with <code>entityManager2</code>, I am not getting any results. I am using Spring+Hibernate+JPA.</p>
  
  <p>Here is my <code>ApplicationContext.xml</code></p>
  
  <pre><code>    &lt;?xml version="1.0" encoding="UTF-8"?&gt;
  
      &lt;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"&gt;
  
   &lt;bean
    class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" /&gt;
  
  
   &lt;bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
    &lt;property name="dataSource" ref="dataSource" /&gt;
    &lt;property name="jpaVendorAdapter"&gt;
     &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"&gt;
      &lt;property name="database" value="INFORMIX" /&gt;
      &lt;property name="showSql" value="true" /&gt;
     &lt;/bean&gt;
    &lt;/property&gt;
    &lt;property name="persistenceUnitManager" ref="persistenceUnitManager" /&gt;
    &lt;property name="persistenceUnitName" value="PU1" /&gt;
   &lt;/bean&gt;
  
  
  
   &lt;bean id="entityManagerFactory2"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
    &lt;property name="dataSource" ref="dataSource2" /&gt;
    &lt;property name="jpaVendorAdapter"&gt;
     &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"&gt;
      &lt;property name="database" value="INFORMIX" /&gt;
      &lt;property name="showSql" value="true" /&gt;
     &lt;/bean&gt;
    &lt;/property&gt;
    &lt;property name="persistenceUnitManager" ref="persistenceUnitManager" /&gt;
    &lt;property name="persistenceUnitName" value="PU2" /&gt;
   &lt;/bean&gt;
  
  
  
   &lt;!-- Data Sources --&gt;
  
   &lt;bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close"&gt;
    &lt;property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver" /&gt;
    &lt;property name="url"
     value="jdbc:db2://HOST_NAME:PORT_NO/DB_NAME:INFORMIXSERVER=SERVER_NAME;DELIMIDENT=y;" /&gt;
    &lt;property name="username" value="username" /&gt;
    &lt;property name="password" value="password" /&gt;
    &lt;property name="minIdle" value="2" /&gt;
   &lt;/bean&gt;
  
   &lt;bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close"&gt;
    &lt;property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver" /&gt;
    &lt;property name="url"
     value="jdbc:db2://HOST_NAME:PORT_NO/DB_NAME2:INFORMIXSERVER=SERVER_NAME;DELIMIDENT=y;" /&gt;
    &lt;property name="username" value="username" /&gt;
    &lt;property name="password" value="password" /&gt;
    &lt;property name="minIdle" value="2" /&gt;
   &lt;/bean&gt;
  
   &lt;bean
    class="org.springframework.beans.factory.config.MethodInvokingFactoryBean"
    lazy-init="false"&gt;
    &lt;property name="targetObject" ref="dataSource" /&gt;
    &lt;property name="targetMethod" value="addConnectionProperty" /&gt;
    &lt;property name="arguments"&gt;
     &lt;list&gt;
      &lt;value&gt;characterEncoding&lt;/value&gt;
      &lt;value&gt;UTF-8&lt;/value&gt;
     &lt;/list&gt;
    &lt;/property&gt;
   &lt;/bean&gt;
  
   &lt;bean
    class="org.springframework.beans.factory.config.MethodInvokingFactoryBean"
    lazy-init="false"&gt;
    &lt;property name="targetObject" ref="dataSource2" /&gt;
    &lt;property name="targetMethod" value="addConnectionProperty" /&gt;
    &lt;property name="arguments"&gt;
     &lt;list&gt;
      &lt;value&gt;characterEncoding&lt;/value&gt;
      &lt;value&gt;UTF-8&lt;/value&gt;
     &lt;/list&gt;
    &lt;/property&gt;
   &lt;/bean&gt;
  
   &lt;bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
    scope="prototype"&gt;
    &lt;property name="dataSource" ref="dataSource" /&gt;
   &lt;/bean&gt;
  
  
  
   &lt;bean id="persistenceUnitManager"
    class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager"&gt;
    &lt;property name="persistenceXmlLocations"&gt;
     &lt;list&gt;
      &lt;value&gt;classpath*:META-INF/persistence.xml&lt;/value&gt;
      &lt;value&gt;classpath*:META-INF/persistence2.xml&lt;/value&gt;
     &lt;/list&gt;
    &lt;/property&gt;
    &lt;property name="dataSources"&gt;
     &lt;map&gt;
      &lt;entry key="localDataSource" value-ref="dataSource" /&gt;
      &lt;entry key="dataSource2" value-ref="dataSource2" /&gt;
     &lt;/map&gt;
    &lt;/property&gt;
    &lt;property name="defaultDataSource" ref="dataSource" /&gt;
   &lt;/bean&gt;
  
  
  
   &lt;bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"&gt;
    &lt;property name="entityManagerFactory" ref="entityManagerFactory" /&gt;
   &lt;/bean&gt;
  
   &lt;bean id="transactionManager2" class="org.springframework.orm.jpa.JpaTransactionManager"&gt;
    &lt;property name="entityManagerFactory" ref="entityManagerFactory2" /&gt;
   &lt;/bean&gt;
  
   &lt;tx:annotation-driven transaction-manager="transactionManager" /&gt;
   &lt;tx:annotation-driven transaction-manager="transactionManager2" /&gt;
  
   &lt;!-- MORE Action and DAO beans --&gt;
  
  
  &lt;/beans&gt;
  </code></pre>
  
  <p>This is my service layer code which works fine with <code>enityManager1</code>:</p>
  
  <pre><code>  @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&lt;States&gt; 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;
    }
  
   }
  
      }
  </code></pre>
  
  <p>My two <code>persitence.xml</code> files look like this:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;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"&gt;
   &lt;persistence-unit name="PU1" transaction-type="RESOURCE_LOCAL"&gt;
    &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
    &lt;class&gt;com.jpa.entity.States&lt;/class&gt;
   &lt;/persistence-unit&gt;
  &lt;/persistence&gt;
  </code></pre>
  
  <p>and</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;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"&gt;
   &lt;persistence-unit name="PU2" transaction-type="RESOURCE_LOCAL"&gt;
    &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
    &lt;class&gt;com.jpa.other.entity.States&lt;/class&gt;
   &lt;/persistence-unit&gt;
  &lt;/persistence&gt;
  </code></pre>
  
  <p>If I change my service layer (as shown below), I get no results. Basically the size of the list is zero:</p>
  
  <pre><code>@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&lt;com.jpa.other.entity.States&gt; 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;
      }
  
  }
  
  }
  </code></pre>
  
  <p>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</p>
  
  <p>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?</p>
  
  <p><strong>Follow-up:</strong> 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.</p>
  
  <p>This is the exception I am getting now:</p>
  
  <pre><code>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 
  </code></pre>