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 wasn’t any page view for this particular web resource.

Joining unrelated entities is only possible when using JPQL or HQL. This feature is not available when using the JPA Criteria API.

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

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses 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.

Insert details about how the information is going to be processed

DOWNLOAD NOW