How to JOIN unrelated entities with JPA and Hibernate

(Last Updated On: January 4, 2018)

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

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

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

  1. How can two unrelated entities , nothing in common be loaded in one Query using spring data jpa?

    Current Code :
    User user = userRepo.findOne(userId);
    Post post = postRepo.findOne(postId);

    This creates two sql query is there any way to do it 1 Query.

    Is there any way doing like this
    Object[] userAndPost = someRepo.findUserAndPost(userId, postId);

    Please note both user and post are unrelated and have no common column on which join can be done.

    Thanks,
    Sandeep

    1. You can find the answer to this question in my video course. You are also going to see why your initial assumption about one query is not really a good idea, this time, in terms of performance.

Leave a Reply

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.