How to handle multiple database connections with Spring + Jpa + Hibernate

Occasionally, your application needs to retrieve data from another database simultaneously to your primary database.

To archieve it, we have to do some configurations to our spring set up. Notice we are using Spring + Jpa + Hibernate.

<?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:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
                           http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
                           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
                           http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">
 
    <!-- POJOs labeled with the @Controller and @Service annotations are auto-detected. -->
    <context:component-scan base-package="br.com.robsonjr />
     
    <!-- Configurer that replaces ${...} placeholders with values from a properties file -->
    <!-- (in this case, JDBC-related settings for the JPA EntityManager definition below) -->
    <context:property-placeholder location="classpath:resources/spring/data-access.properties" system-properties-mode="OVERRIDE"/>
     
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/WEB-INF/views/" />
        <property name="suffix" value=".jsp" />
    </bean>
     
    <mvc:annotation-driven />
 
    <!-- serve static resources (*.html, ...) from src/main/webapp/ -->
    <mvc:default-servlet-handler />
 
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>      
    </bean>
 
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceUnitName" value="localJpaIntegration" />
        <property name="dataSource" ref="dataSource" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
        </property>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.dialect">${hibernate.dialect}</prop>
                <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
                <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
                <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
            </props>
        </property> 
    </bean>
 
    <!-- gerenciamento da transação pelo spring -->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
    </bean>
     
  <!-- Configurations for second database connection -->
  <bean id="dataSourceSecondDatabase" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
    <property name="driverClassName" value="${jdbc2.driverClassName}"/>
    <property name="url" value="${jdbc2.url}"/>
    <property name="username" value="${jdbc2.username}"/>
    <property name="password" value="${jdbc2.password}"/>    
  </bean>
 
  <bean id="entityManagerFactorySecondDatabase" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="secondDatabaseIntegration" />
    <property name="dataSource" ref="dataSourceSecondDatabase" />
    <property name="jpaVendorAdapter">
      <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    </property>
    <property name="jpaProperties">
      <props>
        <!-- <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop> -->
        <prop key="hibernate.dialect">${hibernate.dialect}</prop>
        <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
        <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
        <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
      </props>
    </property> 
  </bean>
 
  <!-- gerenciamento da transação pelo spring -->
  <bean id="transactionManagerSecondDatabase" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactorySecondDatabase"/>
  </bean>
   
   
    <!-- enables scanning for @Transactional annotations -->
    <tx:annotation-driven />
</beans>

Our persistence.xml file should be as follows:

<?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_2_0.xsd"
             version="2.0">
 
  <persistence-unit name="firstDatabaseIntegration">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>br.com.robsonjr.model.ClassFirstDB</class>
    <exclude-unlisted-classes />
  </persistence-unit>
   
  <persistence-unit name="secondDatabaseIntegration">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>br.com.robsonjr.model.ClassSecondDB</class>
    <exclude-unlisted-classes />
  </persistence-unit>
   
</persistence>

Let’s define our dao for accessing our databases.

The access of each DAO to our database will be through persistence units. Our primary DAO shouldn’t specify this configuration.

@Repository
public class PrimaryDatabaseDao
{
  @PersistenceContext
  private EntityManager manager;
 
  public void doSomeActionPrimaryDatabase(String primaryDatabaseParameter) {
    // do actions and create primary database objects
    // manager.persist(primaryDatabaseObject);
  }
}

Pay attention, and define our non default persistence unit name to the EntityManager.

@Repository
public class SecondDatabaseDao 
{
  @PersistenceContext(unitName="secondDatabaseIntegration") 
  private EntityManager manager;
   
  public void addSomeActionAnotherDatabase(String AnotherDatabaseTableEntityObject)
  {
    manager.persist(AnotherDatabaseTableEntityObject);
  }
}

Next, we are going to define our service for the non default database

public interface SecondDatabaseService
{
  public List getSomeTableName(String value) throws DataAccessException;
}

public class SecondDatabaseServiceImpl implements AnotherDatabaseService
{
    @Autowired
    SecondDatabaseDao secondDatabaseDao;

    @Override
    @Transactional(value="transactionManagerSecondDatabase")
    public void doSomeActionOnSecondDatabase(String value) throws DataAccessException
    {
        // persist something on secondDatabaseDao
    }
}

That’s it.

We are using two databases at once, at the same application.