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

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

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

Advertisements

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

  1. Upgraded Hibernate 4 to 5.2 last month.

    Switched over to JPA constructor mapping after Query.setResultTransformer() was found to be marked as deprecated.

    Here’s what I encountered when trying to find an alternative to setResultTransformer()
    – When declaring a @NamedNativeQuery, either resultClass or resultSetMapping must be present
    – @SqlResultSetMapping/@SqlResultSetMappings must be declared in a class annotated with @Entity or @MappedSuperclass so they can be picked up during application start up
    – createNamedQuery(query, resultClass): resultClass only accepts Object[], Tuple, managed entity, single java type
    – createNativeQuery(query, resultClass): resultClass only accepts managed entity (Tuple in 5.2.11)

    1. You shouldn’t have replaced Query.setResultTransformer() because it will not be removed. It will be enhanced as a @FuncationalInterface and the migration will be trivial.

  2. Timestamp.from(LocalDateTime.of(2016, 1, 1, 0, 0, 0).toInstant(ZoneOffset.UTC))

    Is there are reason this is uses over

    Timestamp.from(LocalDateTime.of(2016, 1, 1, 0, 0, 0))

    or

    Timestamp.valueOf(“2016-01-01 00:00:00”))

    The posted way seems verbose, error prone and wrong as java.sql.Timestamp is bound to the JVM time zone, not UTC https://stackoverflow.com/questions/21242110/convert-java-util-date-to-java-time-localdate/21242111#21242111

    In general I’m not a friend of java.sql java.time conversions. Pick one and stick with it rather than convert everywhere. Either your database access layer and driver supports java.time then use that or if not use java.sql.

    1. That’s a great question. I’m glad you asked it.

      Well, they are definitely not equal.

      Let’s take the one you suggested:

      Timestamp.valueOf(
      	LocalDateTime
      	.of( 
      		2016, 1, 1, 0, 0, 0 )
      	)
      .getTime()
      

      If you evaluate this one, you’ll get the epoch time of 1451599200000 which is:

      GMT: Thursday, December 31, 2015 10:00:00 PM

      Now, if we test the one that I suggested:

      Timestamp.from(
      	LocalDateTime
      	.of( 
      		2016, 1, 1, 0, 0, 0 
      	)
      	.toInstant( ZoneOffset.UTC ) )
      .getTime()
      

      And you evaluate it, you’ll get the epoch time of 1451606400000 which is:

      GMT: Friday, January 1, 2016 12:00:00 AM

      Meaning, that’s exactly what I wanted the Timestamp to represent.

      Although the Timestamp lacks time-zone info, it’s still a point in time.

      Therefore, it matters the relative time-zone of the LocalDateTime object when calculating the point in time.

      1. If you evaluate this one, you’ll get the epoch time of 1451599200000

        That depends on the JVM time zone you’re running in because you end up calling java.util.Date#Date(int, int, int, int, int, int) which will call TimeZone.getDefaultRef()

        I is important to note that the epoch time of java.sql.Timestamp is pointless because it does not identify a specific point in time or instant. The epoch time of a java.sql.Timestamp is always to be interpreted in the JVM time zone. If you write a java.sql.Timestamp to the database in one time zone and read it in a different JVM with a different time zone their epoch time values will be different.

        GMT: Friday, January 1, 2016 12:00:00 AM
        Meaning, that’s exactly what I wanted the Timestamp to represent.

        Then java.sql.Timestamp is the wrong abstraction, the right abstraction in that case is java.time.OffsetDateTime and TIMESTAMP WITH TIME ZONE
        LocalDateTime.of(2016, 1, 1, 0, 0, 0).atOffset(ZoneOffset.UTC))

      2. That depends on the JVM time zone you’re running in because you end up calling java.util.Date#Date(int, int, int, int, int, int) which will call TimeZone.getDefaultRef().

        It is important to note that the epoch time of java.sql.Timestamp is pointless because it does not identify a specific point in time or instant.

        Nope, you’re wrong. For java.util.Date and java.sql.Timestamp, TimeZone has no meaning other than being used when calling toString. It’s just the epoch millis that count.

        The epoch time of a java.sql.Timestamp is always to be interpreted in the JVM time zone.

        Not necessarily. It depends on what you want to do with it. If you are to display it in the UI, you can use either a relative TimeZone or the UTC if you want to normalize all date/times against a single Time zone.

        If you are doing period calculations, time zones are superfluous if all timestamps are stored in the same Time Zone. In fact, storing all timestamps in UTC is the only sane thing to do. Mixing time zones is as good as mixing multiple encodings when dealing with a single file.

        Then java.sql.Timestamp is the wrong abstraction, the right abstraction in that case is java.time.OffsetDateTime and TIMESTAMP WITH TIME ZONE

        Recently, we wanted to add support for TIMESTAMP WITH TIME ZONE when dealing with ZonedDateTime in Hibernate, and it looks like JDBC Drivers are miles away from supporting it.
        Therefore, my advice is as valid as it has been for the past 20 years.

  3. Hello Vlad,
    Thanks for your very good explanation.
    Does it also work by using the CriteriaBuider instead of JPQL?
    Kind regards
    Stephan

  4. Thanks for this simple but critical ankle in any realistic project.
    But i don’t feel this blog post is sufficent Vlad.
    I have a question : what about the performance impact (memory and/or cpu) ?
    It’s quite certain for me that one of this should be recommended on high volumes with heavy structures. And high volumes combined with JOINs are exactly the use case that leads you to this DTO approach.
    You don’t bother yourself for these to handle 10 pets and their food !
    So you illustrate all the opportunities (really appreciated : I ended up with new ideas on things I have overlooked) ,
    BUT you didn’t gave any hint on the impact, but you should, especially because you are one of those who CAN.
    You never neglected this aspect in your posts and your (wonderful must-read) book !

    1. It would be a performance impact on the DB side if you fetched entities instead of DTOs. On the application side, it’s more efficient to build DTOs from the JDBC ResultSet instead of generating an Object[] from the ResultSet and then build the entities.

      The number of joins affects the Execution Plan, while the select clause affects how much data you fetch.

      As for the impact, it’s very well detailed in my book. An article can never give you all the answers, but a book is much more suitable, don’t you agree?

    1. SQL is source code as well. You could store it in external files or embed it in your code either via prepared statements or build it dynamically using jOOQ or Criteria API. But, no matter how you end up with it, the SQL statement is just a String sent by the DB driver along with the prepared statement bind parameter values.

      As for a data access framework, it’s as good as the SQL it can generate. In the end, from a DB perspective, it’s all SQL.

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