Introduction
In the previous article, we have seen how to call a stored procedure and map the native query result to POJO with Hibernate
In this article, we’re going to learn how to join unrelated entities with Spring Data JPA using JPQL and Hibernate using HQL.
HQL and JPQL
The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, but not vice versa.
Both HQL and JPQL are non-type-safe ways to perform query operations.
Domain Model
Assuming we have the following entities

The Post
entity has an authorId
attribute which maps to the id
attribute of Author
entity.
The Post
entity also 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 persisted with a slug
attribute so that we know which web resource was viewed by the user.
Post
and PageView
has the slug
attribute in common. 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.
Projections
Interface-based Projection
Spring Data JPA provides an interface-based projection. We need to declare an interface that exposes accessor methods for the properties to be read from the database.
PostWithAuthor.java
package com.javachinna.model; public interface PostWithAuthor { public Post getPost(); public Author getAuthor(); }
Class-based Projections (DTOs)
PostInfo.java
Project Lombok provides an @Value
annotation which makes the fields private final
by default, and the class exposes a constructor that takes all fields and automatically gets equals(…)
and hashCode()
methods implemented.
package com.javachinna.model; import lombok.Value; @Value public class PostInfo { String title, authorName; Long viewCount; }
PostWithAuthorDTO.java
package com.javachinna.model; import lombok.Value; @Value public class PostWithAuthorDTO { private Post post; private Author author; }
Joining with JPQL
Join Unrelated Entities
Assume that we need to select both Post
and Author
entity with a single select query, then we can do so with the following JPA repository method.
@Query("select p as post, a as author from Post p inner join Author a on p.authorId = a.id where p.id = ?1")622 public PostWithAuthor getPostWithAuthor(Long id);
In the above JPQL query, we are using aliases post
and author
which should match with the accessor methods defined in the PostWithAuthor
interface. So that JPA will map the result to the interface automatically. We are joining the Author
entity using the inner join
with on
clause which was introduced in Hibernate 5.1.
Join Multiple Entities
Assume that we want to select data from multiple tables and map the result to a POJO, then we can use JPQL inner join
with on
clause to join the tables and JPA constructor expression to map the result to the POJO as follows
@Query("select new com.javachinna.model.PostInfo(p.title, a.firstName || ' ' || a.lastName, count(v)) from Post p inner join Author a on p.authorId = a.id inner join PageView v on p.slug = v.slug") public List<PostInfo> getPostInfoWithConstrutorExp();
The class reference must be fully qualified and it must have a matching constructor. Hence, In the above query, we are using the fully qualified name of com.javachinna.model.PostInfo
class followed by the new
operator and this class is having a matching constructor exposed by lombok’s @Value
annotation.
Join Unrelated Entities in JPA and Hibernate Older than 5.1
In JPA <=2.1 and Hibernate versions older than 5.1, entities should have an association to join them. Therefore, joining unrelated entities using the join
clause is not supported.
However, we can still join unrelated entities using a Cartesian join or Cross join and reduce the cartesian product by specifying the conditions in the where
clause. This is how we used to join the tables before the join
clauses were introduced in SQL.
A Cartesian join or Cross join is a join of every row of one table to every row of another table. For example, if table A has 100 rows and is joined with table B, which has 1,000 rows, a Cartesian join will result in 100,000 rows.
@Query("select new com.javachinna.model.PostInfo(p.title, a.firstName || ' ' || a.lastName, count(v)) from Post p, Author a, PageView v where p.authorId = a.id and p.slug = v.slug") public List<PostInfo> getPostInfoWithoutJoinClause();
For this JPQL query, Hibernate will generate the below cross join
SQL query:
select post0_.title as col_0_0_, concat(author1_.first_name, ' ', author1_.last_name) as col_1_0_, count(pageview2_.id) as col_2_0_ from post post0_ cross join author author1_ cross join page_view pageview2_ where post0_.author_id=author1_.id and post0_.slug=pageview2_.slug
Note: It does not support
outer join
. So, If you need outer join, then either you need to write a native query or upgrade your hibernate version.
Joining with HQL
In this section, we are going to implement the above Spring Data JPA examples using pure Hibernate.
Join Unrelated Entities
Here, we are using constructor expression for DTO projection since Hibernate doesn’t support interface-based projection.
public PostWithAuthorDTO getPostWithAuthor(Long id) { String query = "select new com.javachinna.model.PostWithAuthorDTO(p, a) from Post p inner join Author a on p.authorId = a.id where p.id = ?1"; Session session = entityManager.unwrap(Session.class); return session.createQuery(query, PostWithAuthorDTO.class).setParameter(1, id).getSingleResult(); }
Join Multiple Entities
public List<PostInfo> getPostInfoWithoutJoinClause() { String query = "select new com.javachinna.model.PostInfo(p.title, a.firstName || ' ' || a.lastName, count(v)) from Post p, Author a, PageView v where p.authorId = a.id and p.slug = v.slug"; Session session = entityManager.unwrap(Session.class); return session.createQuery(query, PostInfo.class).getResultList(); }
Join Unrelated Entities in Hibernate Older than 5.1
public List<PostInfo> getPostInfoWithoutJoinClause() { String query = "select new com.javachinna.model.PostInfo(p.title, a.firstName || ' ' || a.lastName, count(v)) from Post p, Author a, PageView v where p.authorId = a.id and p.slug = v.slug"; Session session = entityManager.unwrap(Session.class); return session.createQuery(query, PostInfo.class).getResultList(); }
Test with Junit 5
DemoApplicationTests.java
package com.javachinna.demo; import java.util.List; import org.junit.jupiter.api.Assertions; import org.junit.jupiter.api.Test; import org.junit.jupiter.api.extension.ExtendWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit.jupiter.SpringExtension; import com.javachinna.model.PostInfo; import com.javachinna.model.PostWithAuthor; import com.javachinna.model.PostWithAuthorDTO; import com.javachinna.repo.PostInfoRepository; import com.javachinna.repo.PostRepository; @ExtendWith(SpringExtension.class) @SpringBootTest public class DemoApplicationTests { @Autowired private PostRepository postRepository; @Autowired private PostInfoRepository postInfoDAO; @Test public void getPostWithAuthorJpaTest() { PostWithAuthor postWithAuthor = postRepository.getPostWithAuthor(1L); Assertions.assertEquals("Chinna", postWithAuthor.getAuthor().getFirstName()); } @Test public void getPostInfoWithConstrutorExpJpaTest() { List<PostInfo> list = postRepository.getPostInfoWithConstrutorExp(); Assertions.assertEquals(2, list.get(0).getViewCount()); } @Test public void getPostInfoWithoutJoinClauseJpaTest() { List<PostInfo> list = postRepository.getPostInfoWithoutJoinClause(); Assertions.assertEquals(2, list.get(0).getViewCount()); } @Test public void getPostWithAuthorTest() { PostWithAuthorDTO postWithAuthor = postInfoDAO.getPostWithAuthor(1L); Assertions.assertEquals("Chinna", postWithAuthor.getAuthor().getFirstName()); } @Test public void getPostInfoWithoutJoinClauseTest() { List<PostInfo> list = postInfoDAO.getPostInfoWithoutJoinClause(); Assertions.assertEquals(2, list.get(0).getViewCount()); } @Test public void getPostInfoWithConstrutorExpTest() { List<PostInfo> list = postInfoDAO.getPostInfoWithConstrutorExp(); Assertions.assertEquals(2, list.get(0).getViewCount()); } }
Test Result

References
https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/chapters/query/hql/HQL.html
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections
https://thorben-janssen.com/how-to-join-unrelated-entities
Source Code
https://github.com/JavaChinna/spring-jpa-hibernate-join-unrelated-entities
Conclusion
That’s all folks. In this article, we have explored various approches for joining the results of multiple entities using Spring Data JPA and Hibernate.
Please share it in your circle if you like this article. Thanks for reading.