How to join unrelated entities with JPA and Hibernate

By Thorben Janssen

Jpql, Query

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.

classdiagram

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”.

Data

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.

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.

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.

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

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.

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.


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.


Tags

Jpql, Query


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  1. Great blog,

    I have tricky question, please help 🙂

    I can join unrelated entities with following jpql
    SELECT m.number, d from MyUnrelatedEntity m, MyAggregateRoot d where m.someId = d.someId

    but I can’t figure out how to add eager joins to childEntities to the MyAggregateRoot with above

    Any hints about the syntax?

    Reply

    1. Hi Tapio,

      You can use a JOIN FETCH clause to specify eager loading of childEntities.

      So, this query should work:
      SELECT m.number, d from MyUnrelatedEntity m, MyAggregateRoot d LEFT JOIN FETCH d.childEntities c where m.someId = d.someId

      Regards,
      Thorben

      Reply

  2. Another very informative article in your series of articles!

    Is there a chance to join unrelated entities using CriteriaAPI also?!

    Thanks!

    Reply

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}