How to fetch a one-to-many DTO projection with JPA and Hibernate

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!

Introduction

In this article, I’m going to show you how you can fetch a one-to-many relationship as a DTO projection when using JPA and Hibernate.

While entities make it very easy to fetch additional relationships, when it comes to DTO projections, you need to use a ResultTransformer to achieve this goal.

Table relationships

Let’s assume we have the following post and post_comment tables, which form a one-to-many relationship via the post_id Foreign Key column in the post_comment table.

The post and post_comment tables used for the JPA DTO projection

Fetching a one-to-many JPA entity projection

The aforementioned post table can be mapped to the following Post entity:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    @Column(name = "created_on")
    private LocalDateTime createdOn;

    @Column(name = "created_by")
    private String createdBy;

    @Column(name = "updated_on")
    private LocalDateTime updatedOn;

    @Column(name = "updated_by")
    private String updatedBy;

    @Version
    private Integer version;

    @OneToMany(mappedBy = "post", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<PostComment> comments = new ArrayList<>();
    
    //Getters and setters omitted for brevity
    
    public Post addComment(PostComment comment) {
        comments.add(comment);
        comment.setPost(this);
        return this;
    }
}

And the post_comment table is mapped to the following PostComment entity:

@Entity
@Table(name = "post_comment")
public class PostComment {

    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Post post;

    private String review;

    //Getters and setters omitted for brevity
}

The one-to-many table relationship is mapped as a bidirectional @OneToMany JPA association, and for this reason, we can easily fetch the association using the JOIN FETCH JPQL directive:

List<Post> posts = entityManager.createQuery("""
    select distinct p
    from Post p
    join fetch p.comments pc
    order by pc.id
    """)
.setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false)
.getResultList();

The HINT_PASS_DISTINCT_THROUGH is needed as we don’t want the DISTINCT JPQL keyword to be passed to the underlying SQL query. For more details about this JPA query hint, check out this article.

When running the JPQL query above, Hibernate generates the following SQL statement:

SELECT p.id AS id1_0_0_,
       pc.id AS id1_1_1_,
       p.created_by AS created_2_0_0_,
       p.created_on AS created_3_0_0_,
       p.title AS title4_0_0_,
       p.updated_by AS updated_5_0_0_,
       p.updated_on AS updated_6_0_0_,
       p.version AS version7_0_0_,
       pc.post_id AS post_id3_1_1_,
       pc.review AS review2_1_1_,
       pc.post_id AS post_id3_1_0__,
       pc.id AS id1_1_0__
FROM post p
INNER JOIN post_comment pc ON p.id=pc.post_id
ORDER BY pc.id

The entity projection selects all entity properties and, while this is very useful when we want to modify an entity, for read-only projections, this can be an overhead.

Fetching a one-to-many DTO projection with JPA and Hibernate

Considering we have a use case that only requires fetching the id and title columns from the post table, as well as the id and review columns from the post_comment tables, we could use the following JPQL query to fetch the required projection:

select p.id as p_id, 
       p.title as p_title,
       pc.id as pc_id, 
       pc.review as pc_review
from PostComment pc
join pc.post p
order by pc.id

When running the projection query above, we get the following results:

| p.id | p.title                           | pc.id | pc.review                             |
|------|-----------------------------------|-------|---------------------------------------|
| 1    | High-Performance Java Persistence | 1     | Best book on JPA and Hibernate!       |
| 1    | High-Performance Java Persistence | 2     | A must-read for every Java developer! |
| 2    | Hypersistence Optimizer           | 3     | It's like pair programming with Vlad! |

However, we don’t want to use a tabular-based ResultSet or the default List<Object[]>JPA or Hibernate query projection. We want to transform the aforementioned query result set to a List of PostDTO objects, each such object having a comments collection containing all the associated PostCommentDTO objects:

The PostDTO and PostCommentDTO used for DTO projection

As I explained in this article, we can use a Hibernate ResultTransformer, as illustrated by the following example:

List<PostDTO> postDTOs = entityManager.createQuery("""
    select p.id as p_id, 
           p.title as p_title,
           pc.id as pc_id, 
           pc.review as pc_review
    from PostComment pc
    join pc.post p
    order by pc.id
    """)
.unwrap(org.hibernate.query.Query.class)
.setResultTransformer(new PostDTOResultTransformer())
.getResultList();

assertEquals(2, postDTOs.size());
assertEquals(2, postDTOs.get(0).getComments().size());
assertEquals(1, postDTOs.get(1).getComments().size());

The PostDTOResultTransformer is going to define the mapping between the Object[] projection and the PostDTO object containing the PostCommentDTO child DTO objects:

public class PostDTOResultTransformer 
        implements ResultTransformer {

    private Map<Long, PostDTO> postDTOMap = new LinkedHashMap<>();

    @Override
    public Object transformTuple(
            Object[] tuple, 
            String[] aliases) {
            
        Map<String, Integer> aliasToIndexMap = aliasToIndexMap(aliases);
        
        Long postId = longValue(tuple[aliasToIndexMap.get(PostDTO.ID_ALIAS)]);

        PostDTO postDTO = postDTOMap.computeIfAbsent(
            postId, 
            id -> new PostDTO(tuple, aliasToIndexMap)
        );
        
        postDTO.getComments().add(
            new PostCommentDTO(tuple, aliasToIndexMap)
        );

        return postDTO;
    }

    @Override
    public List transformList(List collection) {
        return new ArrayList<>(postDTOMap.values());
    }
}

