Introduction

JPA queries allow you to fetch either entities or DTO projections. However, sometimes you want a combined result set as illustrated in this Hibernate forum question.

Domain Model

Assuming you have the following entities:

The relationship between the two entities is not materialized in a @ManyToOne association. However, both entities share a locale attribute which we can use to form a join between the two.

Returning an entity in a DTO projection

As I explained before, DTO projections are suitable for read-only transactions and fetching data that is not meant to be modified.

However, there might be use cases when you want to select an entity inside your DTO projection. Therefore, considering we have the following DTO projection:

public class PersonAndCountryDTO{

    private final Person person;

    private final String country;

    public PersonAndCountryDTO(
        Person person, 
        String country) {
        this.person = person;
        this.country = country;
    }

    public Person getPerson() {
        return person;
    }

    public String getCountry() {
        return country;
    }
}

When you execute a JPQL query like this one:

List<PersonAndCountryDTO> personAndAddressDTOs = entityManager.createQuery(
    "select new " +
    "   com.vladmihalcea.book.hpjp.hibernate.query.dto.PersonAndCountryDTO(" +
    "       p, " +
    "       c.name" +
    "   ) " +
    "from Person p " +
    "join Country c on p.locale = c.locale " +
    "order by p.id", PersonAndCountryDTO.class)
.getResultList();

Hibernate generates the following SQL queries:

SELECT p.id AS col_0_0_,
       c.name AS col_1_0_
FROM   Person p
INNER JOIN 
       Country c 
ON 
       ( p.locale = c.locale )
ORDER BY 
       p.id

SELECT p.id AS id1_1_0_,
       p.locale AS locale2_1_0_,
       p.name AS name3_1_0_
FROM   Person p
WHERE  p.id = 3

SELECT p.id AS id1_1_0_,
       p.locale AS locale2_1_0_,
       p.name AS name3_1_0_
FROM   Person p
WHERE  p.id = 4

The Hibernate 5.2 implementation of the DTO projection cannot materialize the DTO projection from the ResultSet without executing a secondary query. However, this is very bad to performance since it can lead to N+1 query issues.

This HQL limitation has been discussed, and Hibernate 6.0 new SQM parser might address this issue, so stay tuned!

ResultTransformer

However, you are not limited to using JPA alone. Hibernate offers many enhancements that have no direct equivalent in the standard. One of these enhancements is the ResultTransformer mechanism which allows you to customize the ResultSet any way you like.

List<PersonAndCountryDTO> personAndAddressDTOs = entityManager
.createQuery(
    "select p, c.name " +
    "from Person p " +
    "join Country c on p.locale = c.locale " +
    "order by p.id")
.unwrap( org.hibernate.query.Query.class )
.setResultTransformer( 
    new ResultTransformer() {
        @Override
        public Object transformTuple(
            Object[] tuple, 
            String[] aliases) {
            return new PersonAndCountryDTO(
                (Person) tuple[0],
                (String) tuple[1]
            );
        }

        @Override
        public List transformList(List collection) {
            return collection;
        }
    } 
)
.getResultList();

There are two things to consider for this query:

1. The unwrap method is used to cast the JPA javax.persistence.Query to the Hibernate-specific org.hibernate.query.Query so that we gain access to the setResultTransformer method.
2. The ResultTransformer comes with a legacy definition which is not following the Functional Interface syntax. Hence, we cannot use a lambda in this example. Hibernate 6.0 aims to overcome this issue, so that’s why the Hibernate ORM 5.2 ResultTransformer is deprecated. Nevertheless, an alternative will be provided, so the concept we are discussing in this article is going to stand still even in Hibernate 6.

When running the aforementioned Hibernate ResultTransformer query, Hibernate generates the following output:

SELECT p.id AS col_0_0_,
       c.name AS col_1_0_,
       p.id AS id1_1_,
       p.locale AS locale2_1_,
       p.name AS name3_1_
FROM   Person p
INNER JOIN 
       Country c 
ON 
       ( p.locale = c.locale )
ORDER BY 
       p.id

Much better!

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

While the JPA NEW select clause is fine for trivial DTO projections, the ResultTransformer it allows you to customize the result set any way you like. In the particular use case we tested in this article, the ResultTransformer is also much more efficient as well, generating a single SQL query instead of N+1 ones.

Insert details about how the information is going to be processed

DOWNLOAD NOW