Introduction

In this article, I’m going to show you what is the best way to fetch a Spring Data JPA DTO Projection.

Apart from the basic use cases, we’re going to see how to fetch even hierarchical DTO structures using Spring Data JPA.

Why use DTO projections

As I explained in this article, DTO projections allow you to select only the columns that you need for a given use case.

Throughout my consulting and training engagements, I get to see that, many times, developers fetch more than necessary, and the best way to fix this issue is to use pagination and projections.

While pagination allows you to reduce the number of records in the result set, projections allow you to reduce the number of columns you are fetching, and this can help you reduce query response time.

What is a DTO projection

An SQL projection is basically a query that provides a list of columns in the SELECT clause.

A DTO projection is a Java Object that contains the column values that were fetched by a given SQL projection query.

The DTO projection can be a POJO (Plain Old Java Object), a JPA Tuple, or a Java Record, and we can fetch all those DTO projection types using Spring Data JPA.

YouTube Video

I also published a YouTube video about DTO projections, so enjoy watching it if you’re interested in this topic.

Fetching a Tuple projection with Spring Data JPA

The first option you have is to map the SQL projection recording a JPA Tuple container like this:

@Query("""
    select 
        p.id as id, 
        p.title as title, 
        c.review as review
    from PostComment c
    join c.post p
    where p.title like :postTitle
    order by c.id
    """)
List<Tuple> findCommentTupleByTitle(
    @Param("postTitle") String postTitle
);

And we can call the findCommentTupleByTitle method like this:

List<Tuple> commentTuples = postRepository
    .findCommentTupleByTitle(titleToken);

[code lang="java" gutter="false"]
Tuple commentTuple = commentTuples.get(0);

assertEquals(
    Long.valueOf(1), 
    ((Number) commentTuple.get("id")).longValue()
);

assertTrue(
    ((String) commentTuple.get("title"))
        .contains("Chapter nr. 1")
);

However, while the Tuple allows us to retrieve the column values by their column alias, we still need to do a type casting, and that’s a major limitation since the customer will have to know upfront the actual type to cast to.

It would be much better if the projection container were type-safe.

Fetching an interface-based Proxy projection with Spring Data JPA

With Spring Data JPA, we can map the SQL projection to a DTO that implements an interface, like the following one:

public interface PostCommentSummary {
⠀
    Long getId();
⠀
    String getTitle();
⠀
    String getReview();
}

The PostSummary interface methods define the name of the associated projection column alias and the type that needs to be used to cast the projection column value.

Behind the scenes, Spring Data JPA will use a Proxy that implements this interface when returning the PostCommentSummary object references.

In our case, we can define the findCommentSummaryByTitle repository method to use the aforementioned PostCommentSummary interface like this:

@Query("""
    select 
        p.id as id, 
        p.title as title, 
        c.review as review
    from PostComment c
    join c.post p
    where p.title like :postTitle
    order by c.id
    """)
List<PostCommentSummary> findCommentSummaryByTitle(
    @Param("postTitle") String postTitle
);

And when calling the findCommentSummaryByTitle method, we no longer have to cast the projection values:

List<PostCommentSummary> commentSummaries = postRepository
    .findCommentSummaryByTitle(titleToken);

PostCommentSummary commentSummary = commentSummaries.get(0);

assertEquals(
    Long.valueOf(1), 
    commentSummary.getId().longValue()
);

assertTrue(
    commentSummary.getTitle()
        .contains("Chapter nr. 1")
);

Much better, right?

However, there is also a downside to using the Proxy projection. We cannot provide a specific implementation for equals and hashCode, and this limits its usability.

Fetching a Record DTO projection with Spring Data JPA

While the Proxy projection is a fine solution, in reality, it’s just as easy to use a Java record as the following one:

public record PostCommentRecord(
    Long id,
    String title,
    String review
) {}

The PostCommentRecord has a very compact definition but provides support for the equals, hashCode, and toString methods.

To use the PostCommentRecord in our projection, we need to change the JPQL query to use the constructor expression, as illustrated by the following example:

@Query("""
    select new PostCommentRecord(
        p.id as id, 
        p.title as title, 
        c.review as review
    )
    from PostComment c
    join c.post p
    where p.title like :postTitle
    order by c.id
    """)
List<PostCommentRecord> findCommentRecordByTitle(
    @Param("postTitle") String postTitle
);

Normally, we’d have to use the fully-qualified name of the PostCommentRecord Java class, but thanks to the ClassImportIntegrator offered by the Hypersistence Utils project, we can use the simple Class name in the constructor expression JPQL queries.

To benefit from this feature, you just need to provide the ClassImportIntegrator that registers all DTO and Record classes in your Java-based Spring configuration bean via the hibernate.integrator_provider Hibernate setting:

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = 
        new LocalContainerEntityManagerFactoryBean();
⠀
    ...
⠀
    Properties properties = new Properties();
    properties.put(
        "hibernate.integrator_provider",
        (IntegratorProvider) () -> Collections.singletonList(
            new ClassImportIntegrator(
                List.of(
                    PostCommentDTO.class,
                    PostCommentRecord.class
                )
            )
        )
    );
    entityManagerFactoryBean.setJpaProperties(properties);
⠀
    return entityManagerFactoryBean;
}

And, when calling the findCommentRecordByTitle method, we can see that we get the expected result back:

List<PostCommentRecord> commentRecords = postRepository
    .findCommentRecordByTitle(titleToken);

PostCommentRecord commentRecord = commentRecords.get(0);

assertEquals(
    Long.valueOf(1), 
    commentRecord.id()
);

assertTrue(
    commentRecord.title()
        .contains("Chapter nr. 1")
);

