Using Multiple Entity managers in DAO

Question

I'm trying to configure Spring+Hibernate+JPA for work with two databases ( one for write only i.e insertion & updation & other is only for retrieval.

I did some research & found these possible solutions:

• http://www.studytrails.com/frameworks/spring/spring-hibernate-jpa.jsp
• Multiple database with Spring+Hibernate+JPA
• How do I connect to multiple databases using JPA?

But I stuck at one place & getting this error

No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 2: entityManagerFactoryReadOnly,entityManagerFactoryWriteOnly

What am I doing wrong ?

persistent.read.only.xml:

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

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

    <persistence-unit name="readOnly" transaction-type="RESOURCE_LOCAL">        
        <provider>org.hibernate.ejb.HibernatePersistence</provider>     
        <class>com.demo.domain.Contact</class>  
    </persistence-unit>
</persistence>

persistent.write.only.xml:

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

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

    <persistence-unit name="writeOnly" transaction-type="RESOURCE_LOCAL">       
        <provider>org.hibernate.ejb.HibernatePersistence</provider>     
        <class>com.demo.domain.Contact</class>  
    </persistence-unit>
</persistence>

mcv-dispatcher-servlet.xml:

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:jee="http://www.springframework.org/schema/jee"
    xmlns:lang="http://www.springframework.org/schema/lang" xmlns:p="http://www.springframework.org/schema/p"
    xmlns:tx="http://www.springframework.org/schema/tx" xmlns:util="http://www.springframework.org/schema/util"
    xmlns:oxm="http://www.springframework.org/schema/oxm"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
        http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
        http://www.springframework.org/schema/oxm http://www.springframework.org/schema/oxm/spring-oxm-3.2.xsd">

    <!-- Activates various annotations to be detected in bean classes -->
    <context:annotation-config />

    <!-- Scans the classpath for annotated components that will be auto-registered 
        as Spring beans. For example @Controller and @Service. Make sure to set the 
        correct base-package -->
    <context:component-scan base-package="com.demo" />

    <!-- Setup a simple strategy: 1. Take all the defaults. 2. Return XML by 
        default when not sure. -->
    <!-- Total customization - see below for explanation. -->
    <bean id="cnManager"
        class="org.springframework.web.accept.ContentNegotiationManagerFactoryBean">
        <property name="favorPathExtension" value="true" />
        <property name="ignoreAcceptHeader" value="true" />
        <property name="defaultContentType" value="application/json" />
        <property name="useJaf" value="false" />

        <property name="mediaTypes">
            <map>
                <entry key="json" value="application/json" />
                <entry key="xml" value="application/xml" />
            </map>
        </property>
    </bean>

    <!-- Make this available across all of Spring MVC -->
    <mvc:annotation-driven
        content-negotiation-manager="cnManager" />

    <bean class="com.demo.view.MvcConfiguringPostProcessor" />

    <bean id="propertyConfigurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
        p:location="/WEB-INF/jdbc.properties" />


    <!-- ******************************************************************** -->
    <!--       START: Multiple C3P0 data-sources for DB instance                -->
    <!-- ******************************************************************** -->

    <!-- https://stackoverflow.com/questions/12922351/can-i-use-multiple-c3p0-datasources-for-db-instance -->
    <!-- Using Apache DBCP Data Sources -->
    <bean id="dataSource" 
        abstract="true" >
        <property name="driverClass" value="${db.driverClassName}" />
        <property name="user" value="${db.username}" />
        <property name="password" value="${db.password}" />
        <property name="idleConnectionTestPeriod" value="${db.idleConnectionTestPeriod}" />
        <property name="preferredTestQuery" value="select 1" />
        <property name="testConnectionOnCheckin" value="true" />
    </bean>

    <bean id="dataSourceReadOnly" 
        parent="dataSource"
        class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="jdbcUrl" value="${db.readOnlyDataBaseUrl}" />
    </bean>
    <bean id="dataSourceWriteOnly" 
        parent="dataSource"
        class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="jdbcUrl" value="${db.writeOnlyDataBaseUrl}" />
    </bean>

    <!-- ******************************************************************** -->
    <!--       END: Multiple C3P0 data-sources for DB instance              -->
    <!-- ******************************************************************** -->


    <bean id="jpaVendorProvider"
        class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="database" value="MYSQL" />
        <property name="databasePlatform" value="${db.dialect}" />
        <property name="showSql" value="true" />
        <property name="generateDdl" value="true" />
    </bean>

