Map jooq record data to multiple pojos

Question

We have multiple tables like :

• School one to many teacher
• teacher one to many subject
• teacher one to many classes

Entity are as follows

   public class School {
    
        private String name;
        private long id;
        private List<teacher> teachers;

   public School() {
    }
}


public class teachers {

    private String name;
    private Long id;
    private List<Subject> subjects;
    private List<Classes> classes;

}

public class  Subject {

    private String name;
    private long id;

    public Subject() {
    }
}

public class Classes{

    private String name;
    private long id;
        public Classes() {
    }
}

we have written the jooq query for the required fields. For a single school data, we were getting multiple rows instead of one that was expected. However, We were unable to map the data.

We tried :

• ModelMapper( Unable to find a way to covert multiple basically horizontal(table) records to vertical)

• intoGroups() worked only till single join(bw two tables)

• simpleflatmapper same issue

Is there any way we can achieve it. Are we missing something?

PS: In response, We don't require all the columns(variable) from all the tables.

Answer 1

That's a tricky question for a school assignment, given that this has been, historically, one of jOOQ's most missing features :)

A jOOQ 3.15+ solution using MULTISET

In addition to the below SQL/XML or SQL/JSON based solution, jOOQ 3.15 now supports the standard SQL MULTISET value constructor operator as well as a synthetic MULTISET_AGG aggregate function, which can be used like this:

List<School> schools =
ctx.select(
     SCHOOL.NAME,
     SCHOOL.ID,
     multisetAgg(
       TEACHER.NAME,
       TEACHER.ID,
       multiset(
         select(SUBJECT.NAME, SUBJECT.ID)
         .from(SUBJECT)
         .where(SUBJECT.TEACHER_ID.eq(TEACHER.ID))
       ).as("subjects").convertFrom(r -> r.map(Records.mapping(Subject::new))),
       multiset(
         select(CLASS.NAME, CLASS.ID)
         .from(CLASS)
         .where(CLASS.TEACHER_ID.eq(TEACHER.ID))
       ).as("classes").convertFrom(r -> r.map(Records.mapping(Classes::new)))
     ).as("teachers").convertFrom(r -> r.map(Records.mapping(Teachers::new)))
   )
   .from(SCHOOL)
   .join(TEACHER).on(TEACHER.SCHOOL_ID.eq(SCHOOL.ID))
   .groupBy(SCHOOL.NAME, SCHOOL.ID)
   .fetch(Records.mapping(School::new));

The above approach using the various Records.mapping() overloads along with ad-hoc data type conversion assumes the presence of an immutable constructor, such as you'd get if your classes were Java 16 records:

record Subject (String name, long id) {}

A jOOQ 3.14+ solution using SQL/XML or SQL/JSON

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. (All other approaches using joins and trying to deduplicate and shoe-horn flat result sets into nested data structures will not work well enough, as you've noticed)

You can write a query like this (assuming you use the code generator, and assuming you're interested in a tree structure with the School at the top):

List<School> schools =
ctx.select(jsonObject(
     jsonEntry("name", SCHOOL.NAME),
     jsonEntry("id", SCHOOL.ID),
     jsonEntry("teachers", jsonArrayAgg(jsonObject(
       jsonEntry("name", TEACHER.NAME),
       jsonEntry("id", TEACHER.ID),
       jsonEntry("subjects", field(
         select(jsonArrayAgg(jsonObject(SUBJECT.NAME, SUBJECT.ID)))
         .from(SUBJECT)
         .where(SUBJECT.TEACHER_ID.eq(TEACHER.ID))
       )),
       jsonEntry("classes", field(
         select(jsonArrayAgg(jsonObject(CLASS.NAME, CLASS.ID)))
         .from(CLASS)
         .where(CLASS.TEACHER_ID.eq(TEACHER.ID))
       ))
     )))
   ))
   .from(SCHOOL)
   .join(TEACHER).on(TEACHER.SCHOOL_ID.eq(SCHOOL.ID))
   .groupBy(SCHOOL.NAME, SCHOOL.ID)
   .fetchInto(School.class);

This solution is based on assumptions of your schema, namely that there is a to-one relationship between both SUBJECT -> TEACHER and CLASS -> TEACHER.

Also, you can see I've still used a join to group TEACHER per SCHOOL, aggregating the teachers using JSON_ARRAYAGG(). That's one option, another correlated subquery as for the SUBJECT and CLASS queries would have been possible as well.

A simpler solution might be possible using SQL Server's FOR JSON clause, which can be emulated in other dialects.