Cross database joins in JPA

Question

Is it possible to do cross database table joins in JPA?

I have a users table in one database which has a foreign key to a organizations table in a separate database. Both the databases are on same physical machine. Now MySQL allows me to write queries which span across multiple databases, but I am not sure how to do this with JPA.

The @Entity annotations on the Java POJO's don't take the name of the database so there is no way to mark a cross DB relationship.

Is there a workaround for this situation? Perhaps using a native query to load the joined entity?

Answer 1

If MySQL allows you to write SQL that query across the database, then you can use this SQL in a native Query in JPA.

I assume you are using some kind of database linking mechanism? If so, then you should be able to map this as well. You can set the "schema" on your @Table of the linked database to the link name.

i.e.

@Table(name="organizations", schema="org_schema@org_db")