JPA Hibernate Spring MySql Tomcat - Connect to 2 databases

Question

I've been searching, reading, trying code for 2 days and have not been successful.

I need to be able to connect to 2 different databases, not necessarily simultaneously, using the technologies listed in the header. I'm using Tomcat7, not a J2EE container.

Below is what I have for the application context. It works fine for one database. What do I need to do to configure it for two? How do I tell my DAOs which connection to use? Thanks in advance.

<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:context="http://www.springframework.org/schema/context"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:tx="http://www.springframework.org/schema/tx"
   xmlns:aop="http://www.springframework.org/schema/aop"
   xsi:schemaLocation="
    http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd">


<tx:annotation-driven/>
<context:component-scan base-package="org.aaa.slds"/>
<context:property-placeholder location="classpath:db.properties" />

<bean id="dataSourceWCCC" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="username" value="${dbuser_wccc}"/>
    <property name="password" value="${dbpassword_wccc}"/>
    <property name="url" value="${dburl_wccc}"/>
    <property name="driverClassName" value="${dbdriver_wccc}"/>
</bean>

    <bean id="dataSourceDWS" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="username" value="${dbuser_dws}"/>
    <property name="password" value="${dbpassword_dws}"/>
    <property name="url" value="${dburl_dws}"/>
    <property name="driverClassName" value="${dbdriver_dws}"/>
</bean>

<bean id="entityManagerFactoryWCCC" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSourceWCCC"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
    </property>
    <property name="jpaProperties">
        <map>
            <entry key="hibernate.hbm2ddl.auto" value="create-drop"/>
            <entry key="hibernate.show_sql" value="false"/>
        </map>
    </property>
    <property name="packagesToScan" value="org.aaa.slds.core.models.entities.wccc"/>
</bean>

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"/>

As soon as I add a 2nd EntityManager I get a runtime errpr /// INFO: HHH000204: Processing PersistenceUnitInfo [ name: punit2 ...] May 18, 2015 1:01:22 PM org.apache.catalina.core.StandardContext startInternal SEVERE: Error listenerStart

Here is my complete appcontext file ...

<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:context="http://www.springframework.org/schema/context"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:tx="http://www.springframework.org/schema/tx"
   xmlns:aop="http://www.springframework.org/schema/aop"
   xsi:schemaLocation="
    http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd">

<context:property-placeholder location="classpath:db.properties"/>

<!-- Enable AspectJ style of Spring AOP -->
<aop:aspectj-autoproxy/>

<context:annotation-config/>
<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"/>
<context:component-scan base-package="gov.wyo.slds"/>

<!-- Configure Aspect Beans, without this Aspects advices won't execute
<bean name="loggingAspect" class="LoggingAspect" />  -->

<bean id="dataSourceWCCC" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="username" value="${wccc.dbuser}"/>
    <property name="password" value="${wccc.dbpassword}"/>
    <property name="url" value="${wccc.dburl}"/>
    <property name="driverClassName" value="${wccc.dbdriver}"/>
</bean>

<bean id="entityManagerFactoryWCCC" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="punit"/>

    <property name="dataSource" ref="dataSourceWCCC"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="showSql" value="false"/>
        </bean>
    </property>
    <property name="jpaPropertyMap">
        <map>
            <entry key="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <entry key="hibernate.hbm2ddl.auto" value="update"/>
            <entry key="hibernate.format_sql" value="true"/>
        </map>
    </property>
    <property name="packagesToScan" value="gov.wyo.slds.core.models.entities.wccc"/>
</bean>

<bean id="dataSourceDWS" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="username" value="${dws.dbuser}"/>
    <property name="password" value="${dws.dbpassword}"/>
    <property name="url" value="${dws.dburl}"/>
    <property name="driverClassName" value="${dws.dbdriver}"/>
</bean>

<bean id="entityManagerFactoryDWS" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="punit2"/>

    <property name="dataSource" ref="dataSourceDWS"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="showSql" value="false"/>
        </bean>
    </property>
    <property name="jpaPropertyMap">
        <map>
            <entry key="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <entry key="hibernate.hbm2ddl.auto" value="create"/>
            <entry key="hibernate.format_sql" value="true"/>
        </map>
    </property>
    <property name="packagesToScan" value="gov.wyo.slds.core.models.entities.dws"/>
</bean>

<bean id="transactionManagerWCCC" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactoryWCCC" />
    <qualifier value="wccc"/>
</bean>

<bean id="transactionManagerDWS" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactoryDWS" />
    <qualifier value="dws"/>
</bean>

<tx:annotation-driven/>

<bean id="persistenceExceptionTranslationPostProcessor"
      class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor"/>

<!-- TODO: re-eanble when we have services -->
<!-- context:component-scan base-package="com.slds.core.services.impl"/>  -->

And here is my persistence.xml

<persistence 
    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_2_0.xsd" version="2.1">

<persistence-unit name="punit">
</persistence-unit>

<persistence-unit name="punit2">
</persistence-unit>

A more complete error is

Error creating bean with name 'DWSWageControllerImpl' defined in file [/Users/sja/Development/p20w-slds-poc/target/slds-1.0-SNAPSHOT/WEB-INF/classes/gov/wyo/slds/api/controllers/impl/DWSWageControllerImpl.class] 

Unsatisfied dependency expressed through constructor argument with index 0 of type [gov.wyo.slds.core.services.DWSWageService]: : Error creating bean with name 'DWSWageService': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private gov.wyo.slds.core.repositories.wccc.DWSWageRepository gov.wyo.slds.core.services.impl.DWSWageServiceImpl.dwsWageRepository; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dwsWageRepository': Injection of persistence dependencies failed; nested exception is org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined:

expected single matching bean but found 2: entityManagerFactoryWCCC,entityManagerFactoryDWS;

Any help would be appreciated.

Answer 1

For that you need to have:

1. Two EntityManagerFactory:

   <bean id="entityManagerFactoryWCCC" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
       <property name="dataSource" ref="dataSourceWCCC"/>
       <property name="jpaVendorAdapter">
           <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
       </property>
       <property name="jpaProperties">
           <map>
               <entry key="hibernate.hbm2ddl.auto" value="create-drop"/>
               <entry key="hibernate.show_sql" value="false"/>
           </map>
       </property>
       <property name="packagesToScan" value="org.aaa.slds.core.models.entities.wccc"/>
   </bean>
   
   <bean id="entityManagerFactoryDWS" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
       <property name="dataSource" ref="dataSourceDWS"/>
       <property name="jpaVendorAdapter">
           <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
       </property>
       <property name="jpaProperties">
           <map>
               <entry key="hibernate.hbm2ddl.auto" value="create-drop"/>
               <entry key="hibernate.show_sql" value="false"/>
           </map>
       </property>
       <property name="packagesToScan" value="org.aaa.slds.core.models.entities.dws"/>
   </bean>
   

2. Two JPA transaction managers:

   <bean id="transactionManagerWCCC" class="org.springframework.orm.jpa.JpaTransactionManager">
       <property name="entityManagerFactory" ref="entityManagerFactoryWCCC" />
       <qualifier value="wccc"/>
   </bean>
   
   <bean id="transactionManagerDWS" class="org.springframework.orm.jpa.JpaTransactionManager">
       <property name="entityManagerFactory" ref="entityManagerFactoryDWS" />
       <qualifier value="dws"/>
   </bean>
   

3. And your Service methods are annotated for a specific transaction manager:

   @Transactional("wccc")
   

   or

   @Transactional("dws")
   

If you want to enlist both data sources in a global transaction, you need to use JTA transactions and a JTA transaction manager.