Doing a join over 2 tables in different databases using Hibernate

Question

Hi everybody,

I have two tables in two separate oracle databases (not schemas) that I need to join in Hibernate. Currently, I have two hibernate sessions going out to the separate databases. Before anybody says look at Hibernate Shards, I have spent a better part of a day looking at that sub-project and have found that: it is for horizontal partitioned data (all the tables must be in all of the databases AFAIK), there is no way for one to tell shards to look only in one database (Hibernate Shards Docs), and is no longer being worked on.

Things that I have thought about to try to solve this problem:

• Doing a findAll() or some restricted variant of that on both of the tables, and manually doing the join using some loops. (Ok for very small tables - prohibitive from small tables on up)

• Have the sessions do some kind of interaction (I have no idea if this is even feasible - will have to look at the Hibernate Session API)

• Removing the database name from the url string of different hibernate-xxxx.cfg.xml and insert them into the separate hbm.xml files like this:
  <class name="foo" table="foo_table" schema="foo_schema" catalog="foo_db">
  (Doesn't seem to work from my initial tests and that seems like truck sized security hole)

• Use the Repository Pattern (Unsure if my Java-Fu is strong enough)

Is there something that I'm overlooking in one of the cases above or can it be another way that I haven't listed above?

Answer 1

You have a couple of problems unfortunately.

• Hibernate does not supporting joining across multiple "physical" database instances
• Out of the box, most database don't support joining across multiple "physical" database instances

Fundamentally DBs are only good/performant at joining tables that are in the same database. There are ways of joining across databases but if the size of both tables is big this can be a problem, and peformance may suffer. Do some googling on "oracle join across database" and you'll find some pointers on how to do this but it involves fiddling around with Oracle create a virtual link from one DB to the other.

I would consider either doing the join in memory, if you are comfortable the data set will fit within the memory constraints AND you are only doing this in one special case.

If you'll need to do different joins between these two databases then I would go for a more permanent solution, like the Oracle linking above.

Answer 2

I have no experience with doing this myself, but I know that Oracle supports database "links" between two separate database instances. Maybe this article will help you?

Post on Hibernate forums about using Oracle link between two instances