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.
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();
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()....)
}
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
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();
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);
}
Since jOOQ 3.15, jOOQ allows for nesting collections using its standard SQL MULTISET
value constructor operator, which can be emulated using SQL/JSON or SQL/XML on a variety of RDBMS. This approach would probably better solve the underlying problem you have.
sql.select(
SCHEDULE,
multiset(
select(
SCHEDULE.course(),
multiset(
selectFrom(SCHEDULE.course().student())
)
)
.from(SCHEDULE.course())
)
)
.from(SCHEDULE)
.fetch();
The above approach also uses:
select(SCHEDULE)
where SCHEDULE
is a Table
)MULTISET
correlated subqueries).Furthermore, in order to map jOOQ records to your own DTO's you might want to use jOOQ 3.15's ad-hoc conversion possibilities