CODEWITHSUNDEEP
javahibernate
raajaag
raajaag

Reputation: 175

connect to different databases with single query using hibernate

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.

Upvotes: 1

Views: 2937

Answers (1)

Stephen C
Stephen C

Reputation: 719551

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.

Upvotes: 6

Related Questions