rss-bridge 2022-08-30T14:48:44+00:00

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 … Continue reading The Second Best Way to Fetch a Spring Data JPA DTO Projection →

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

Posted on August 30, 2022August 30, 2022 by lukaseder

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:

Like Loading...

###
Published by lukaseder

I made jOOQ View all posts by lukaseder

Original source