Join 3 tables and map one to many relation

Question

How to map records with one-many relation. Say below query returns list of Course and List of Student for each Schedule and i wanted to query multiple schedule's

List<Record> result = sql
    .select()
    .from(SCHEUDLE)
    .innerJoin(COURSE)
    .on(SCHEDULE.ID.eq(COURSE.SID))
    .leftJoin(STUDENT)
    .on(SCHEDULE.ID.eq(STUDENT.SID))
    .where(SCHEUDLE.ID.in(List.of(10,11,12)))
    .fetch();

How to map List of Record to List of Schedule with each Scheudule having list of Courses and list of Students?

Consider domain as

public class Scheudule{
 String scheuduleName;
 List<Course> courses;
 List<Student> students 
}

Answer 1

Before jOOQ 3.14

Historically, jOOQ does not offer an extensive set of object graph mapping features, and third party tools like SimpleFlatMapper were recommended for mapping tasks like this.

The biggest caveat is that it's wrong to think that joins, including left joins are an appropriate way to create nested collections. In your case, there is a cartesian product between the joined COURSE and STUDENT tables, which is almost impossible (let alone inefficient) to deduplicate again. If you want to solve this with joins, you'd have to run at least 2 queries:

• A query fetching SCHEDULE / COURSE relationships
• A query fetching SCHEDULE / STUDENT relationships

After jOOQ 3.14

Luckily, jOOQ 3.14 will now have an answer to these problems. Starting with jOOQ 3.14's SQL/XML and SQL/JSON support, a more SQL-style approach to mapping nested collections can be achieved. For example, using JSON:

List<Schedule> result =
sql.select(
     jsonEntry("scheduleName", SCHEDULE.NAME),
     jsonEntry("courses", field(
       select(jsonArrayAgg(jsonObject(COURSE.fields())))
       .from(COURSE)
       .where(COURSE.SID.eq(SCHEDULE.ID))
     )),
     jsonEntry("students", field(
       select(jsonArrayAgg(jsonObject(STUDENT.fields())))
       .from(STUDENT)
       .where(STUDENT.SID.eq(SCHEDULE.ID))
     ))
   )
   .from(SCHEDULE)
   .where(SCHEDULE.ID.in(10, 11, 12))
   .fetchInto(Schedule.class);

If you call fetch() on this query, you'll get a set of JSON documents in the desired structure. If you have Jackson or Gson on your classpath, jOOQ's DefaultConverterProvider can map the JSON document to your own classes using those libraries. Their mapping rules apply.

The same can be done using XML and JAXB.

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

Note

Future jOOQ versions may offer this functionality without depending on third party mapping libraries.