jOOQ - nested object with many to many relationship

Question

I've been trying to figure out how to convert nested objects from a jOOQ query. I've seen the RecordMapper or java 8 streams mentioned as possible solutions but have trouble figuring out how I could use those.

My objects look similar to this, with Student-Teacher being a N:N relationship and Student-Book a 1:N.

Student {
  List<Teacher> teachers
  List<Book> books;
}

Now I want to query the tables using jooq and convert that to a Student object that contains the others. Is there a straight forward way to do this? Are there examples that show how to achieve this? Is intoGroups usable with a schema similar to this? If I do intoGroups do I get a list of all the different types of records that I joined the database with?

I could also do 2 queries, that shouldn't really be a problem. Thanks.

Answer 1

Before jOOQ 3.14

Historically, jOOQ did not offer any mapping solutions for many-to-many relationships. It is not easy to achieve using flat result sets originating from SQL joins, where cartesian products between unrelated entities (in your case: between Teacher and Book) are not uncommon.

Solutions using 2 or more queries are possible, but there's a lot of hand written mapping code that one would like to avoid.

After jOOQ 3.14

Starting from jOOQ 3.14 and the new SQL/XML and SQL/JSON support, this will be possible relatively easily. In essence, you will be using your RDBMS's native XML or JSON support to nest collections directly in SQL.

You can write a query like this (assuming you use the code generator):

List<Student> students =
ctx.select(jsonObject(
     jsonEntry("name", STUDENT.NAME),
     jsonEntry("id", STUDENT.ID),
     jsonEntry("teachers", field(
       select(jsonArrayAgg(jsonObject(TEACHER.NAME, TEACHER.ID)))
       .from(TEACHER)
       .join(STUDENT_TEACHER).on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     )),
     jsonEntry("books", field(
       select(jsonArrayAgg(jsonObject(BOOK.NAME, BOOK.ID)))
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ))
   ))
   .from(STUDENT)
   .fetchInto(Student.class);

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that's a problem, use COALESCE()

After jOOQ 3.15

jOOQ finally has MULTISET support, see #3884 or this blog post. This allows for simplifying the above JSON approach:

Using reflection mapping

List<Student> students =
ctx.select(
     STUDENT.NAME,
     STUDENT.ID,
     multiset(
       select(TEACHER.NAME, TEACHER.ID)
       .from(TEACHER)
       .join(STUDENT_TEACHER)
         .on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     ).as("teachers").convertFrom(r -> r.map(Teacher.class)),
     multiset(
       select(BOOK.NAME, BOOK.ID)
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ).as("books").convertFrom(r -> r.map(Book.class))
   ))
   .from(STUDENT)
   .fetchInto(Student.class);

Using type-safe, reflection free mapping

Much better than using reflection would be if you had immutable DTOs (e.g. Java 16 records), in case of which you could map jOOQ records directly into constructor references, in a type-safe, compile-time checked, reflection free way:

List<Student> students =
ctx.select(
     STUDENT.NAME,
     STUDENT.ID,
     multiset(
       select(TEACHER.NAME, TEACHER.ID)
       .from(TEACHER)
       .join(STUDENT_TEACHER)
         .on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     ).as("teachers").convertFrom(r -> r.map(Records.mapping(Teacher::new))),
     multiset(
       select(BOOK.NAME, BOOK.ID)
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ).as("books").convertFrom(r -> r.map(Records.mapping(Book::new)))
   ))
   .from(STUDENT)
   .fetch(Records.mapping(Student::new));