4

The Second Best Way to Fetch a Spring Data JPA DTO Projection

 2 years ago
source link: https://blog.jooq.org/the-second-best-way-to-fetch-a-spring-data-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

The Second Best Way to Fetch a Spring Data JPA DTO Projection

I’ve just stumbled upon this great post by Vlad Mihalcea, titled The Best Way to Fetch a Spring Data JPA DTO Projection. It got some nice traction on reddit, too. This is such a nice use-case and apt solution, I wanted to quickly show the second best way of doing the same, with jOOQ this time.

Tip: you can easily use jOOQ with Spring Data JPA, just use Spring Boot’s jOOQ starter, inject the DataSource to jOOQ, then delegate all repository queries to jOOQ.

I’ll skip right to the hierarchical DTO projection from the post, which projects things into this type hierarchy:

public record PostCommentDTO (
Long id,
String review
) {}
public record PostDTO (
Long id,
String title,
List<PostCommentDTO> comments
) {}

So, we’ll be using jOOQ like this using the MULTISET value constructor:

List<PostDTO> result =
ctx.select(
POST.ID,
POST.TITLE,
multiset(
select(POST_COMMENT.ID, POST_COMMENT.REVIEW)
.from(POST_COMMENT)
.where(POST_COMMENT.POST_ID.eq(POST.ID))
).convertFrom(r -> r.map(mapping(PostCommentDTO::new)))
)
.from(POST)
.where(POST.TITLE.like(postTitle))
.fetch(mapping(PostDTO::new));

Alternatively, use the MULTISET_AGG aggregate function, if that’s more your thing (and if you’re not nesting collections more than 1 level deep):

List<PostDTO> result =
ctx.select(
POST_COMMENT.post().ID,
POST_COMMENT.post().TITLE,
multisetAgg(POST_COMMENT.ID, POST_COMMENT.REVIEW)
.convertFrom(r -> r.map(mapping(PostCommentDTO::new)))
.from(POST_COMMENT)
.where(POST_COMMENT.post().TITLE.like(postTitle))
.fetch(mapping(PostDTO::new));

Both solutions are completely type safe, using ad-hoc record conversion. You change the schema, re-generate the code, and your code no longer compiles.

Apart from the query itself, you don’t need to write any additional infrastructure logic.

Cool, right? :)

Like this:

Loading...

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK