The best way to map a projection query to a DTO (Data Transfer Object) with JPA and Hibernate

(Last Updated On: October 24, 2018)

Introduction

While answering questions on the Hibernate forum, I stumbled on the following question. What was different than previous questions on the same topic was that the original poster realized the shortcoming of the JPA constructor expression which can be addressed elegantly by Hibernate.

Because this has been a recurring theme on StackOverflow and the Hibernate forum, I decided to dedicate an article to the best way of mapping DTO projections using JPA and Hibernate.

Domain Model

Considering we have the following Post entity:

As previously explained, fetching entities only makes sense if you plan to modify them. If you are only interested in a DTO projection, it’s more efficient to select only the columns that are really needed by the caller.

Assuming we want to select just the id and the title from our Post entities, it would be a waste of resources to select an entire Post entity, so let’s see how you could achieve this goal using JPA and Hibernate.

DTO projections using JPA

When using JPA or Hibernate, you can execute both entity queries via JPQL or Criteria API or native SQL queries.

DTO projections using Tuple and JPQL

If you don’t want to supply a DTO class for your projection, you can use the JPA Tuple.

So, to use Tuple projection, your JPQL query looks as follows:

List<Tuple> postDTOs = entityManager
.createQuery(
    "select " +
    "       p.id as id, " +
    "       p.title as title " +
    "from Post p " +
    "where p.createdOn > :fromTimestamp", Tuple.class)
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
        .toInstant( ZoneOffset.UTC ) ))
.getResultList();

assertFalse( postDTOs.isEmpty() );

Tuple postDTO = postDTOs.get( 0 );
assertEquals( 
    1L, 
    postDTO.get( "id" ) 
);

assertEquals( 
    "High-Performance Java Persistence", 
    postDTO.get( "title" ) 
);

As you can see, the Tuple is a convenient way of fetching DTO projections as you don’t require to specify a DTO class for every type of projection that needs to be supported.

DTO projections using a Constructor Expression and JPQL

If you don’t want to use a Tuple because you want the DTO projection to use a specific class, you can use a Constructor Expression by specifying the NEW keyword along with the fully-qualified name of the class representing the DTO projection and the list of attributes that will be passed as constructor arguments.

The DTO class must provide a constructor that takes all the attributes fetched by the result set projection.

So, the DTO projection must look as follows:

public class PostDTO {

    private Long id;

    private String title;

    public PostDTO(Number id, String title) {
        this.id = id.longValue();
        this.title = title;
    }

    public Long getId() {
        return id;
    }

    public String getTitle() {
        return title;
    }
}

Therefore, the constructor expression JPQL query looks as follows:

List<PostDTO> postDTOs = entityManager
.createQuery(
    "select new " +
    "   com.vladmihalcea.book.hpjp.hibernate.query.dto.projection.jpa.PostDTO(" +
    "       p.id, " +
    "       p.title " +
    "   ) " +
    "from Post p " +
    "where p.createdOn > :fromTimestamp", PostDTO.class)
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
        .toInstant( ZoneOffset.UTC ) ))
.getResultList();

DTO projections using Tuple and native SQL queries

Starting from Hibernate ORM 5.2.11, because the HHH-11897 Jira issue got fixed, you can use Tuple for native SQL queries.

List<Tuple> postDTOs = entityManager
.createNativeQuery(
    "SELECT " +
    "       p.id AS id, " +
    "       p.title AS title " +
    "FROM Post p " +
    "WHERE p.created_on > :fromTimestamp", Tuple.class)
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
        .toInstant( ZoneOffset.UTC ) ))
.getResultList();

assertFalse( postDTOs.isEmpty() );

Tuple postDTO = postDTOs.get( 0 );
assertEquals( 
    1L, 
    ((Number) postDTO.get( "id" )).longValue() 
);

assertEquals( 
    "High-Performance Java Persistence", 
    postDTO.get( "title" ) 
);

DTO projections using a ConstructorResult

For native SQL queries, you can no longer use a Constructor Expression, so you need to use a named native query and configure a given SqlResultSetMapping so that you can populate the DTO class either via its constructor or its fields.

If we use the same PostDTO class type introduced previously, we have to provide the following SqlResultSetMapping:

@NamedNativeQuery(
    name = "PostDTO",
    query =
        "SELECT " +
        "       p.id AS id, " +
        "       p.title AS title " +
        "FROM Post p " +
        "WHERE p.created_on > :fromTimestamp",
    resultSetMapping = "PostDTO"
)
@SqlResultSetMapping(
    name = "PostDTO",
    classes = @ConstructorResult(
        targetClass = PostDTO.class,
        columns = {
            @ColumnResult(name = "id"),
            @ColumnResult(name = "title")
        }
    )
)

Now, the SQL projection named native query is executed as follows:

