1

I am working on a legacy application. It uses java, hibernate. The problem is there are sql joins which are getting executed using Hibernate.

These SQls consists of two tables TableA, TableB. The problem is now TableA moved to database at US and TableB moved to database at UK. Means both are at different locations and different schemas. Now I have to migrate the application so that these joins can be executed.

How can I use this join to fetch the data from these two tables or how to configure hibernate to connect to different databases so that the SQL join can be executed.

raajaag
  • 175
  • 4
  • 18
  • As you know Hibernate does not support joining across multiple "physical" database instances. If you join DB, the size of both tables are big this can be a problem, and performance may suffer. For overcoming this, you have to migrate on Oracle (or you can join in memory). – Tehmina Aug 05 '17 at 02:44

1 Answers1

6

According to this Q&A:

... it cannot be done by Hibernate itself.

The other approach to consider would be to use XA to integrate the database. But that is heavy-weight and not likely to be performant. See this Q&A

... with sums it up like this:

The best way to distribute transactions over more than one database is: Don't.

In your case, this is saying is that you should pull the data from the two tables separately and then "merge" them programatically. Clunky.

Alternatively, have a long hard discussion with management about doing something about your organization's split-brain database problem. (For example, could the UK and US databases each hold read-only snapshots of the other sites business-critical tables?)

Please note that the above is substantially "opinion", but I don't think we can do much better than that. My understanding is that there are no "silver bullet" solutions to this difficult problem.

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
  • Thanks for the answer Stephen. That is where I am struggling, how they shifted the tables without considering of the downstream applications concerns. I cant change the current flow. Only thing I can do is dump all the data from the two tables to application specific database and execute this join but not sure whether the database memory supports as data is huge. The databases which I mentioned are common to all the applications. I cannot introduce spring to the existing frameworks. – raajaag Aug 05 '17 at 02:48
  • OK ... so it sound like you have already figured out the pragmatic solution before you asked the question. (Need more memory? Ask for more memory ... or use a local on-disk database. Either will be faster than the time spent pulling the data from overseas, or trying to wrangle an "elegant" solution using XA.) – Stephen C Aug 05 '17 at 02:52
  • Yes I thought of doing like that. But that is not feasible as requesting for increase of memory requires lot of approvals from across globe and takes time and they have not given that much time. That is why I am struggling to finish and posted this question. One thing I came to know for sure is I cannot use Hibernate for this kind of scenario. Thanks for the suggestions. – raajaag Aug 05 '17 at 02:58
  • Sounds to me like missing the deadline might be your only feasible option :-) – Stephen C Aug 05 '17 at 03:40