8

The best way to fetch a Spring Data JPA DTO Projection

 2 years ago
source link: https://vladmihalcea.com/spring-jpa-dto-projection/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
Last modified: Aug 30, 2022

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

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.

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);
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 Hibernate Types 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:

The PostDTO and PostCommentDTO used for DTO projection

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:

Custom Spring Data Repository

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?

I'm running an online workshop on the 15th of September about High-Performance SQL.

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.

Transactions and Concurrency Control eBook

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK