SimpleFlatMapper: Multiple 1-N relationship

Question

Please refer this link:

https://www.petrikainulainen.net/programming/jooq/jooq-tips-implementing-a-read-only-one-to-many-relationship/#comment-1966747

import java.util.List;

public class StudentDTO {
 
    private Long id;
    private String name;
    private List<BookDTO> books;
 
    //Getters and setters are omitted
}

here there is one 1-N relationship books which needs to be extracted. That is achieved through addKeys.

But let us say if the POJO is:

public class StudentDTO {
 
    private Long id;
    private String name;
    private List<BookDTO> books;
    private List<CourseDTO> courses;
    //Getters and setters are omitted
}

then how can we achieve it?

Answer 1

I understand you chose to use SimpleFlatMapper because that article recommended it. But jOOQ has a much better out of the box way to map to-many relationship, see the MULTISET operator.

Assuming you have a constructor for your attributes, or even use a record type:

public record StudentDTO(
    Long id,
    String name,
    List<BookDTO> books,
    List<CourseDTO> courses
{}

You can now write a query like this:

List<StudentDTO> students =
ctx.select(
       STUDENT.ID,
       STUDENT.NAME,
       multiset(
           select(STUDENT_BOOK.book().ID, ...)
           .from(STUDENT_BOOK)
           .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
       ).convertFrom(r -> r.map(Records.mapping(BookDTO::new))),
       multiset(
           select(STUDENT_COURSE.course().ID, ...)
           .from(STUDENT_COURSE)
           .where(STUDENT_COURSE.STUDENT_ID.eq(STUDENT.ID))
       ).convertFrom(r -> r.map(Records.mapping(CourseDTO::new)))
   )
   .from(STUDENT)
   .fetch(Records.mapping(StudentDTO::new));

The ad-hoc conversion feature makes the whole thing type safe.