And, unlike the interface-based Proxy, equality works as expected now:

assertEquals(
    commentRecord,
    new PostCommentRecord(
        commentRecord.id(),
        commentRecord.title(),
        commentRecord.review()
    )
);

So, the Java Record solution is much better than the interface-based Proxy one.

Fetching a POJO DTO projection with Spring Data JPA

Nevertheless, if you want to have absolute control over your DTO classes, then a POJO is the most flexible solution.

In our case, we can define the following PostCommentDTO class:

public class PostCommentDTO {
⠀
    private final Long id;
⠀
    private final String title;
⠀
    private final String review;
⠀
    public PostCommentDTO(Long id, String title, String review) {
        this.id = id;
        this.title = title;
        this.review = review;
    }
⠀
    public Long getId() {
        return id;
    }
⠀
    public String getTitle() {
        return title;
    }
⠀
    public String getReview() {
        return review;
    }
⠀
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof PostCommentDTO)) return false;
        PostCommentDTO that = (PostCommentDTO) o;
        return Objects.equals(getId(), that.getId()) &&
               Objects.equals(getTitle(), that.getTitle()) &&
               Objects.equals(getReview(), that.getReview());
    }
⠀
    @Override
    public int hashCode() {
        return Objects.hash(
            getId(),
            getTitle(),
            getReview()
        );
    }
}

And, just like with the Java Record projection, we can fetch the PostCommentDTO using the JPQL constructor expression feature:

@Query("""
    select new PostCommentDTO(
        p.id as id, 
        p.title as title, 
        c.review as review
    )
    from PostComment c
    join c.post p
    where p.title like :postTitle
    order by c.id
    """)
List<PostCommentDTO> findCommentDTOByTitle(
    @Param("postTitle") String postTitle
);

And, when calling the findCommentDTOByTitle method, the underlying JPQL query is going to map the SQL record projection to the PostCommentDTO Java object:

List<PostCommentDTO> commentDTOs = postRepository
    .findCommentDTOByTitle(titleToken);

PostCommentDTO commentDTO = commentDTOs.get(0);

assertEquals(
    Long.valueOf(1), 
    commentDTO.getId()
);

assertTrue(
    commentDTO.getTitle()
        .contains("Chapter nr. 1")
);

assertEquals(
    commentDTO,
    new PostCommentDTO(
        commentDTO.getId(),
        commentDTO.getTitle(),
        commentDTO.getReview()
    )
);

Fetching a hierarchical DTO projection with Spring Data JPA

And that’s not all. You can actually fetch hierarchical DTO projections with Spring Data JPA.

Assuming we have the following PostDTO and PostCommentDTO classes that form a bidirectional association:

We can actually fetch this hierarchical DTO structure using the Hibernate ResultTransformer.

To be able to do that, we need a custom Spring Data JPA Repository, as explained in this article:

The findPostDTOByTitle is implemented in the custom Spring Data JPA repository so that it can take advantage of the Hibernate ResultTransformer functionality:

public class CustomPostRepositoryImpl implements CustomPostRepository {
⠀
    @PersistenceContext
    private EntityManager entityManager;
⠀
    @Override
    public List<PostDTO> findPostDTOByTitle(
            @Param("postTitle") String postTitle) {
        return entityManager.createNativeQuery("""
            SELECT p.id AS p_id, 
                   p.title AS p_title,
                   pc.id AS pc_id, 
                   pc.review AS pc_review
            FROM post p
            JOIN post_comment pc ON p.id = pc.post_id
            WHERE p.title LIKE :postTitle
            ORDER BY pc.id
            """)
        .setParameter("postTitle", postTitle)
        .unwrap(org.hibernate.query.Query.class)
        .setResultTransformer(new PostDTOResultTransformer())
        .getResultList();
    }
}

The PostDTOResultTransformer allows us to map the default Object[] JPA query projection to the PostDTO while also adding the PostCommentDTO child elements to the comments collection of the parent PostDTO class:

public class PostDTOResultTransformer implements ResultTransformer {
⠀
    private Map<Long, PostDTO> postDTOMap = new LinkedHashMap<>();
⠀
    @Override
    public PostDTO transformTuple(Object[] tuple, String[] aliases) {
        Map<String, Integer> aliasToIndexMap = aliasToIndexMap(aliases);
⠀
        Long postId = AbstractTest.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<PostDTO> transformList(List collection) {
        return new ArrayList<>(postDTOMap.values());
    }
⠀
    private Map<String, Integer> aliasToIndexMap(String[] aliases) {
        Map<String, Integer> aliasToIndexMap = new LinkedHashMap<>();
⠀
        for (int i = 0; i < aliases.length; i++) {
            aliasToIndexMap.put(
                aliases[i].toLowerCase(Locale.ROOT),
                i
            );
        }
⠀
        return aliasToIndexMap;
    }
}

And, when calling the findPostDTOByTitle method, we get the expected result set:

List<PostDTO> postDTOs = postRepository.findPostDTOByTitle(titleToken);

assertEquals(POST_COUNT, postDTOs.size());

PostDTO postDTO = postDTOs.get(0);

assertEquals(
    Long.valueOf(1), 
    postDTO.getId()
);

assertTrue(
    postDTO.getTitle()
        .contains("Chapter nr. 1")
);

assertEquals(
    POST_COMMENT_COUNT, 
    postDTO.getComments().size()
);

Awesome, right?

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

Conclusion

Knowing how to use DTO projections with Spring Data JPA is very important when developing a non-trivial application.

While the Spring documentation shows you some very simple use cases, in reality, you can fetch hierarchical DTO structures or Java Record projections using Spring Data JPA.

Insert details about how the information is going to be processed

DOWNLOAD NOW