Spring Roo: Two Databases Configuration

spring-roo-logoI’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

Database.properties file original

database.password=<password>
database.url=jdbc\:mysql\://localhost\:3306/twodb
database.username=<username>
database.driverClassName=com.mysql.jdbc.Driver

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

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>

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>

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>

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.



27 Comments

  • Gregg 24 February, 2011, 23:48

    Tried the two db set up and cannot get it to work using
    roo 1.1.0.RELEASE [rev 793f2b0]
    and
    sts-2.5.1.RELEASE

    I keep getting the error

    Error creating bean with name ‘entityManagerFactory’ defined in file [/Users/gregh/springsource.2.5.1/tc-server-developer-2.1.0.RELEASE/spring-insight-instance/wtpwebapps/crud/WEB-INF/classes/META-INF/spring/applicationContext.xml]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory

    Any ideas where I can got to find out how to solve this?

  • Jose Delgado 25 February, 2011, 0:46

    Hi Gregg,

    Don’t worry we will make to work…
    1)I want you to carefully 2ble check the configuration. It is kind-of tricky: involving many different files. One parameter wrong and It screw-up the whole thing. I don’t think is the version at all. I can’t remember what version I came up with it. I believe it was 1.1.1.RELEASE. Execute 1) if still the same 2)please install 1.1.1.RELEASE and give it a try.

    Please throw some lines to tell me what are you doing which such configuration. I am curious as a cat.

    We move it from there…
    B. Roogards
    jD

  • Gregg 25 February, 2011, 2:53

    I’ll upgrade roo. I basically the killer lines are related to “entityManagerFactory2″. When I ad that line that is when I start getting the bean creation error on “entityManagerFactory1″

  • Jose Delgado 25 February, 2011, 3:06

    Weird ?

    Lets do this…
    Create a project backup (zip) file using the backup Roo bck command… Email it to me.
    Let me take a look. I’ll respond asap. I am in the middle of something for the Spring Roo Forum guys right now.

    Thx
    jD

  • Jose Delgado 25 February, 2011, 4:06

    Gregg,

    Do this before emailing anything…
    Execute the pet clinic sample in you environment. Basically validate that Roo is working properly in first place.
    Thx
    jD

  • Gregg 25 February, 2011, 4:49

    Okay I upgraded to roo 1.1.1.RELEASE [rev 156ccd6].

    FILE: roo.log ( I did all this inside the eclipse IDE)

    // Spring Roo 1.1.1.RELEASE [rev 156ccd6] log opened at 2011-02-24 15:12:42
    project –topLevelPackage org.octri –projectName test_release –java 6
    // Spring Roo 1.1.1.RELEASE [rev 156ccd6] log closed at 2011-02-24 15:12:44
    // Spring Roo 1.1.1.RELEASE [rev 156ccd6] log opened at 2011-02-24 15:12:45
    persistence setup –provider HIBERNATE –database MYSQL –userName XXX –password XXX –databaseName XXX
    logging setup –level DEBUG –package PERSISTENCE
    entity –class ~.domain.DbOne
    field string –fieldName name
    entity –class ~.domain.DbTwo
    field string –fieldName name
    controller all –package ~.web
    version

    FILE: database.properties

    #Updated at Thu Feb 24 15:13:18 PST 2011
    #Thu Feb 24 15:13:18 PST 2011
    database.password1=admin
    database.url1=jdbc\:mysql\://localhost\:3306/biolibrary_eval
    database.username1=root
    database.driverClassName=com.mysql.jdbc.Driver

    database.password2=admin
    database.url2=jdbc\:mysql\://localhost\:3306/biolibrary_eval2
    database.username2=root

    FILE: persistence.xml

    org.hibernate.ejb.HibernatePersistence
    org.octri.domain.DbOne



    org.hibernate.ejb.HibernatePersistence
    org.octri.domain.DbTwo
    true



    FILE: applicationContext.xml

    FILE: web.xml

    Spring OpenEntityManagerInViewFilter1
    org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter

    entityManagerFactoryBeanName
    entityManagerFactory1

    Spring OpenEntityManagerInViewFilter2
    org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter

    entityManagerFactoryBeanName
    entityManagerFactory2

    AND….in FILE: web.xml

    Spring OpenEntityManagerInViewFilter1
    /*

    Spring OpenEntityManagerInViewFilter2
    /*

    Resulting server startup:

    2011-02-24 15:42:05,089 [main] ERROR org.springframework.web.context.ContextLoader – Context initialization failed
    org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘transactionManager1′ defined in file [/Users/gregh/springsource.2.5.1/tc-server-developer-2.1.0.RELEASE/spring-insight-instance/wtpwebapps/test_release/WEB-INF/classes/META-INF/spring/applicationContext.xml]: Cannot resolve reference to bean ‘entityManagerFactory1′ while setting bean property ‘entityManagerFactory’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘entityManagerFactory1′ defined in file [/Users/gregh/springsource.2.5.1/tc-server-developer-2.1.0.RELEASE/spring-insight-instance/wtpwebapps/test_release/WEB-INF/classes/META-INF/spring/applicationContext.xml]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit1] Unable to build EntityManagerFactory

  • Gregg 25 February, 2011, 4:58

    I don’t see your email around to send you the zip.

    To test things out, I first rant eh roo script and made sure it worked in single database mode. Then I started adding parts until I can to the final error.

  • Gregg 25 February, 2011, 5:33

    Hi Jose, I don’t see any of my later posts showing up on this page. so I hope not to bombard you.

    I have the pet clinic up and running, I also have a project zip I can send if you can me your email and I tried earlier to post all the config files and the error.

    You can email me at hoshovsk@ohsu.edu

  • Jose Delgado 2 March, 2011, 17:47

    @Gregg,

    I am glad you are up-N-running with the 2 db conf. out there…
    I won’t forget what you did for my “Open Source Better Angel” mission.
    You know that to do next time you need help.

    B. Roogards
    jD

  • Gregg 3 March, 2011, 19:26

    Thanks this now work.

  • Jose Delgado 3 March, 2011, 20:37

    @Gregg,

    Wow this only what you have to say…
    You chase me for several days as I have the obligation of helping you as in a commercial product .

    You were showing more emotion before . when the configuration was not working for you than now that is working. Just look the trace of replies that you left behind.

    You make think that probably you really don’t need the configuration for a project. You just to want to probe that I was incorrect. You were the one skeptical about it not others as you mentioned.

    Btw, there are many other readers before that made it to work without any help at all. Besides this is open source the the source code is delivered “as is”.

    Anyway you have taught me a lesson.

    Thank you

    jD

  • Johnson Davis 29 March, 2011, 23:40

    Hi Jose,
    I am on Spring Roo 1.1.2.RELEASE and am having a similar problem. I have to keep replacing the plain @PersistenceContext with @PersistenceContext(unitName=”persistenceUnit1″) in my *_Roo_Entity.aj domain objects. I know I shouldn’t be editing those files directly, but can’t seem to figure out how to get Roo to set those properly. Hope this is a dumb question with an easy answer…
    Thanks,
    Johnson

  • jD 30 March, 2011, 11:30

    Hi Johnson,

    Just move the referred statements to the correspondent entity classes…
    That will avoid the issue that you are experiencing.

    b roogrards
    jD

  • Shelby 16 August, 2011, 4:19

    Hey JD if you’re still around monitoring this blog I have a question. I’m trying to use two databases, one is an existing one that has to be reverse engineered(read-only entities) and the other will be the write-only database. that’s the goal. I came across your blog when looking through spring and I tried to recreate your project but keep getting this error

    Aug 15, 2011 4:13:51 PM org.apache.catalina.startup.SetContextPropertiesRule begin
    WARNING: [SetContextPropertiesRule]{Context} Setting property ‘source’ to ‘org.eclipse.jst.j2ee.server:twodatabases’ did not find a matching property.
    Aug 15, 2011 4:13:51 PM org.apache.catalina.startup.HostConfig deployDescriptor
    SEVERE: Error deploying configuration descriptor twodatabases.xml

    And I just get a 404 error. Any ideas I can work off of? Thanks a lot.
    Oh and if it matters I’m using the latest roo shell/STS

  • jD 17 August, 2011, 8:50

    @Shelby

    Add following lines to web.xml:

    Spring OpenEntityManagerInViewFilter1
    /*

    Spring OpenEntityManagerInViewFilter2
    /*

    on the entities assign the correspondent persistent unit name in the annotation as shown.

    @PersistenceContext(unitName=”persistenceUnit1″)
    transient EntityManager entityManager;

    This should fix your issue. Unfortunately the article was publish incorrectly

    Roogards
    jD

    • vladsfl 25 August, 2011, 8:45

      Just sharing what I found:

      jDs recipe works!

      But two things:

      1. STS does not seem to allow Push-In @PersistenceContext annotation into main Entity class so just cut and paste it
      2. If you want you unit tests to work add following at the top of the test class @TransactionConfiguration(transactionManager=”fooTransactionManager”)

  • jD 25 August, 2011, 15:58

    @vladsfl,

    Thank you for your “two things”…
    I am really not a big fan of “unit testing” at all. I am more for a “common-sense” testing while in development with a strong system testing at the end of it.

    I think “unit testing” is costly in time and resources and their benefits are always a disappointment: Your point 1) shows what I am saying. -I am sure Spring people do a very good unit testing on STS,

    I believe certain sdlc practices are accepted without verifying their cost/benefit.

    I have had many projects all the way from prototyping to production without an issue surfaced. I am not saying that I am 100% percent sure that there are not issues hidden in my code.
    But the same can be said of any piece of code unit tested.

    Anyway thank you for your feedback

    B. Roogards
    jD

  • vladsfl 25 August, 2011, 16:42

    And one more thing… no need to move

    @PersistenceContext(unitName = “persistenceUnit1″) into the Entity class

    just add attribute to @RooEntity

    @RooEntity(persistenceUnit=”persistenceUnit1″, ….. )

  • jD 26 August, 2011, 10:20

    @vladsfl,

    You are right…
    But be aware that persistenceUnit attribute of the @RooEntity annotation is Roo version dependent.

    Thank you for reviewing my configuration in such detailed way.

    Roogards
    jD

  • Mital Pritmani 20 October, 2011, 18:27

    Great work!!!

    I have not looked at whole tutorial, but your tutorial solved my problem. I was confused how to specify 2 transaction managers with tag. I saw it in your tutorial and came to know that it needs to be specified 2 times for each transaction manager.

    Thanks.

    • jD 20 October, 2011, 23:44

      @Mital Pritmani,

      Awesome!…
      I ‘m glad of being helpful to you.

      B. Roogards
      jD

  • Fernando 2 January, 2012, 19:21

    good morning (Brazil),
    HIbernate application can be started without database,properties? or with wrong data? I need edit/add this properties by own app, in execution time.
    thank’s in advance.

  • BM 24 February, 2012, 1:26

    This is awesome. Thank you so much!

    • jD 24 February, 2012, 11:19

      BM,

      I know, I know… this is what I say every time I look to the mirror*….

      B. and Narcissist Roogards

      *Just kidding.

  • rafeeq 14 June, 2013, 19:17

    i get the exception –

    nested exception is org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 2:

    • rafeeq 14 June, 2013, 19:55

      This error went away after i qualified my entity manager with the persistencecontext annotation

      @PersistenceContext(unitName = "enrichmentDatabasePersistenceUnit")
      
      	private EntityManager enrichmentDbEntityManager;
      
      
  • VL.Ganesh 13 May, 2014, 3:18

    It worked perfectly after added the below lines,

    @PersistenceContext(unitName = “entityManagerFactory1″)
    transient EntityManager entityManager;

    –thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]

Current ye@r *