Why you should use the Hibernate ResultTransformer to customize result set mappings

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

Way much better!

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

If you liked this article, you might want to subscribe to my newsletter too.

Advertisements

7 thoughts on “Why you should use the Hibernate ResultTransformer to customize result set mappings

  1. Thanks, nice hint.
    You have a typo in Introduction section: … or DTO projects.
    Should be: … or DTO projections.

  2. Is very strange but for section with ResultTransformer I get the following error: “Caused by: javax.ejb.EJBTransactionRolledbackException: org.hibernate.internal.QueryImpl cannot be cast to org.hibernate.query.Query”;

    I’ve already verified if there is any mvn dependency conflict.

    1. That’s because you’re using an older version of Hibernate. In 5.2, org.hibernate.Query is deprecated in favor of org.hibernate.query.Query. Try with org.hibernate.Query for older versions.

  3. Thank you! Indeed, the container that I’m using has “Hibernate 5.0.10 Final” and I tried to use “Hibernate 5.2.10 Final” with provided scope. Because of that worked on compile using org.hibernate.query.Query but on runtime does not worked; With “Hibernate 5.0.10” works but there does not exists the method “org.hibernate.Query.getResultList()”; instead could be used “org.hibernate.Query.list()”;

  4. If the person has a list of countries, like “private List countries;”, how do you suggest to get the result?

    I know that an approach could be: In the @Overridden method “transformTuple(…)” for every row, doing a second query to get all countries. Do you know a better approach?

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