The Second Finest Solution to Fetch a Spring Information JPA DTO Projection – Java, SQL and jOOQ.

I’ve simply stumbled upon this nice submit by Vlad Mihalcea, titled The Finest Solution to Fetch a Spring Information JPA DTO Projection. It bought some good traction on reddit, too. That is such a pleasant use-case and apt resolution, I needed to rapidly present the second greatest means of doing the identical, with jOOQ this time.

Tip: you’ll be able to simply use jOOQ with Spring Information JPA, simply use Spring Boot’s jOOQ starter, inject the DataSource to jOOQ, then delegate all repository queries to jOOQ.

I’ll skip proper to the hierarchical DTO projection from the submit, which initiatives issues into this kind hierarchy:

public report PostCommentDTO (
    Lengthy id,
    String assessment
) {}

public report PostDTO (
    Lengthy id,
    String title,
    Listing feedback
) {}

So, we’ll be utilizing jOOQ like this utilizing the MULTISET worth constructor:

Listing end result =
ctx.choose(
        POST.ID,
        POST.TITLE,
        multiset(
            choose(POST_COMMENT.ID, POST_COMMENT.REVIEW)
            .from(POST_COMMENT)
            .the place(POST_COMMENT.POST_ID.eq(POST.ID))
        ).convertFrom(r -> r.map(mapping(PostCommentDTO::new)))
   )
   .from(POST)
   .the place(POST.TITLE.like(postTitle))
   .fetch(mapping(PostDTO::new));

Alternatively, use the MULTISET_AGG mixture operate, if that’s extra your factor (and in case you’re not nesting collections greater than 1 stage deep):

Listing end result =
ctx.choose(
        POST_COMMENT.submit().ID,
        POST_COMMENT.submit().TITLE,
        multisetAgg(POST_COMMENT.ID, POST_COMMENT.REVIEW)
            .convertFrom(r -> r.map(mapping(PostCommentDTO::new)))
   .from(POST_COMMENT)
   .the place(POST_COMMENT.submit().TITLE.like(postTitle))
   .fetch(mapping(PostDTO::new));

Each options are utterly sort protected, utilizing ad-hoc report conversion. You alter the schema, re-generate the code, and your code now not compiles.

Aside from the question itself, you don’t want to put in writing any extra infrastructure logic.

Cool, proper? 🙂