How to setup multiple data sources with Spring and JPA

Question

In our application, we want to set multiple data source with Spring and JPA. Hence we have created 2 entityManagerFactory, 2 data source and 2 transaction- manager.

web.xml

 <param-value>
    /WEB-INF/a_spring.xml
    /WEB-INF/b_spring.xml
 </param-value>

Persistence.xml

<?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="db1" transaction-type="RESOURCE_LOCAL">
        <class>com.rh.domain.RcA</class>
    </persistence-unit>

      <persistence-unit name="db2" transaction-type="RESOURCE_LOCAL">
      <class>com.rh.domain.Rcb</class>
    </persistence-unit>
</persistence>

a_spring.xml

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

    <beans xmlns="http://www.springframework.org/schema/beans"
           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/tx
           http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
           http://www.springframework.org/schema/aop
           http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">

      <bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"/>  
      <bean id = "RcMaintenanceService" class="com.rh.services.RcAbcMaintenanceServiceImpl" autowire="byName" />

    <aop:config>
            <aop:pointcut id="rOperation" expression="execution(* com.rh.services.*.*(..))"/>
            <aop:advisor advice-ref="txAdvice" pointcut-ref="rOperation"/>
        </aop:config>

    <tx:advice id="txAdvice" transaction-manager="transactionManager">
            <tx:attributes>
                   <tx:method name="*"/>
            </tx:attributes>
        </tx:advice>

    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
            <property name="jndiName" value="java:comp/env/jdbc/db1" />
        </bean> 
        <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
            <property name="entityManagerFactory" ref="entityManagerFactory"/>
            <property name="dataSource" ref="dataSource"/>
        </bean>

        <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
               <property name="persistenceUnitName" value="db1" />     
            <property name="dataSource" ref="dataSource"/>
            <property name="jpaVendorAdapter">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> 
                    <property name="showSql" value="true"/>
                    <property name="generateDdl" value="false"/>
                    <property name="database" value="MYSQL" />
                    <property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect"/>
                </bean>
            </property>
            <property name="jpaDialect">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect">
                </bean>
            </property>
        </bean>

I also declare another entityManagetFactory,Transaction Manager and dataSource to b_spring.xml.

Error

Initialization of bean failed; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No unique bean of type [javax.persistence.EntityManagerFactory] is defined: expected single bean but found 2 Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No unique bean of type [javax.persistence.EntityManagerFactory] is defined: expected single bean but found 2 at org.springframework.beans.factory.BeanFactoryUtils.beanOfTypeIncludingAncestors(BeanFactoryUtils.java:303) at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.findDefaultEntityManagerFactory(PersistenceAnnotationBeanPostProcessor.java:451) at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.findEntityManagerFactory(PersistenceAnnotationBeanPostProcessor.java:428) at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor$AnnotatedMember.resolveEntityManager(PersistenceAnnotationBeanPostProcessor.java:582) at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor$AnnotatedMember.resolve(PersistenceAnnotationBeanPostProcessor.java:553) at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor$AnnotatedMember.inject(PersistenceAnnotationBeanPostProcessor.java:489)

Answer 1

The error message you've posted indicates that you're autowiring by type for an object of type EntityManagerFactory. None of the code you've shown so far contains such an injection, which implies that it's probably in some code you haven't yet posted.

If you were to post the full stack trace of the error, you'd be able to walk up the stack to see which bean contains the un-satisfiable reference to an EntityManagerFactory object, which in turn would let you change how you're referencing it to allow a reference to the specific bean you want.

Update

Based on the further information you provided (thank you) and some Googling, it seems as though other users have similarly found that specifying a unitName isn't enough to get the correct EntityManager injected. Several posts online back up @lucid's recommendation to use the @Qualifier annotation to get Spring to select the correct bean, but unfortunately that annotation was introduced in 2.5 so it's only available to you if you upgrade. (Which, given the age of the Spring framework that you're using, is probably a good idea, but that's a separate conversation.)

However, several users have indicated that an alternate approach is available in 2.0.5, using a single PersistenceUnitManager that references multiple data sources rather than multiple persistence units that each reference a single data source. From the official Spring docs: https://docs.spring.io/spring/docs/2.0.x/reference/orm.html#orm-jpa-multiple-pu.

Overall I'd suggest you consider upgrading to a version of Spring that's not more than a decade old, which would let you specify an @Qualifier annotation per @lucid's answer. But if that's not possible for some reason, the PersistenceUnitManager approach should give you a way to make it work within Spring 2.0.5.

Answer 2

In case of multiple data source configuration, we need to define which one will be considered as a primary data source. we can specify that using @Primary annotation in java config or primary=true in XML bean config.

As there is two entity manager being created in XML, we need to use @Qualifier to specify which bean should be injected where. In your case, something like this.

@PersistenceContext(unitName = "db1")
public void setEntityManager(@Qualifier("entityManagerFactory") EntityManager entityMgr) {
    this.em = entityMgr;
}

for XML configuration, we can do something like this

<bean id="BaseService" class="x.y.z.BaseService">
    <property name="em" ref="entityManagerFactory"/>
    <property name="em1" ref="entityManagerFactory1"/>
</bean>

<bean id = "RcMaintenanceService" class="com.rh.services.RcAbcMaintenanceServiceImpl" autowire="byName" parent="BaseService"/>

Answer 3

Did you try providing the package details which contains your EntityManagerFactory bean?

You can provide the package details as a property in you bean definition -

<property name="packagesToScan" value="com.XX.XX.XX.XX" />

new property to be added in this block -

<bean id="entityManagerFactory1" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
           <property name="persistenceUnitName" value="db2" />     
        <property name="dataSource" ref="dataSource1"/>
  <-- add here for both beans  -->

        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> 
                <property name="showSql" value="true"/>
                <property name="generateDdl" value="false"/>
                <property name="database" value="MYSQL" />
                <property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect"/>
            </bean>
        </property>
        <property name="jpaDialect">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect">
            </bean>
        </property>
    </bean>

Also, you are missing the persistenceXmlLocation property -

 <property name="persistenceXmlLocation" value="***/persistence.xml" />

Answer 4

There are a couple of things that don't look good to me. The name of the setters won't match the property names, I think that is important, the second thing is about the inheritance, some annotations sometimes only works in concrete classes not in base classes. I would try to change the base service as follow.

public class BaseService {

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

@PersistenceContext(unitName = "db1")
private EntityManager em1;

public EntityManager getEm() {
    return em;
}

protected EntityManager getEm2() {
    return em1;
}

public void setEm(EntityManager entityMgr) {
    this.em = entityMgr;
}

public void setEm1(EntityManager entityMgr) {
    this.em = entityMgr;
}
}

If that does not work I probably try to remove the base class and see if I put the annotation under the concrete class I can make that works, I would do that moving just the annotations to the RcAbcMaintenanceServiceImpl class and remove the extend statement that inherits from the BaseService

Also, I noticed that the PersistenceContext annotation has another param https://docs.oracle.com/javaee/6/api/javax/persistence/PersistenceContext.html, so you could try to use the name as well to match the id on the bean definition.