List<PostDTO> postDTOs = entityManager
.createNamedQuery("PostDTO")
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
        .toInstant( ZoneOffset.UTC ) ))
.getResultList();

DTO projections using Hibernate

While you can use all the JPA features with Hibernate, there are many more features Hibernate has to offer than the standard Java Persistence specification.

DTO projections using ResultTransformer and JPQL

As previously explained, the ResultTransformer allows you to customize the result set any way you like so you can use it to transform the typical Object[] array projection into a DTO result set.

This time, you don’t need to provide a constructor to match the entity attributes being selected by the query.

Although you don’t even have to provide setters in your DTO class, here, we need the setter because BigInteger might be returned for the id database column while we need it to be cast as a Long.

Hibernate can set the appropriate fields using Reflection, so it’s more flexible than the previous JPA Constructor Expression alternative.

Considering we have the following DTO class:

public class PostDTO {

    private Long id;

    private String title;

    public Long getId() {
        return id;
    }

    public void setId(Number id) {
        this.id = id.longValue();
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }
}

We can transform the result set using the setResultTransformer method of the Hibernate-specific org.hibernate.query.Query interface which you can unwrap from the JPA Query.

List<PostDTO> postDTOs = entityManager
.createQuery(
    "select " +
    "       p.id as id, " +
    "       p.title as title " +
    "from Post p " +
    "where p.createdOn > :fromTimestamp")
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 ).toInstant( ZoneOffset.UTC ) ))
.unwrap( org.hibernate.query.Query.class )
.setResultTransformer( Transformers.aliasToBean( PostDTO.class ) )
.getResultList();

DTO projections using ResultTransformer and a Native SQL query

If you want to use a native SQL query, you don’t need to go through all the trouble of declaring a SqlResultSetMapping since you can use the AliasToBeanResultTransformer just like it was the case for the aforementioned JPQL example.

List postDTOs = entityManager
.createNativeQuery(
    "select " +
    "       p.id as \"id\", " +
    "       p.title as \"title\" " +
    "from Post p " +
    "where p.created_on > :fromTimestamp")
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 ).toInstant( ZoneOffset.UTC ) ))
.unwrap( org.hibernate.query.NativeQuery.class )
.setResultTransformer( Transformers.aliasToBean( PostDTO.class ) )
.getResultList();

Cool, right?

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

Conclusion

As you can see, there are multiple options to generate a DTO projection with JPA and Hibernate. Although the JPA specification offers both the Constructor Expression and the Tuple result, the ResultTransformer can be a much more flexible alternative.

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