<!--    <bean -->
<!--        class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"> -->
<!--        <property name="defaultPersistenceUnitName" value="readOnly" /> -->
<!--     </bean> -->

    <bean id="persistenceUnitManager"
        class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
        <!-- defining multiple persistence unit -->
        <property name="persistenceXmlLocations">
            <list>
                <value>/META-INF/persistence.read.only.xml</value>
                <value>/META-INF/persistence.write.only.xml</value>
            </list>
        </property>
        <property name="defaultDataSource" ref="dataSourceReadOnly" /> 
        <property name="dataSources">
            <map>
                <entry key="readOnlyDsKey" value-ref="dataSourceReadOnly" />
                <entry key="writeOnlyDsKey" value-ref="dataSourceWriteOnly" />
            </map>
        </property>
    </bean>

    <bean id="entityManagerFactoryReadOnly"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<!--        <property name="dataSource" ref="dataSourceReadOnly" />      -->
        <property name="persistenceUnitManager" ref="persistenceUnitManager" />
        <property name="jpaVendorAdapter" ref="jpaVendorProvider" />
        <property name="persistenceUnitName" value="readOnly" />
        <!-- entityManagerFactory does not specify persistenceUnitName property 
            because we're defining more than one persistence unit -->
        <!-- <property name="persistenceUnitName" value="hello_mysql" /> -->
        <!-- <property name="persistenceXmlLocation" value="/META-INF/persistence.xml" /> -->
    </bean>

    <bean id="entityManagerFactoryWriteOnly"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<!--        <property name="dataSource" ref="dataSourceWriteOnly" />         -->
        <property name="persistenceUnitManager" ref="persistenceUnitManager" />
        <property name="jpaVendorAdapter" ref="jpaVendorProvider" />
        <property name="persistenceUnitName" value="writeOnly" />
    </bean>

    <!-- ******************************************************************** -->
    <!-- Mark bean transactions as annotation driven -->
    <!-- ******************************************************************** -->
    <tx:annotation-driven transaction-manager="transactionManagerReadOnly" />
    <tx:annotation-driven transaction-manager="transactionManagerWriteOnly" />

    <!-- ******************************************************************** -->
    <!-- Setup the transaction manager -->
    <!-- ******************************************************************** -->

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

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


</beans>

My DAO:

package com.demo.dao;

import java.util.Collections;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.demo.domain.Contact;

//import java.util.Collections;

@Repository("ContactDAO")
@Transactional
public class ContactDAOImpl extends AppDAOimpl<Contact> implements ContactDAO {

    /**
     * 
     */
    private static final long serialVersionUID = 3986253823316728444L;

    /**
     * EntityManager injected by Spring for persistence unit MYSQL
     * 
     */
    @PersistenceContext(unitName = "readOnly")
    @Qualifier("entityManagerFactoryReadOnly")
    private EntityManager entityManager;

    /**
     * Get the entity manager that manages persistence unit MYSQL
     * 
     */
    public EntityManager getEntityManager() {
        return entityManager;
    }

    /**
     * EntityManager injected by Spring for persistence unit MYSQL
     * 
     */
    @PersistenceContext(unitName = "writeOnly")
    @Qualifier("entityManagerFactoryWriteOnly")
    private EntityManager woEntityManager;

    /**
     * Get the entity manager that manages persistence unit MYSQL
     * 
     */
    public EntityManager getWoEntityManager() {
        return woEntityManager;
    }


    // other functions goes here 
}

Both the databases have the same schema ( read & write ).

Answer 1

We have a similar setup in a project here, and I think

@PersistenceContext(unitName = "writeOnly")
private EntityManager woEntityManager;

is sufficient, you don't need the additional Qualifier. But in my experience, you have to set the attribute on Transactional, too. So drop the Transactional annotation on the DAO class and start marking individual methods with

@Transactional(value="transactionManagerReadOnly")

and i believe the tx:annotation-driven element in the context doesn't work with multiple contexts, too.

And ideally, the whole stuff belongs into the service layer anyway, you don't want your DAOs to decide or even know which Persistence context they're called from. So you'd have a ReadContactService:

 @PersistenceContext(unitName = "readOnly")
 private EntityManager em;

 @Transactional(value="transactionManagerReadOnly")
 public Contact readContact(int id) {
   return dao.findById(em, id);
 }

and a WriteContactService:

 @PersistenceContext(unitName = "writeOnly")
 private EntityManager em;

 @Transactional(value="transactionManagerWriteOnly")
 public void writeContact(String name, String address) {
   return dao.writeContact(em, name, address);
 }

and a DAO that is unaware of the context. Then you need only N entity classes and you can reuse DAO methods (even writeOnly will eventually have to read from the database, trust me).

Answer 2

JTA transaction manager. is answer to my question. Below are links for references.

• JPA Multiple Transaction Managers
• Spring multiple @Transactional datasources

& here is nice tutorial about integrating JTA with spring.

http://www.javacodegeeks.com/2013/07/spring-jta-multiple-resource-transactions-in-tomcat-with-atomikos-example.html

Answer 3

Add two classes

ContactWrite.java on top declare the schema and table like below

@Table(name = "Contact", schema="DB1")

Do the same for the other table in the DB2

ContactRead.java

@Table(name = "Contact", schema="DB2")

Now use these two classes in the persistance xml files like below.

persistent.read.only.xml:

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

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

    <persistence-unit name="readOnly" transaction-type="RESOURCE_LOCAL">        
        <provider>org.hibernate.ejb.HibernatePersistence</provider>     
        <class>com.demo.domain.ContactWrite</class>  
    </persistence-unit>
</persistence>

persistent.write.only.xml:

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

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

    <persistence-unit name="writeOnly" transaction-type="RESOURCE_LOCAL">       
        <provider>org.hibernate.ejb.HibernatePersistence</provider>     
        <class>com.demo.domain.ContactRead</class>  
    </persistence-unit>
</persistence>