The aliasToIndexMap is just a small utility that allows us to build a Map structure that associates the column aliases and the index where the column value is located in the Object[] tuple array:

public  Map<String, Integer> aliasToIndexMap(
        String[] aliases) {
    
    Map<String, Integer> aliasToIndexMap = new LinkedHashMap<>();
    
    for (int i = 0; i < aliases.length; i++) {
        aliasToIndexMap.put(aliases[i], i);
    }
    
    return aliasToIndexMap;
}

The postDTOMap is where we are going to store all PostDTO entities that, in the end, will be returned by the query execution. The reason we are using the postDTOMap is that the parent rows are duplicated in the SQL query result set for each child record.

The computeIfAbsent method allows us to create a PostDTO object only if there is no existing PostDTO reference already stored in the postDTOMap.

The PostDTO class has a constructor that can set the id and title properties using the dedicated column aliases:

public class PostDTO {

    public static final String ID_ALIAS = "p_id";
    
    public static final String TITLE_ALIAS = "p_title";

    private Long id;

    private String title;

    private List<PostCommentDTO> comments = new ArrayList<>();

    public PostDTO(
            Object[] tuples, 
            Map<String, Integer> aliasToIndexMap) {
            
        this.id = longValue(tuples[aliasToIndexMap.get(ID_ALIAS)]);
        this.title = stringValue(tuples[aliasToIndexMap.get(TITLE_ALIAS)]);
    }

    //Getters and setters omitted for brevity
}

The PostCommentDTO is built in a similar fashion:

public class PostCommentDTO {

    public static final String ID_ALIAS = "pc_id";
    
    public static final String REVIEW_ALIAS = "pc_review";

    private Long id;

    private String review;

    public PostCommentDTO(
            Object[] tuples, 
            Map<String, Integer> aliasToIndexMap) {
        this.id = longValue(tuples[aliasToIndexMap.get(ID_ALIAS)]);
        this.review = stringValue(tuples[aliasToIndexMap.get(REVIEW_ALIAS)]);
    }

    //Getters and setters omitted for brevity
}

That’s it!

Using the PostDTOResultTransformer, the SQL result set can be transformed into a hierarchical DTO projection, which is much convenient to work with, especially if it needs to be marshalled as a JSON response:

postDTOs = {ArrayList}, size = 2
  0 = {PostDTO} 
    id = 1L
    title = "High-Performance Java Persistence"
    comments = {ArrayList}, size = 2
      0 = {PostCommentDTO} 
        id = 1L
        review = "Best book on JPA and Hibernate!"
      1 = {PostCommentDTO} 
        id = 2L
        review = "A must read for every Java developer!"
  1 = {PostDTO} 
    id = 2L
    title = "Hypersistence Optimizer"
    comments = {ArrayList}, size = 1
      0 = {PostCommentDTO} 
       id = 3L
       review = "It's like pair programming with Vlad!"

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

Conclusion

While entities make it very easy to fetch relationships, selecting all columns is not efficient if we only need a subset of entity properties.

On the other hand, DTO projections are more efficient from a SQL fetching perspective but require a little bit of work to associate parent and child DTOs. Luckily, the Hibernate ResultTransformer offers a very flexible solution to this problem, and we can fetch a one-to-many relation even as a DTO projection.

Transactions and Concurrency Control eBook

11 Comments on “How to fetch a one-to-many DTO projection with JPA and Hibernate

  1. Hi Vlad, setResultTransformer is deprecated as of 5.2 Do you have any alternatives to using this deprecated api in the context of the one to many dto projection? Thanks

  2. Hello Vlad, thanks for this article! Does this approach hold any advantage over processing the query result as tuples? For example I could fetch the tuples with Criteria API and then process them with a similar logic as you have in the ResultTransformer. Would that be performing worse than using the ResultTransformer itself?

    • The overhead of transforming Object[] to Tuple and then to DTO is extra memory, more work for the GC to reclaim objects (e.g., CPU), as well a processing the collection twice (e.g., CPU).

  3. Or you use a tool like Blaze-Persistence Entity-Views which supports flushing back the dirty state to entities or directly to the database through JPQL DML queries.

  4. It’s basically the same algorithm you’d use to transform a List to a Graph.

  5. Hi I met problem:
    everything is ok but when the posts has no comments i get empty post list.
    I’ ve tried to make left join in query but it gives me an error.
    How can i get my posts with empty sublists of posts instead of nothing ? Thanks in advance !

    • I don’t see why using LEFT JOIN would generate any error. You also have to make sure you use is null or on the LEFT JOIN predicates in case you reference them in the WHERE clause.

  6. Thank you very much ! I’ve adapted this solution to my project and it works perfect (also with other joins) 🙂 !

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.