Multiple Entity Manager issue in Spring when using more than one datasource
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.
Related videos on Youtube
Sameer Malhotra
Updated on May 03, 2022Comments
-
Sameer Malhotra about 2 years
I have two entity managers in my
applicationContext.xml
which corresponds to two different databases. I can easily querydatabase1
withentityManager1
, but when I try to accessdatabase2
withentityManager2
, 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
-
Devanshu Mevada over 13 yearsYou'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? -
Devanshu Mevada over 13 yearsStill, 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?
-