Friday, October 7, 2022
HomeProgrammingThe Second Greatest Approach to Fetch a Spring Knowledge JPA DTO Projection...

The Second Greatest Approach to Fetch a Spring Knowledge JPA DTO Projection – Java, SQL and jOOQ.


I’ve simply stumbled upon this nice publish by Vlad Mihalcea, titled The Greatest Approach to Fetch a Spring Knowledge JPA DTO Projection. It acquired some good traction on reddit, too. That is such a pleasant use-case and apt resolution, I needed to shortly present the second greatest approach of doing the identical, with jOOQ this time.

Tip: you may simply use jOOQ with Spring Knowledge 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 publish, which tasks issues into this kind hierarchy:

public document PostCommentDTO (
    Lengthy id,
    String overview
) {}

public document PostDTO (
    Lengthy id,
    String title,
    Checklist<PostCommentDTO> feedback
) {}

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

Checklist<PostDTO> 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 when you’re not nesting collections greater than 1 stage deep):

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

Each options are utterly kind secure, utilizing ad-hoc document conversion. You modify the schema, re-generate the code, and your code not compiles.

Aside from the question itself, you don’t want to jot down any extra infrastructure logic.

Cool, proper? 🙂

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments