How to JOIN unrelated entities with JPA and Hibernate

Introduction

Answering questions on the Hibernate forum is a neverending source of inspiration. The following question is about joining unrelated entities using entity queries, and this post is going to explain how you can do it when using JPA and Hibernate.

Domain Model

Assuming we have the following entities:

The Post entity has a slug attribute which defines the relative address of this particular HTTP resource on our application server. Every time a user visits a given web page, a PageView event is logged which, among other properties, it also has a slug attribute so that we know which web resource was viewed by our user.

Just because they share a common property, it does not mean that we need to define an association between these entities. However, we would still want to join them on a per query basis.

Test data

Now, let’s say we have the following entries in our database:

Post post = new Post();
post.setSlug("/books/high-performance-java-persistence");
post.setTitle("High-Performance Java Persistence");

entityManager.persist(post);

Post post = new Post();
post.setSlug("/presentations");
post.setTitle("Presentations");

entityManager.persist(post);

PageView pageView = new PageView();
pageView.setSlug("/books/high-performance-java-persistence");
pageView.setIpAddress("127.0.0.1");

entityManager.persist(pageView);

PageView pageView = new PageView();
pageView.setSlug("/books/high-performance-java-persistence");
pageView.setIpAddress("192.168.0.1");

entityManager.persist(pageView);

We want to join the Post and the PageView entities so that we know how many hits a given Post has generated.

Although JPA 2.0 has introduced support for JOIN ON clause in JPQL queries, this syntax requires the association to be present at the entity level.

However, in our case, our entities are unrelated so there is no such association present. Therefore, the JPA standard does not offer a solution for unrelated entities so we have to address this issue using Hibernate-specific features.

Hibernate 5.1 and newer

Since Hibernate 5.1, you can easily join unrelated entities using the same syntax you’d use when writing a native SQL query:

Tuple postViewCount = entityManager.createQuery(
	"select p as post, count(pv) as page_views " +
	"from Post p " +
	"left join PageView pv on p.slug = pv.slug " +
	"where p.title = :title " +
	"group by p", Tuple.class)
.setParameter("title", "High-Performance Java Persistence")
.getSingleResult();

Post post = (Post) postViewCount.get("post");
assertEquals(
    "/books/high-performance-java-persistence", 
    post.getSlug()
);

int pageViews = (
    (Number) postViewCount.get("page_views")
).intValue();
assertEquals(2, pageViews);

As expected, there were 2 hits for the Post with the slug value of /books/high-performance-java-persistence.

If we run the same query for the second Post:

Tuple postViewCount = entityManager.createQuery(
    "select p as post, count(pv) as page_views " +
    "from Post p " +
    "left join PageView pv on p.slug = pv.slug " +
    "where p.title = :title " +
    "group by p", Tuple.class)
.setParameter("title", "Presentations")
.getSingleResult();

Post post = (Post) postViewCount.get("post");
assertEquals("/presentations", post.getSlug());

int pageViews = (
    (Number) postViewCount.get("page_views")
).intValue();
assertEquals(0, pageViews);

We get 0 hits since, currently, there is no PageView associated with this Post entity.

That’s exactly why we used LEFT JOIN instead of just JOIN which is equivalent to an INNER JOIN. If we used INNER JOIN for this query, then no row would be returned. However, we want the Post to be always returned and have the page_views return 0 if there was not hit for this particular web resource.

Prior to Hibernate 5.1

If you are using an older version of Hibernate, the only way to join two unrelated entities is to use a theta-style join.

For the first Post entity:

Tuple postViewCount = entityManager.createQuery(
    "select p as post, count(pv) as page_views " +
    "from Post p, PageView pv " +
    "where p.title = :title and " +
    "      ( pv is null or p.slug = pv.slug ) " +
    "group by p", Tuple.class)
.setParameter("title", "High-Performance Java Persistence")
.getSingleResult();

Post post = (Post) postViewCount.get("post");
assertEquals(
    "/books/high-performance-java-persistence", 
    post.getSlug()
);

int pageViews = (
    (Number) postViewCount.get("page_views")
).intValue();
assertEquals(2, pageViews);

This query gives the same result as the one executed before because there are PageView rows associated with this particular Post entity.

However, if we do the same for the second Post entity:

List<Tuple> postViewCount = entityManager.createQuery(
    "select p as post, count(pv) as page_views " +
    "from Post p, PageView pv " +
    "where p.title = :title and " +
    "      ( p.slug = pv.slug ) " +
    "group by p", Tuple.class)
.setParameter("title", "Presentations")
.getResultList();

assertEquals(0, postViewCount.size());

We get no result back because the Theta-style join is equivalent to an equijoin or an INNER JOIN, and not to a LEFT OUTER JOIN.

Therefore, prior to Hibernate 5.1, you could only join unrelated entities using the equijoin relational algebra operator.

If you enjoyed this article, I bet you are going to love my book as well.

Conclusion

If you need to join two unrelated entities, then you should upgrade to at least Hibernate 5.1. Otherwise, if you don’t want to use an equijoin, you can no longer use JPQL or HQL. For older versions of Hibernate, if you need an OUTER JOIN for your unrelated entities, then you can only do it using native SQL queries.

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements

4 thoughts on “How to JOIN unrelated entities with JPA and Hibernate

  1. Might be good to mention that JPA only defines support for the theta join solution. Note that JPA does support the JOIN ... ON ... syntax, but the thing joined has to be an association. The ability to use another entity name as the target of the join is an extension.

    1. That being said, I might also conclude, that a left join is not possible using the Criteria API offered by JPA? I would love to use some hibernate specific features implemented within the HQ language, like the left join, in criteria queries as well. Do you plan in the feature to support this somehow? Or is there something available, I don’t know?

      1. LEFT JOINs are possible with Criteria API, just not for unrelated entities. There will be Hibernate specific features to be available in Criteria API starting from Hibernate 6.0, once we get the new HQM parser integrated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s