Deal of the Day

Home » Main » Manning Forums » 2010 » Spring Roo in Action

Thread: Solution for connecting multiple databases using JPA + Hibernate

Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List

Permlink Replies: 13 - Pages: 1 - Last Post: Feb 28, 2013 2:25 AM by: jbbarquero Threads: [ Previous | Next ]
sanjoy

Posts: 4
From: London
Registered: 1/20/11
Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 20, 2011 6:25 PM
  Click to reply to this thread Reply

Here is the setup:

In persistence.xml
------------------


<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence </provider>
<class>com.abc.neotest.model.Person</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.driver_class" value="com.mysql.jdbc.Driver"/>
<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/neotest"/>
<property name="hibernate.connection.username" value="root"/>
<property name="hibernate.connection.password" value="admin"/>
</properties>
</persistence-unit>

<persistence-unit name="staticPersistenceUnit" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence </provider>
<class>com.abc.neotest.model.Title</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.driver_class" value="com.mysql.jdbc.Driver"/>
<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/neostatic"/>
<property name="hibernate.connection.username" value="root"/>
<property name="hibernate.connection.password" value="admin"/>

</properties>
</persistence-unit>


In applicationContext.xml
------------------------

<bean id="pum" class="org.springframework.orm.jpa.persistenceunit .DefaultPersistenceUnitManager">
<property name="persistenceXmlLocations">
<list>
<value>classpath*:META-INF/persistence.xml</value>
</list>
</property>
</bean>

<bean class="org.springframework.orm.jpa.JpaTransactionM anager" id="transactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>

<bean class="org.springframework.orm.jpa.JpaTransactionM anager" id="transactionManagerStatic">
<property name="entityManagerFactory" ref="entityManagerFactoryStatic"/>
</bean>

<tx:annotation-driven mode="aspectj" transaction-manager="transactionManager"/>
<tx:annotation-driven mode="aspectj" transaction-manager="transactionManagerStatic"/>


<bean class="org.springframework.orm.jpa.LocalContainerE ntityManagerFactoryBean" id="entityManagerFactory">
<property name="persistenceUnitManager" ref="pum"/>
<property name="persistenceUnitName" value="persistenceUnit"/>
</bean>

<bean class="org.springframework.orm.jpa.LocalContainerE ntityManagerFactoryBean" id="entityManagerFactoryStatic">
<property name="persistenceUnitManager" ref="pum"/>
<property name="persistenceUnitName" value="staticPersistenceUnit"/>
</bean>


My two entities are:
-------------------

In Db1 ->

@RooJavaBean
@RooToString
@RooEntity
public class Person {


private String firstName;

@Temporal(TemporalType.TIMESTAMP)
@DateTimeFormat(style = "S-")
private Date dateOfBirth;
}


In Db2 ->

@RooJavaBean
@RooToString
@RooEntity
public class Title {

}


Thanks

delgad9

Posts: 51
From: az
Registered: 8/18/10
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 22, 2011 12:09 AM   in response to: sanjoy in response to: sanjoy
  Click to reply to this thread Reply

Another configuration that supports connection pool...


Please visit my showcases web site to see it working at https://pragmatikroo.org/showcases. Select the "Two databases" (TDBs) showcase.

TDBs is Roo web app with independent CRUD operations against two mysql db. No "all entities get created in both databases" issue.


This is what I did.


On the application.xml file: I created another datasource, entityManager and transactionManager beans for the 2nd db. So each db had is own set of beans. Not to mention that each bean should have unique id.

On the database.properties: Added parameters for 2nd db and modified to properly work with application.xml.


On the persistence.xml: Duplicate original persistence unit and named both with different names. Use the <class></class> and the <exclude-unlisted-classes>true</exclude-unlisted-classes> elements. The <classes> element should group the entities only managed with correspondent PU.

Important: Add a persistenceUnitName property to each one of your entityManagerFactory with the name you set you persistent unit of the persistence.xml file.

I mean: <property name="persistenceUnitName" value="your persistence unit name here"/>


On the web.xml: Add a second OpenEntityManagerInViewFilter and set the entityManagerFactoryBeanName parameter with the id of entityManageFactories defined in application.xml

Finally on Entities files: annotate EntityManager variable with @PersistenceContext(unitName="you persistence unit name here").


Note: As you can see this configuration uses a connection pool. As the original single source created by Roo.

Well, If interested try it and post how it goes on your box.

Feedback is always welcome

Cheers
jD

sanjoy

Posts: 4
From: London
Registered: 1/20/11
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 23, 2011 11:15 AM   in response to: delgad9 in response to: delgad9
  Click to reply to this thread Reply

Hi,

thank you for your reply.

I have tried your setup and it works :)

I have missed web.xml setup and forgot to put persistenceUnitName in my entities.

Anyway is it possible to create relationship (@OneToMany , @ManyToMany) between two entities in two different persistence unit? Does Hibernate support it? I don't think it is possible. What do you think?

Cheers
Sanjoy

delgad9

Posts: 51
From: az
Registered: 8/18/10
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 24, 2011 7:41 PM   in response to: sanjoy in response to: sanjoy
  Click to reply to this thread Reply

