How to join unrelated 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!

---

The representation of relationships 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 related entities in your Java code, like from a Person entity to the corresponding Address entity.

You can also use the relationship attributes in JPQL queries to join related entities. The trouble starts as soon as you want to join 2 entities without a relationship attribute. JPA and Hibernate versions prior to 5.1 don’t support this kind of joins, and you have to use a workaround to create an implicit cross join. I will show you this in the first part of this post and explain Hibernates new ad-hoc join feature at the end of the post.

Model and test data

I use a very simple model in this example, which you can see in the following diagram. It just consists of two unrelated 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 relationship to join two entities in a JPQL query. That often leads to cascading JOIN statements to traverse the relationship graph between the entities or the statement that a JOIN of the entities is not possible with JPQL if there is no relationship between them.

It’s hard to believe that even with JPA 2.1 and Hibernate 5.0 it’s still not possible to join two unrelated entities. But unfortunately, that’s the truth. The only thing you can do is to 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.

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 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 unrelated entities to fix these issues. The syntax and behaviour are similar to SQL JOIN statements as you can see in the following code snippet. Instead of referencing the attribute which defines the relationship between the two entities, you have to reference the second entity which 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 unrelated entities with JPA 2.1 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.