32 thoughts on “The best way to map a projection query to a DTO (Data Transfer Object) with JPA and Hibernate

  1. How do all the options that you have described here compare to having a projection to DTO in the repository layer? Example:

    @Repository
    public interface PostRepository extends CrudRepository<Post, Long> {

    @Query(“select new ” +
    ” com.vladmihalcea.book.hpjp.hibernate.query.dto.projection.jpa.PostDTO(” +
    ” p.id, ” +
    ” p.title ” +
    ” ) ” +
    “from Post p ” +
    “where p.createdOn > :fromTimestamp”)
    List getPosts(Timestamp fromTimestamp);

    }

    Will that give you all the same performance benefits?

  2. Hi Vladmihalcea,

    First of all, congrats for the great article.
    I was looking for the “DTO projections using a ConstructorResult” solution since the “NEW” keywords in a JPA query didn’t work as I thought so.

    I managed to use your solution but I encounter this situation. In case I use “SqlResultSetMapping” annotation in my DTO class, the namedQuery can’t been found. Therefore I placed it in an existing entity, which is irrelevant with the DTO itself and worked. Am I forced to use the mapping annotation in an Entity class ? The whole purpose if this solution was to use a DTO and not be forced to map it to a table.

    Thanks in advance,

  3. Hi Vlad. Fan of your posts like always. I have a doubt, i want to get in your example a PostDto with comments(my PostDto class had a list of CommentDto class). How you perform that ? Its even this possible?

  4. Hi,
    Thanks for sharing this.
    I have replicated the same code snippet as example 1(DTO projections using Tuple and JPQL). But I am getting the below error:
    Caused by: org.hibernate.MappingException: Unknown entity: javax.persistence.Tuple.
    I have also added this config:
    @EntityScan(basePackages= {“com.mypackage.example”, “javax.persistence”})
    but still the same error.
    Could you please help me?

  5. Hi Vlad,

    Is it possible to have nested DTOs?

    Let’s say each post also has an owner. How can I use something like this?

    “select new ” +
    ” com.vladmihalcea.book.hpjp.hibernate.query.dto.projection.jpa.PostDTO(” +
    ” p.id, ” +
    ” p.title, ” +
    ” new com.vladmihalcea.book.hpjp.hibernate.query.dto.projection.jpa.UserDTO(
    ” p.owner.id,
    ” p.owner.name
    ” )
    ” ) ” +
    “from Post p ” +
    “where p.createdOn > :fromTimestamp”

    Right now, this results in an error: “org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ,”.

      1. Thanks for the quick reply, Vlad. I have worked around this limitation by passing all the required data to the DTO constructor and creating the nested DTOs there. Anyway, good to know about this annoying and undocumented JPQL limitation.

  6. Hi! Will this work even for associations? For example, what if Post had a @OneToMany relationship and I want to have only this collection in my DTO?
    Thanks for this post, didn’t know projection was natively possible.

      1. Check out the Associations chapter in my High-Performance Java Persistence book for a detailed explanation.

    1. A DTO is not a UI thing. It’s how you choose to send a response back from the business logic. So, it’s not a bug, it’s a feature.

  7. Hi Vlad,

    i try to implement your example using the REsultSetTransformer for a native sql query:

    public List findAll(final String sqlQuery, final Map<String, Object> parameters, Class resultType) {
    final Query query = em.createNativeQuery(sqlQuery);
    parameters.forEach(query::setParameter);
    return (List)query.unwrap(org.hibernate.query.NativeQuery.class).setResultTransformer(Transformers.aliasToBean(resultType)).getResultList();
    }

    The Result class is a simple pojo with getter and setter of a single field which i use as projection in the query.

    I always get an exception saying:
    DeviceSearchResult cannot be cast to java.util.Map

    How does the mapping works? the projection field has exactly the same name as the pojo field

      1. Thanks, i already check your example.
        I did exactly the same way, but i always get a ClassCastException.
        I debugged the AliasToResultTransformer at all aliases for the projected fields are caps. E.g. “DEVICEID” instead of “deviceId”.
        As a result the reflection call to find the setter fails.

        Any idea what i am doing wrong?

        Thanks in advance.

        P.S. I love your book 😉

  8. Hi Vlad, thanks for the great article!

    I’m using native queries to populate DTOs and I have a customized aliasToBean transformer (I wanted to be able to annotate the DTO fields with @Column in order to have better names for my DTO fields).

    This has been working beautifully, but now I am stuck on a case where a column has a special type (PG inet). I created the custom UserType for it and I would happily annotate my field with this type and process it in the transformer, however hibernate breaks before I even reach the Transformer (No Dialect mapping for JBDC type: 1111).

    I have tried the following:
    – using addScalar on the SQLQuery to define the custom type for the column, however now I am forced to add all of the fields… and there are about 50 of them.
    – creating an @SqlResultSetMapping. This works, but it is not nice at all, because:
    1. 50 fields need to be mapped;
    2. I need to put this mapping on an Entity class, not on the actual DTO class and it doesn’t logically belong to a specific Entity.

    I am thinking of trying the following:
    A. add the custom type to the org.hibernate.dialect.Dialect using the contributeTypes method. This is not straightforward and I am not sure it will work..
    B. add some annotation processing for my DTOs and use SQLQuery.addScalar for all the fields.

    Extra thoughts:
    Wouldn’t it be awesome if there was a built-in way to create a result set mapping by annotating the DTO? It would be so much more flexible than the current way to do it.

    I know this is way long, but I would appreciate any thoughts you have!

    Cheers,
    Emilia

    1. While for JPQL queries, Hibernate can parse the query and know the actual Type associated to a given DB column, for native queries the Type cannot be inferred.

      For basic types, this is not a problem since there is a mapping between JDBC type and Hibernate Types. The problem is more related to JDBC than Hibernate since the Types.OTHER (e.g. 1111) can be anything: UUID, Inet, JSON, etc.

      So, if you map Types.OTHER to Inet in the Dialect, Hibernate will always use your custom InetType even if you need UUID or JSON.

      Therefore, it’s probably better if you give the Type info on a per query basis, using addScalar or SqlResultSetMapping. It’s unfortunate that JPA does not define a programmatic way of building a SqlResultSetMapping and it can only be done via annotations.

  9. How can I overcome the issue of NativeQuery.setResultTransfomer @Deprecated issue for hibernate 5.12.17?

    Thank you in advance

    1. Just use it, there’s no issue. The class will only be replaced by something else in 6.x.

      You could turn the warning of like this if it bothers you:

      @SuppressWarnings( "deprecation" )
      
  10. 1、@SqlResultSetMapping How is this paged?
    2、when i use 《DTO projections using ResultTransformer and a Native SQL query》,Console error occurred ,“ java.lang.IllegalArgumentException: java.lang.ClassCastException@495edf96”

  11. Thank’s for share this knowledge.

    I’m currently implementing a stored procedure call. I coded a DTO object and defined the result set mapping in a orm.xml file. I have two questions that arise as a result of some tests execution with errors, ¿I have to define de DTO object as an @Entity? and because of that, ¿I have to create a table in the database that match with the DTO object structure?

    I’d appreciate your support Vlad, thank you!!

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.