Multiple database with Spring+Hibernate+JPA

Question

I'm trying to configure Spring+Hibernate+JPA for work with two databases (MySQL and MSSQL).

My datasource-context.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:aop="http://www.springframework.org/schema/aop"
 xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
  http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
  http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.5.xsd"
 xmlns:p="http://www.springframework.org/schema/p" xmlns:tx="http://www.springframework.org/schema/tx"
 xmlns:util="http://www.springframework.org/schema/util">

 <!--
 Data Source config 
  -->
 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
  destroy-method="close" p:driverClassName="${local.jdbc.driver}" p:url="${local.jdbc.url}"
  p:username="${local.jdbc.username}" p:password="${local.jdbc.password}">
 </bean>

 <bean id="dataSourceRemote" class="org.apache.commons.dbcp.BasicDataSource"
  destroy-method="close" p:driverClassName="${remote.jdbc.driver}"
  p:url="${remote.jdbc.url}" p:username="${remote.jdbc.username}"
  p:password="${remote.jdbc.password}" />

 <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"
  p:entity-manager-factory-ref="entityManagerFactory" />

 <!-- 
    JPA config   
    -->
 <tx:annotation-driven transaction-manager="transactionManager" />

 <bean id="persistenceUnitManager"
  class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
        <property name="persistenceXmlLocations">
   <list value-type="java.lang.String">
    <value>classpath*:config/persistence.local.xml</value>
    <value>classpath*:config/persistence.remote.xml</value>
   </list>
  </property>

  <property name="dataSources">
   <map>
    <entry key="localDataSource" value-ref="dataSource" />
    <entry key="remoteDataSource" value-ref="dataSourceRemote" />
   </map>
  </property>
  <property name="defaultDataSource" ref="dataSource" />
 </bean>

 <bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="jpaVendorAdapter">
   <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
    p:showSql="true" p:generateDdl="true">
   </bean>
  </property>
  <property name="persistenceUnitManager" ref="persistenceUnitManager" />
<property name="persistenceUnitName" value="localjpa"/>
 </bean>

 <bean
  class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />

</beans>

Each persistence.xml contains one unit, like this:

<persistence-unit name="remote" transaction-type="RESOURCE_LOCAL">
  <properties>
   <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.DefaultNamingStrategy" />
   <property name="hibernate.dialect" value="${remote.hibernate.dialect}" />
   <property name="hibernate.hbm2ddl.auto" value="${remote.hibernate.hbm2ddl.auto}" />
  </properties>
 </persistence-unit>

PersistenceUnitManager cause following exception:

Cannot resolve reference to bean 'persistenceUnitManager' while setting bean property 'persistenceUnitManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'persistenceUnitManager' defined in class path resource [config/datasource-context.xml]: Initialization of bean failed; nested exception is org.springframework.beans.TypeMismatchException: Failed to convert property value of type [java.util.ArrayList] to required type [java.lang.String] for property 'persistenceXmlLocation'; nested exception is java.lang.IllegalArgumentException: Cannot convert value of type [java.util.ArrayList] to required type [java.lang.String] for property 'persistenceXmlLocation': no matching editors or conversion strategy found

If left only one persistence.xml without list, every works fine but I need 2 units...

I also try to find alternative solution for work with two databases in Spring+Hibernate context, so I would appreciate any solution.

New error after changing to persistenceXmlLocations:

No single default persistence unit defined in {classpath:config/persistence.local.xml, classpath:config/persistence.remote.xml}

Update:

I add persistenceUnitName, it works, but only with one unit, still need help.

Update:

