The Second Best Way to Fetch a Spring Data JPA DTO Projection – Java, SQL and jOOQ.

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? 🙂