How to join unassociated entities with JPA and Hibernate

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

Representing associations between entities as attributes is one of the most comfortable and popular features of JPA and Hibernate. It makes it very easy to navigate from one entity to one or more associated entities in your Java code, like from a Person entity to the corresponding Address entity.

You can also use the association attributes in JPQL queries to join entities. 

However, the real challenge arises when you join two entities lacking an association attribute.

JPA and Hibernate versions prior to 5.1 don’t support this. In the first part of this post, I will show you how to use an implicit cross-join as a workaround. After that, I will show you Hibernate’s proprietary ad-hoc join feature, which is more powerful and easier to use.

Model and test data

I use a very simple model in this example, which you can see in the following diagram. It consists of two unassociated entities: the PersonEntity, and the PhoneBookEntryEntity. Both of them have the attributes firstName and lastName which we will use to join them.

And I use the following set of test data. As you can see, there are 2 PhoneBookEntries for “John Doe”, 1 for “Jane Doe” and none for “Peter Doe”.

JPA and Hibernate older than 5.1

JPA and Hibernate versions older than 5.1 require a defined association to join two entities in a JPQL query. That often leads to cascading JOIN statements to traverse the association graph between the entities or the statement that a JOIN of the entities is impossible with JPQL if there is no association between them.

But that’s not entirely true.

You can reference both entities in the FROM part to create a cartesian product and reduce it in the WHERE part of the query.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
     
List<Object[]> results = em.createQuery("SELECT p.firstName, p.lastName, n.phoneNumber FROM Person p, PhoneBookEntry n WHERE p.firstName = n.firstName AND p.lastName = n.lastName").getResultList();
 
for (Object[] result : results) {
    log.info(result[0] + " " + result[1] + " - " + result[2]);
}
 
em.getTransaction().commit();
em.close();

Hibernate transforms this into a cross-join, which creates the same result as an inner join when you add the join condition to the WHERE statement.

But be careful: depending on your database, this cross-join might perform significantly worse than an inner join.

05:35:05,400 DEBUG SQL:92 - select personenti0_.firstName as col_0_0_, personenti0_.lastName as col_1_0_, phonebooke1_.phoneNumber as col_2_0_ from Person personenti0_ cross join PhoneBookEntry phonebooke1_ where personenti0_.firstName=phonebooke1_.firstName and personenti0_.lastName=phonebooke1_.lastName
05:35:05,425  INFO TestAdHocJoin:43 - John Doe - 555-0101
05:35:05,426  INFO TestAdHocJoin:43 - John Doe - 555-0100
05:35:05,426  INFO TestAdHocJoin:43 - Jane Doe - 555-0123

This approach has two more disadvantages:

1. It is not as easy to read as an inner join especially if the WHERE statement gets complex and is often the cause of additional bugs.
2. You can’t create an outer join which for example returns all PersonEntities and if there is a matching record, their phone number.

Hibernate >= 5.1

Hibernate 5.1 introduced explicit joins on unassociated entities to fix these issues. The syntax and behavior are similar to SQL JOIN statements, as shown in the following code snippet. Instead of referencing the attribute that defines the association between the two entities, you have to reference the second entity you want to join and define the join criteria in the ON part of the statement.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
     
List<Object[]> results = em.createQuery("SELECT p.firstName, p.lastName, n.phoneNumber FROM Person p JOIN PhoneBookEntry n ON p.firstName = n.firstName AND p.lastName = n.lastName").getResultList();
 
for (Object[] result : results) {
    log.info(result[0] + " " + result[1] + " - " + result[2]);
}
 
em.getTransaction().commit();
em.close();

This creates the expected INNER JOIN in the SQL statement and is much easier to read than the cross-join in the previous example.

05:36:36,253 DEBUG SQL:92 - select personenti0_.firstName as col_0_0_, personenti0_.lastName as col_1_0_, phonebooke1_.phoneNumber as col_2_0_ from Person personenti0_ inner join PhoneBookEntry phonebooke1_ on (personenti0_.firstName=phonebooke1_.firstName and personenti0_.lastName=phonebooke1_.lastName)
05:36:36,267  INFO TestAdHocJoin:60 - John Doe - 555-0101
05:36:36,268  INFO TestAdHocJoin:60 - John Doe - 555-0100
05:36:36,268  INFO TestAdHocJoin:60 - Jane Doe - 555-0123

And you can use the same syntax to define an OUTER JOIN between the two entities, as I’ve done in the following code snippet.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
     
List<Object[]> results = em.createQuery("SELECT p.firstName, p.lastName, n.phoneNumber FROM Person p LEFT JOIN PhoneBookEntry n ON p.firstName = n.firstName AND p.lastName = n.lastName").getResultList();
 
for (Object[] result : results) {
    log.info(result[0] + " " + result[1] + " - " + result[2]);
}
 
em.getTransaction().commit();
em.close();

This query returns the first and last name of all PersonEntities and the phone number if there is a PhoneBookEntryEntity with the same first and last name.

05:37:11,338 DEBUG SQL:92 - select personenti0_.firstName as col_0_0_, personenti0_.lastName as col_1_0_, phonebooke1_.phoneNumber as col_2_0_ from Person personenti0_ left outer join PhoneBookEntry phonebooke1_ on (personenti0_.firstName=phonebooke1_.firstName and personenti0_.lastName=phonebooke1_.lastName)
05:37:11,348  INFO TestAdHocJoin:77 - John Doe - 555-0101
05:37:11,348  INFO TestAdHocJoin:77 - John Doe - 555-0100
05:37:11,349  INFO TestAdHocJoin:77 - Jane Doe - 555-0123
05:37:11,349  INFO TestAdHocJoin:77 - Peter Doe - null

Summary

The only way to join two unassociated entities with JPA and Hibernate versions older than 5.1 is to create a cross-join and reduce the cartesian product in the WHERE statement. This is harder to read and does not support outer joins.

Hibernate 5.1 introduced explicit joins on unrelated entities. The syntax is very similar to SQL and allows you to define the JOIN criteria in an ON statement.