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?

Bury

Answer 1

We tried out the following approach and seems to work.

1) No schema attribute in the @Table annotations

2) create different orm files for entities clubbed by the schema in which they are present.

3) In each of the orm files, you can add a "my_schema".

4) Include the orm files in your respective PUs in the persistence.xml

5) And if you want different databases during tests, create similar orm files for test and change the value in the schema accordingly and include these orm files in a separate PU

HTH

Compressor

Answer 2

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")

Haydenhaydn

+1 Wow, I didn't know that. It would have made my life easier on my previous project. Thanks James! – Distraught

Answer 3

You can't. As each entity is bound to an persistance context and the context is bound to a database.

If by databases you mean schemas on the same server you can do 2 things

• create a view on one of the schemas, pointing to the table on the other schema. The downside, is that you might need to map an entity twice (once for each schema)
• Create a view with the join and map any values you need from there. The downside is that the entity will be read only.

If both schemas are on different databases, then you'll have to do the join manually in your code.

One quesion for you. The "foreign key" you mentioned, is a real DB foreign key or a logical FK ?

Distraught

It is a logical FK. And databases are real separate MySQL databases, though on the same physical machine. – Bury

You won't be able to do the join, for the reasons above. This might make sense if you're trying to read from DBs that are managed from different applications or services (if you're doing something SOA related). The solution is to do the join in your code. – Distraught

I took first approach and it worked. I also tested the performance of the join query on the view vs. the same on the tables of separate databases and they turned out to be almost same. – Bury