Join two un-related tables using JPA EntityManager

Question

I need to join two JPA entities on a property when there is no FK/PK relation between the two. I am using Hibernate and can use HQL query like this

 select foo, bar from FooEntity as foo, BarEntity as bar
 where  foo.someothercol = 'foo' and foo.somecol = bar.somecol

However, I want to avoid dependency on Hibernate and use EntityManager instead. Please help.

Answer 1

Your query is valid JPQL and does not use Hibernate specific functionality (just space between bar and from is missing). In JPA 2.0 specification (4.4.5 Joins) this is explained with following words:

An inner join may be implicitly specified by the use of a cartesian product in the FROM clause and a join condition in the WHERE clause. In the absence of a join condition, this reduces to the cartesian product.

The main use case for this generalized style of join is when a join condition does not involve a foreign key relationship that is mapped to an entity relationship. Example: SELECT c FROM Customer c, Employee e WHERE c.hatsize = e.shoesize

Main difference to your query is that your select contains two types of entities. Result of query is List of Object[]. Order of elements in array is same as in select statement. Following works in your case:

String query =
    "select foo, bar from  FooEntity as foo, BarEntity as bar "+
    "where  foo.someothercol = 'foo' and foo.somecol = bar.somecol";
List<Object[]> results = em.createQuery(query).getResultList();

for (Object[] fooAndBar: results) {
    FooEntity foo = (FooEntity) fooAndBar[0];
    BarEntity bar = (BarEntity) fooAndBar[1];
    //do something with foo and bar
}