Very-very interesting proposition...

You know it might work: It might require to tweak the transactions attributes. It is doable, I believe.

Let's try on our sides and report later.

Thank your for trying/validating/reporting configuration submitted.

jD

krimple

Posts: 141
From: Philadelphia, PA USA
Registered: 3/1/09
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 24, 2011 10:55 PM   in response to: delgad9 in response to: delgad9
  Click to reply to this thread Reply

I don't think you can query across persistence contexts. The whole point of the context is to cache entities, understand mappings, and write the SQL for you - if you query an entity and it has to pull a related entity, they should be related in the same context.

However, I'd love to see that work. I wonder if there is a particular JPA container that allows this.

You certainly can build transactions across multiple JPA contexts provided you're on a server that supports JTA, each driver is XA compliant, and you've mounted the Spring JTA transaction manager.

Interesting work guys, I'm following along. I've been revamping the JPA chapters over the last few weeks since they were the first major things I wrote back in July and Roo changed a bit.

Ken

Message was edited by:
krimple

delgad9

Posts: 51
From: az
Registered: 8/18/10
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 24, 2011 11:37 PM   in response to: krimple in response to: krimple
  Click to reply to this thread Reply

Ken,

You read my mind...
I was precisely thinking on such implementation: to build a test case as described using JTA.

You just pulled out my ace from the sleeve.


Congrats on chapter 6

jD

krimple

Posts: 141
From: Philadelphia, PA USA
Registered: 3/1/09
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 25, 2011 12:23 AM   in response to: delgad9 in response to: delgad9
  Click to reply to this thread Reply

Hey, thanks! More goodness to come...

sanjoy

Posts: 4
From: London
Registered: 1/20/11
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 25, 2011 4:08 AM   in response to: sanjoy in response to: sanjoy
  Click to reply to this thread Reply

Hi Guys,

Thank you very much for your replies. I think we should try this and see how it goes.
Thank you Ken for your feedback.

Cheers
Sanjoy

delgad9

Posts: 51
From: az
Registered: 8/18/10
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 25, 2011 5:59 AM   in response to: sanjoy in response to: sanjoy
  Click to reply to this thread Reply

Guys,

BTW, Forgot to mention. I am currently working on implementing a "Two databases configuration" add on for Roo.

Let you know when released.

Thank you
jD

sanjoy

Posts: 4
From: London
Registered: 1/20/11
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 25, 2011 3:30 PM   in response to: delgad9 in response to: delgad9
  Click to reply to this thread Reply

Hi,

I think it would be good if you could make it in more generic way. I mean let the user decides the number of databases (it could be 2 or more)

Cheers
Sanjoy

delgad9

Posts: 51
From: az
Registered: 8/18/10
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Jan 26, 2011 10:10 PM   in response to: sanjoy in response to: sanjoy
  Click to reply to this thread Reply

Guy,

Raincheck while I get the referred addon...


http://viralpatel.net/blogs/2011/01/spring-roo-two-database-configuration.html

Thx
jD

delgad9

Posts: 51
From: az
Registered: 8/18/10
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Sep 11, 2011 10:14 AM   in response to: delgad9 in response to: delgad9
  Click to reply to this thread Reply

Just updating old replies...
Hello Guys

I posted again in my blog that deals with the configuration for accessing 2 databases with Roo. The post is at http://pragmatikroo.blogspot.com/2011/09/springfamework-multiple-databases.html. *

For reasons out my control the first one published missed few details.

The configuration has been successfully used by multiple developers with their Roo projects.


I have plans to extended the configuration for programatically manage multiple datasources instead of using verbosed xml and to include service/repository approach.




B. Roogards
jD

* The link is ok. I correct the misspell later.

vishalakshi

Posts: 1
From: India
Registered: 2/27/13
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Feb 27, 2013 11:10 PM   in response to: sanjoy in response to: sanjoy
  Click to reply to this thread Reply

Anyway is it possible to create relationship (@OneToMany , @ManyToMany) between two entities in two different persistence unit? Does Hibernate support it? I don't think it is possible. What do you think?

jbbarquero

Posts: 83
From: Spain
Registered: 12/8/10
Re: Solution for connecting multiple databases using JPA + Hibernate
Posted: Feb 28, 2013 2:25 AM   in response to: vishalakshi in response to: vishalakshi
  Click to reply to this thread Reply

What for?

Please, take into account the definition of "Persistence Units": "A persistence unit defines a set of all entity classes that are managed by EntityManager instances in an application. This set of entity classes represents the data contained within a single data store."

http://docs.oracle.com/javaee/6/tutorial/doc/bnbqw.html#bnbrj

So, it's like you want establish a relationship between an entity in, let's say, a MySQL instance and another in a PostgreSQL database (and maybe it's the case).

If you need that, try with a Service (I mean a business POJO) that calls both entities in order to join each of them as desired. Take care with the transactions, maybe you'll need JTA, or you'll have different local transactions. Depending on the business logic, of course.

Legend
Gold: 300 + pts
Silver: 100 - 299 pts
Bronze: 25 - 99 pts
Manning Author
Manning Staff
Manning Developmental Editor