如何连接3个表并使用jooq迭代结果? [英] How to join 3 tables and iterate results using jooq?

问题描述

我有COURSE，STUDENT，SCHEDULE表.

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.

在jooq中加入这3个表的最佳方法是什么?我认为是这样的:

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();

问题(2):

当我得到结果时，将结果分为学生对象和课程对象的最佳方法是什么?我的意思是，由于类型是Result ?，我们有什么方法可以将结果映射到学生，课程实体中，而不必费力地做这样的事情:

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()....)
}

推荐答案

答案1

在jooq中加入这3个表的最佳方法是什么?我认为这就像[...]

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. 不带任何值的SQL语句增加复杂性，例如维护声明时
2. 防止在某些不能很好地处理派生表的数据库中进行优化

相反，只需在单个语句中连接两个表即可:

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();

答案2

您可以使用各种Record.into()方法之一，例如

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);
}

这篇关于如何连接3个表并使用jooq迭代结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！