ViralPatel.net

I’ve seen very often a recurrent question on Spring Roo forums. The question is “How to configure the application to handle two databases”. Certainly this is a more ample question to Spring Roo only, due it involves JPA and the Spring persistence stack. So, I believe it will be of interest to all the Spring development community that face such problem. I am assuming the Reader is a Java Developer with some familarity with Spring Roo. I focus on to how to get to configuration only. For help on how to use Roo, please visit http://www.springsource.org/roo.

Hands on the problem

I created the following minimal Roo project (shown below) for explaining the setup. I’ll modify the configuration in a way that each entity will come from different independent databases.

project --topLevelPackage org.pragmatikroo.twodb
persistence setup --provider HIBERNATE --database MYSQL --userName <username> --password <password> --databaseName twodbone
logging setup --level DEBUG --package PERSISTENCE 
entity --class ~.domain.DbOne
field string --fieldName name
entity --class ~.domain.DbTwo
field string --fieldName name
perform clean
perform eclipse
exit
Code language: HTML, XML (xml)

Database.properties file original

database.password=<password>
database.url=jdbc\:mysql\://localhost\:3306/twodb
database.username=<username>
database.driverClassName=com.mysql.jdbc.Driver
Code language: HTML, XML (xml)

Database.properties file modified

database.password1=<password1>
database.url1=jdbc\:mysql\://localhost\:3306/twodbone
database.username1=<username1>
database.password2=<password1>
database.url2=jdbc\:mysql\://localhost\:3306/twodbtwo
database.username2=<username2>
database.driverClassName=com.mysql.jdbc.Driver
Code language: HTML, XML (xml)

Basically, I have duplicated the code from the original file delivered by Roo to define the 2nd database. This pattern would be repeated for the other files as well.

Persistence.xml file modified

<persistence-unit name="persistenceUnit1" transaction-type="RESOURCE_LOCAL">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>org.josean.twodb.domain.DbOne</class>
      <exclude-unlisted-classes>true</exclude-unlisted-classes>
      <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <!-- Uncomment the following two properties for JBoss only -->
            <!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
            <!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
            </properties>
</persistence-unit>
<persistence-unit name="persistenceUnit2" transaction-type="RESOURCE_LOCAL">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>org.josean.twodb.domain.DbTwo</class>
      <exclude-unlisted-classes>true</exclude-unlisted-classes>
      <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <!-- Uncomment the following two properties for JBoss only -->
            <!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
            <!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
            </properties>
</persistence-unit>
Code language: HTML, XML (xml)

I duplicated the persistence unit code for the 2nd database and define unique ids. Important: Add the <class> and <exclude-unlisted-classes> as shown in the code above. As implied each entity handled by this persistence unit have to be included.

ApplicationContext.xml file modified

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource1">
      <property name="driverClassName" value="${database.driverClassName}"/>
      <property name="url" value="${database.url1}"/>
      <property name="username" value="${database.username1}"/>
      <property name="password" value="${database.password1}"/>
      <property name="validationQuery" value="SELECT 1 FROM DUAL"/>
      <property name="testOnBorrow" value="true"/>
</bean>
<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory1">
      <property name="dataSource" ref="dataSource1"/>
      <property name="persistenceUnitName" value="persistenceUnit1"/>
</bean>
<tx:annotation-driven mode="aspectj" transaction-manager="transactionManager1" />
<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager1">
      <property name="entityManagerFactory" ref="entityManagerFactory1" />
</bean>
<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource2">
      <property name="driverClassName" value="${database.driverClassName}"/>
      <property name="url" value="${database.url2}"/>
      <property name="username" value="${database.username2}"/>
      <property name="password" value="${database.password2}"/>
      <property name="validationQuery" value="SELECT 1 FROM DUAL"/>
      <property name="testOnBorrow" value="true"/>
</bean>
<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager2">
      <property name="entityManagerFactory" ref="entityManagerFactory2"/>
</bean>
<tx:annotation-driven mode="aspectj" transaction-manager="transactionManager2" />
<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory2">
      <property name="dataSource" ref="dataSource2"/>
      <property name="persistenceUnitName" value="persistenceUnit2"/>
</bean>
Code language: HTML, XML (xml)

Same process as the other files. Duplicate as shown. Add property attribute to the entityManagerFactory beans.

Web.xml file modified

<filter>
      <filter-name>Spring OpenEntityManagerInViewFilter1</filter-name>
      <filter-class>org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter</filter-class>
      <init-param>
            <param-name>entityManagerFactoryBeanName</param-name>
            <param-value>entityManagerFactory1</param-value>
      </init-param>
</filter>
<filter>
      <filter-name>Spring OpenEntityManagerInViewFilter2</filter-name>
      <filter-class>org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter</filter-class>
      <init-param>
            <param-name>entityManagerFactoryBeanName</param-name>
            <param-value>entityManagerFactory2</param-value>
      </init-param>
</filter>
Code language: HTML, XML (xml)

This is it!. Application ready for deploying and testing.

Conclusion

I showed the interested Reader how to setup the application context of a Spring web app to access 2nd databases, in particular to Spring Roo Developer.