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
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-unitname="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><propertyname="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 --><propertyname="hibernate.hbm2ddl.auto"value="update"/><propertyname="hibernate.ejb.naming_strategy"value="org.hibernate.cfg.ImprovedNamingStrategy"/><propertyname="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-unitname="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><propertyname="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 --><propertyname="hibernate.hbm2ddl.auto"value="update"/><propertyname="hibernate.ejb.naming_strategy"value="org.hibernate.cfg.ImprovedNamingStrategy"/><propertyname="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
<beanclass="org.apache.commons.dbcp.BasicDataSource"destroy-method="close"id="dataSource1"><propertyname="driverClassName"value="${database.driverClassName}"/><propertyname="url"value="${database.url1}"/><propertyname="username"value="${database.username1}"/><propertyname="password"value="${database.password1}"/><propertyname="validationQuery"value="SELECT 1 FROM DUAL"/><propertyname="testOnBorrow"value="true"/></bean><beanclass="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"id="entityManagerFactory1"><propertyname="dataSource"ref="dataSource1"/><propertyname="persistenceUnitName"value="persistenceUnit1"/></bean><tx:annotation-drivenmode="aspectj"transaction-manager="transactionManager1" /><beanclass="org.springframework.orm.jpa.JpaTransactionManager"id="transactionManager1"><propertyname="entityManagerFactory"ref="entityManagerFactory1" /></bean><beanclass="org.apache.commons.dbcp.BasicDataSource"destroy-method="close"id="dataSource2"><propertyname="driverClassName"value="${database.driverClassName}"/><propertyname="url"value="${database.url2}"/><propertyname="username"value="${database.username2}"/><propertyname="password"value="${database.password2}"/><propertyname="validationQuery"value="SELECT 1 FROM DUAL"/><propertyname="testOnBorrow"value="true"/></bean><beanclass="org.springframework.orm.jpa.JpaTransactionManager"id="transactionManager2"><propertyname="entityManagerFactory"ref="entityManagerFactory2"/></bean><tx:annotation-drivenmode="aspectj"transaction-manager="transactionManager2" /><beanclass="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"id="entityManagerFactory2"><propertyname="dataSource"ref="dataSource2"/><propertyname="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.
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.
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?
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.
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”
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.
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
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
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.
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.
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.
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”.
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
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
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”)
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.
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.
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.
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:
I do push-in with STS and everything seems fine, the tables are created on the corresponding DB and the app seems start ok.
— Console log 2014-12-05 18:29:09,935 [main] INFO org.springframework.web.servlet.DispatcherServlet – FrameworkServlet ‘dosdbs’: initialization completed in 936 ms Dec 5, 2014 6:29:09 PM org.apache.coyote.http11.Http11Protocol init INFO: Initializing Coyote HTTP/1.1 on http-8080 Dec 5, 2014 6:29:09 PM org.apache.coyote.http11.Http11Protocol start INFO: Starting Coyote HTTP/1.1 on http-8080
But when I go to htttp://localhost:8080//myapp/ the app crash
— Console log Dec 5, 2014 6:29:19 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet dosdbs threw exception org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 2: entityManagerFactory_db1,entityManagerFactory_db2
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?
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
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”
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
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
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
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.
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 [email protected]
@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
Thanks this now work.
@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
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
Hi Johnson,
Just move the referred statements to the correspondent entity classes…
That will avoid the issue that you are experiencing.
b roogrards
jD
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
@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
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”)
@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
And one more thing… no need to move
@PersistenceContext(unitName = “persistenceUnit1″) into the Entity class
just add attribute to @RooEntity
@RooEntity(persistenceUnit=”persistenceUnit1”, ….. )
@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
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.
@Mital Pritmani,
Awesome!…
I ‘m glad of being helpful to you.
B. Roogards
jD
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.
This is awesome. Thank you so much!
BM,
I know, I know… this is what I say every time I look to the mirror*….
B. and Narcissist Roogards
*Just kidding.
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:
This error went away after i qualified my entity manager with the persistencecontext annotation
It worked perfectly after added the below lines,
@PersistenceContext(unitName = “entityManagerFactory1″)
transient EntityManager entityManager;
–thank you
Hi,
I do push-in with STS and everything seems fine, the tables are created on the corresponding DB and the app seems start ok.
— Console log
2014-12-05 18:29:09,935 [main] INFO org.springframework.web.servlet.DispatcherServlet – FrameworkServlet ‘dosdbs’: initialization completed in 936 ms
Dec 5, 2014 6:29:09 PM org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
Dec 5, 2014 6:29:09 PM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
But when I go to htttp://localhost:8080//myapp/ the app crash
— Console log
Dec 5, 2014 6:29:19 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet dosdbs threw exception
org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 2: entityManagerFactory_db1,entityManagerFactory_db2
I try like this:
and this:
and this:
and this:
and this:
and I can’t run my app :”(
Can you help me, please.
Hi, yesterday I publish somthing but I don’t see on the block
stupid to ask but i want to see the whole process in project, is there a link of complete project.