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

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, I’m going to show you the best way to map a DTO projection when using JPA and Hibernate.

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:

Post entity for DTO projection

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 projection using JPA 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(
        LocalDate.of(2020, 1, 1)
            .atStartOfDay()
            .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.forum.dto.PostDTO(
       p.id,
       p.title
    )
    from Post p
    where p.createdOn > :fromTimestamp
    """, PostDTO.class)
.setParameter(
    "fromTimestamp",
    Timestamp.from(
        LocalDate.of(2020, 1, 1)
            .atStartOfDay()
            .toInstant(ZoneOffset.UTC)
    )
)
.getResultList();

You can omit the package name for the construction expression if you follow the steps provided in this article.

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(
        LocalDate.of(2020, 1, 1)
            .atStartOfDay()
            .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")
);

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(2020, 1, 1, 0, 0, 0)
            .toInstant(ZoneOffset.UTC)
    )
)
.getResultList();

For more details about the best way to use the JPA SqlResultSetMapping annotation, you should read this article.

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(2020, 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<PostDTO> 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(2020, 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.

Seize the deal! 25% discount. Seize the deal! 25% discount.

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.

Transactions and Concurrency Control eBook

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

  1. Hi great post!
    I have a question about: DTO projections using ResultTransformer.
    Is there a way to avoid the Number setter and keep my normal setter?
    Beacause i feel as i’m coupling a bit doing that.

    • If you don’t target multiple DBs, then use either Long or BigInteger.

      • I would like to put a Long on both (setter and attribute), but if i do that i have an exception on setter. Is there a way to change that? On old Hibernate versions (with old SQLQuery API) i did not have this issue, i could put any kind of type for my numbers inclusive primitives.

        I sorry for my english and thanks for your answer!

      • You should open a Hibernate issue if you think this is a regression.

      • I could solve it, like this.

        I created my ResultTransformer based on AliasToBeanResultTransformer, and then I changed:

         
        setters[i].set( result, tuple[i], null );
        

        to:

        BeanUtils.setProperty(result, aliases[i], tuple[i]);
        

        And, I use this CustomResultTransformer, like this:

        .setResultTransformer(new MyCustomResultTransformer)
        
  2. Hi Vlad,

    thanks for this read. Using “select new com.vladmihalcea.book.hpjp.hibernate.forum.dto.PostDTO” won’t benefit from Java’s typesystem. What if PostDTO is renamed or replaced to another package? I guess you could catch that by putting PostDTO.class in a variable and use getCanonicalName(), but if somebody replaces the 2 argument constructor the typesystem won’t complain.

    Wouldn’t it be safer to use the Tuple solution to get the results using getResultStream() and map that to the PostDTO constructor?

    • If you had Integration Tests, you wouldn’t worry about these issues. Not having Integration Tests is the real problem, not using SQL or JPQL queries.

      The Tuple is definitely not safer. A DTO provides type safety. Using Streams to create the DTO from a Tuple is just a waste of resources.

      • A developer refactoring will get noticed only after running those tests. The typesystem would have told him immediately and give automated refactoring tools the chance to fix it.

        If the the extra resources would be an issue, would you see the middle way as an improvement: check the constructor args in tests, but put PostDTO.class in a variable and use getCanonicalName() to check name and package?

      • This is the same with SQL queries. When you create a native SQL query, the query is provided in a String with bind parameter placeholders. If you change a column name, the query will fail at query time.

        Integration tests are mandatory when implementing a data access code. While type safety is very useful, for SQL queries, your only option would be to use jOOQ and render the query from its Java DSL.

        So, if you want to return Tuple, use Tuple. If you want to return a DTO, fetch the DTO directly. I see no point in generating extra Java objects to achieve the same goal.

        After all, isn’t this what the KISS Principle is all about?

      • In the example, there are 2 checks that cannot use typesafety. 1 is the data read from the database (column names) and 1 is the constructor of PostDTO. Using an intermediate Tuple won’t change anything to the first case, but it will move the second case from unchecked PostDTO creation to unchecked tuple reading (tuple.get(“id”)).

        However, a developer refactoring PostDTO might not see this query use, since it is on another place of the application. On the other hand, if some developer wants to refactor the Tuple, he sees immediately that some more action is need in the lines below (and why would he refactor, since this is the sole purpose of local defined tuple).

        So the point of these extra objects would be to shift the check from automatic test to type system.

      • However, a developer refactoring PostDTO might not see this query use, since it is on another place of the application.

        IntelliJ IDEA can spot these problems.

        IntelliJ IDEA

        Refactoring DTO constructors is a rare occurrence. Most of the time, you will employ use-case specific DTOs, not generic ones. So, the DTOs are associated with the service and data access layer. You can use the package by feature pattern to keep them together.

      • Thanks! I didn’t know this IntelliJ feature. And indeed it seems odd to refactor DTOs a lot.

      • IntelliJ IDEA is a great IDE. I’ve been using it for 15 years and it gets better and better.

        I strongly recommend you read that article from Olive. The idea of packaging services, DAOs, entities, and DTOs by their feature is a very good one. You can achieve better modularity that way and avoid reusing classes outside their scope.

  3. Vlad, I read your stuff all the time. Thanks for everything. Can you tell me how to project into a dto that only provides public static factory methods?

  4. Hi Vlad, great article.
    I have one question: you say, that if I need only reed data and pass it – I only need the dto, not the entity. E.m. I can replace all entity select query in my app with dto selects. But what about cache? The first level cache keeps entities and storing it to prevent extra queries to DB. But it doesn’t work for dto pojo.
    So why I should use dto instead of the entity? In this case, it looks like something bad for performance.
    Thanks for the answer!

  5. Hi Vlad and thanks for your nice article.

    Hibernate caches entities. What about DTOs. If i want to access to a DTO in several views, What should i do?

    I use “Business Objects” for transferring data between the Presentation Layer and the Business Layer and “Entity Objects” for transferring data between the Business Layer and the Data Access Layer in this case, the Hibernate(separation of concerns). I’m Thinking if i want a BO for reading, will use DTO projection but if i want to write a BO to a DB, will write a mapping system to map the BO to the corresponding Entity. Is that right? does the Hibernate have a mapping system for that?

  6. Remove the LongDetails.class parameter in the createNativeQuery method. That’s causing the issue.

    • Each column in the executing SQL query is distinct from an alias perspective.

  7. Hi Vlad,

    Is it possible to map an object inside a DTO object using HQL query directly? I am constructing a DTO by having another object passed as a parameter.

    • It depends on what do you mean by “object inside DTO”. If it’s an entity, then mixing DTOs and entities does not sound like a great idea. If it’s another DTO, then sure it’s possible. You can even build hierarchical DTOs. Check out my High-Performance Java Persistence book for more details.

  8. Hello, thank you very much for this article, is’t very clear how to use DTO projections with your examples.

    I have a doubt about generating a list of related entities in our DTO.
    In my case, I have an entity (Post.java) that has a @OneToMany relationship with another entity (Comment.java) and I’m trying to generate a DTO that contains some values of Post.java and a list containing DTOs from the related Comment.java.

    I have your book High-Performance Java Persistence, but couldn’t find an example of such case (if it’s even possible), perhaps I missed it.

    If you could help me I would really apreciate it. Thank you in advance!

    • Sure, it is possible. You can find an example in the Fetching chapter where I show the difference between fetching all data and using a Recursive CTE to limit the result set on the database side. All the code is on GitHub. Check out this example for instance.

  9. Great Article, but it made me think…
    It states that it is more efficient to select only the needed columns for DTO projections, but what if performance isn’t an issue?
    Isn’t safety (next to efficiency) an other reason to select only the needed columns in DTO projections (while you can modify the data but cannot accidentally persist it to the database)?

  10. Hi Vlad.
    I am trying to use native query to fetch a projection interface which has String, Long and a JsonNode as methods.

    However, when i try to use the above approach ,i.e., using a scalar for each of the types, and by using your dialect, I am facing “No converter found capable of converting from type ObjectNode” error.

    When I use the same interface at repository using @Query annotation, I get No dialect mapping for JDBC type: 1111.

    How can I resolve this issue? Does hibernate support createNativeQuery for interfaces? I would like to send List which will have a tree structure.

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.