CODEWITHSUNDEEP
eclipselinkjava-ee-6managed-beandatabase-partitioning
user2227105
user2227105

Reputation: 11

JPA multiple databases - simple example / best pattern?

I have 2 mySql databases that I'm trying use in a simple Java EE Web Application. To simplify the scenario, the schemas are identical, and for example lets take the Customer table in each.

I'm trying to decide on the best way to return "union" results to my interface for both customer tables across the 2 databases. So far I've come across eclipselink partitioning as a possible avenue.

As an alternative, is there any way to "merge" the "contents" of 2 entity classes / EJB / Managed Beans ?

Upvotes: 1

Views: 433

Answers (2)

Alexander Rühl
Alexander Rühl

Reputation: 6939

You have to decide on which side you want to unite the 2 databases - on the database or the application side.

For the 1st, you can follow the proposal of @Mark Robinson to provide an united view for the application. In this case you can use any database mechanisms available, for the application it's just a simple new entity.

For the 2nd, you can use 2 different PersistenceContext and obtain entities from either one in your application.

Upvotes: 2

Mark Robinson
Mark Robinson

Reputation: 3155

If the schemas are genuinely identical then you might be able to get away with using partitioning but there are going to be some problems with that.

Partitions are usually sliced on technical bounds(ranges, checksums, etc). What I suspect is that these are partitioned for other reasons (ie, HR in one DB, Engineering in another). This will cause problems with the partitioning since it won't know which DB to look in, or which DB to write to.

In your case, I would recommend creating a 'union' schema where you import data from both databases into a single unified database. It's not perfect, but it will give you a proper query interface. You might want to look into if MySQL has remote interface functionality similar to Oracle.

Upvotes: 1

Related Questions