I changed config files: datasource-context.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:aop="http://www.springframework.org/schema/aop"
    xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.5.xsd"
    xmlns:p="http://www.springframework.org/schema/p" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:util="http://www.springframework.org/schema/util">

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close" p:driverClassName="${local.jdbc.driver}" p:url="${local.jdbc.url}"
        p:username="${local.jdbc.username}" p:password="${local.jdbc.password}">
    </bean>

    <bean id="dataSourceRemote" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close" p:driverClassName="${remote.jdbc.driver}"
        p:url="${remote.jdbc.url}" p:username="${remote.jdbc.username}"
        p:password="${remote.jdbc.password}">
    </bean>

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

    <bean id="persistenceUnitManager"
        class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
        <property name="persistenceXmlLocation" value="${persistence.xml.location}" />
        <property name="defaultDataSource" ref="dataSource" /> <!-- problem -->
        <property name="dataSources">
            <map>
                <entry key="local" value-ref="dataSource" />
                <entry key="remote" value-ref="dataSourceRemote" />
            </map>
        </property>
    </bean>

    <bean id="entityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
                p:showSql="true" p:generateDdl="true">
            </bean>
        </property>
        <property name="persistenceUnitManager" ref="persistenceUnitManager" />
        <property name="persistenceUnitName" value="pu1" />
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean id="entityManagerFactoryRemote"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
                p:showSql="true" p:generateDdl="true">
            </bean>
        </property>
        <property name="persistenceUnitManager" ref="persistenceUnitManager" />
        <property name="persistenceUnitName" value="pu2" />
        <property name="dataSource" ref="dataSourceRemote" />
    </bean>

    <tx:annotation-driven />

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"
        p:entity-manager-factory-ref="entityManagerFactory" />


    <bean id="transactionManagerRemote" class="org.springframework.orm.jpa.JpaTransactionManager"
        p:entity-manager-factory-ref="entityManagerFactoryRemote" />

</beans>

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<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_1_0.xsd"
    version="1.0">

    <persistence-unit name="pu1" transaction-type="RESOURCE_LOCAL">
        <properties>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.DefaultNamingStrategy" />
            <property name="hibernate.dialect" value="${local.hibernate.dialect}" />
            <property name="hibernate.hbm2ddl.auto" value="${local.hibernate.hbm2ddl.auto}" />                          
        </properties>
    </persistence-unit>

    <persistence-unit name="pu2" transaction-type="RESOURCE_LOCAL">
        <properties>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.DefaultNamingStrategy" />
            <property name="hibernate.dialect" value="${remote.hibernate.dialect}" />
            <property name="hibernate.hbm2ddl.auto" value="${remote.hibernate.hbm2ddl.auto}" />
        </properties>
    </persistence-unit>

</persistence>

Now it builds two entityManagerFactory, but both are for Microsoft SQL Server

[main] INFO org.hibernate.ejb.Ejb3Configuration - Processing PersistenceUnitInfo [
    name: pu1
    ...]
[main] INFO org.hibernate.cfg.SettingsFactory - RDBMS: Microsoft SQL Server

[main] INFO org.hibernate.ejb.Ejb3Configuration - Processing PersistenceUnitInfo [
    name: pu2
    ...]
[main] INFO org.hibernate.cfg.SettingsFactory - RDBMS: Microsoft SQL Server (but must MySQL)

I suggest, that use only dataSource, dataSourceRemote (no substitution) is not worked. That's my last problem.

Answer 1

You need to use persistenceXmlLocations property (note the plural) rather than persistenceXmlLocation. It's a string array, so it'll be auto-converted from list:

<bean id="persistenceUnitManager"
      class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
  <property name="persistenceXmlLocations"><list>
    <value>classpath*:config/persistence.local.xml</value>
    <value>classpath*:config/persistence.remote.xml</value>
  </list></property>
  ...

Update (based on edit)

Your entityManagerFactory does not specify persistenceUnitName property. You have to do so explicitly because you're defining more than one persistence unit and entityManagerFactory has to know which one to use.

Answer 2

Justin, number 3 can be done in a more standard way like this:

1. In your Spring context:

   <context:annotation-config />
   

2. In your Spring-managed DAOs (note the unitName property):

   @PersistenceContext(unitName = "pu1"`) protected EntityManager entityManager;
   

This way a properly intantiated EntityManager corresponding to the persistence unit named "pu1" would be injected in the corresponding DAOs.

Answer 3

If you follow this tutorial, http://www.javacodegeeks.com/2010/05/jboss-42x-spring-3-jpa-hibernate.html you can make the following changes to access two different databases:

1. persistence.xml, define a second pesristence-unit for your second database.
2. spring.xml, define a second entityManagerFactory bean under a different name, lets say "entityManagerFactoryDB2" and configure it to use the persistent unit for the second database.

3. for every DAO you want to access the second database include the following :

   @Autowired
   private EntityManagerFactory entityManagerFactoryDB2;
   
   @PostConstruct
   public void init() {
       super.setEntityManagerFactory(entityManagerFactoryDB2);
   }
   

Thats all!

On spring service classes, use the DAOs as usual!