How to join 3 tables and iterate results using jooq?

Question

I have COURSE, STUDENT, SCHEDULE tables.

table course(id, name, ....), 
table student(id, name, ...), 
table schedule(id, c_id, s_id).

Now I want to left join schedule table with course and student table.

Question (1):

What's the best way to do join these 3 tables in jooq? I assume it's like:

TableLike<?> firstjoin = sql
    .select()
    .from(Tables.SCHEUDLE)
    .leftOuterJoin(Tables.COURSE)
    .on(Tables.SCHEDULE.CID.eq(Tables.COURSE.ID))
    .asTable();

Result<?> result = sql
    .select()
    .from(firstjoin)
    .leftOuterJoin(Tables.STUDENT)
    .on(Tables.SCHEDULE.SID.eq(Tables.STUDENT.ID))
    .fetch();

Question (2):

When I get the result, what's the best way to split results into Student objects and Course objects? I mean since the type is Result?, is there any way we can mapping result into student, course entities instead of tediously doing something like this:

for(Record r: result){
   Student s = new Student(r.filed(), r.filed()...);
   Course c = new Course(r.filed(), r.filed()....)
}

Answer 1

Answer 1

What's the best way to do join these 3 tables in jooq? I assume it's like [...]

While your query is correct, I wouldn't join like you did. Your approach creates a derived table, which

1. Adds complexity to the SQL statement with no value, e.g. when maintaining the statement
2. Prevents optimisation in some databases that poorly handle derived tables

Instead, just join both tables in a single statement:

Result<?> result = sql
    .select()
    .from(SCHEUDLE)
    .leftOuterJoin(COURSE)
    .on(SCHEDULE.CID.eq(COURSE.ID))
    .leftOuterJoin(STUDENT)
    .on(SCHEDULE.SID.eq(STUDENT.ID))
    .fetch();

Answer 2

You can use one of the various Record.into() methods, such as Record.into(Table)

for (Record r : result) {
    StudentRecord s = r.into(STUDENT);
    CourseRecord c = r.into(COURSE);
}