jOOQ技术问询：多表连接查询返回三个对象列表的实现

嘿，我来帮你搞定这个jOOQ多表左连接并映射到嵌套对象列表的问题！

首先咱们先补全查询语句，再搞定结果映射——毕竟左连接会产生重复的父表记录，得把它们分组整理成你要的「Student包含Volunteermatch列表，每个匹配关联Volunteer」结构。

1. 补全jOOQ查询代码 #

假设你的表关联字段是：

• Student主键是STUDENT_ID
• Volunteermatch通过STUDENT_ID关联Student，通过VOLUNTEER_ID关联Volunteer
• Volunteer主键是VOLUNTEER_ID

完整的查询构建代码如下：

// 构建三表左连接查询
SelectJoinStep<Record> query = using(configuration())
    .select(Student.STUDENT.fields())
    .select(Volunteermatch.VOLUNTEERMATCH.fields())
    .select(Volunteer.VOLUNTEER.fields())
    .from(Student.STUDENT)
    // 左连接Student和Volunteermatch
    .leftJoin(Volunteermatch.VOLUNTEERMATCH)
        .on(Volunteermatch.VOLUNTEERMATCH.STUDENT_ID.eq(Student.STUDENT.STUDENT_ID))
    // 左连接Volunteermatch和Volunteer
    .leftJoin(Volunteer.VOLUNTEER)
        .on(Volunteer.VOLUNTEER.VOLUNTEER_ID.eq(Volunteermatch.VOLUNTEERMATCH.VOLUNTEER_ID));

2. 结果映射：三种实现方式 #

方式一：Java流手动分组（适合所有jOOQ版本） #

如果你的jOOQ版本较低，用Java流来分组整理结果是最稳妥的：

// 执行查询获取结果集
Result<Record> result = query.fetch();

// 分组：将重复的Student合并，每个Student对应其Volunteermatch列表
Map<Student, List<Volunteermatch>> studentMatchMap = result.stream()
    .map(record -> {
        // 映射Student实体
        Student student = record.into(Student.STUDENT).into(Student.class);
        // 映射Volunteermatch实体（左连接可能返回null）
        Volunteermatch match = record.into(Volunteermatch.VOLUNTEERMATCH).into(Volunteermatch.class);
        
        if (match != null) {
            // 映射Volunteer实体（左连接可能返回null）
            Volunteer volunteer = record.into(Volunteer.VOLUNTEER).into(Volunteer.class);
            match.setVolunteer(volunteer); // 假设Volunteermatch有setVolunteer方法
        }
        
        return new AbstractMap.SimpleEntry<>(student, match);
    })
    .filter(entry -> entry.getKey() != null)
    .collect(Collectors.groupingBy(
        Map.Entry::getKey,
        // 过滤掉null的Volunteermatch
        Collectors.mapping(Map.Entry::getValue, Collectors.filtering(Objects::nonNull, Collectors.toList()))
    ));

// 转换为最终的Student列表（每个Student已包含关联的匹配和志愿者）
List<Student> finalResult = new ArrayList<>(studentMatchMap.keySet());
finalResult.forEach(student -> student.setVolunteermatches(studentMatchMap.get(student)));

方式二：jOOQ原生fetchGroups（简化分组代码） #

用jOOQ的fetchGroups方法可以直接按Student分组，减少手动流处理：

// 按Student字段分组查询结果
Map<Record, Result<Record>> groupedResult = query.fetchGroups(Student.STUDENT.fields());

List<Student> finalResult = new ArrayList<>();
for (Map.Entry<Record, Result<Record>> entry : groupedResult.entrySet()) {
    // 映射Student实体
    Student student = entry.getKey().into(Student.class);
    
    // 映射该Student对应的所有Volunteermatch和关联的Volunteer
    List<Volunteermatch> matches = entry.getValue().stream()
        .map(record -> {
            Volunteermatch match = record.into(Volunteermatch.VOLUNTEERMATCH).into(Volunteermatch.class);
            if (match != null) {
                Volunteer volunteer = record.into(Volunteer.VOLUNTEER).into(Volunteer.class);
                match.setVolunteer(volunteer);
            }
            return match;
        })
        .filter(Objects::nonNull)
        .collect(Collectors.toList());
    
    student.setVolunteermatches(matches);
    finalResult.add(student);
}

方式三：MULTISET嵌套查询（jOOQ 3.14+推荐） #

如果你的jOOQ版本在3.14及以上，用MULTISET可以在SQL层面就定义嵌套结构，性能更优，代码也更简洁：

List<Student> finalResult = using(configuration())
    .select(
        Student.STUDENT.fields(),
        // 嵌套查询：获取当前Student对应的所有Volunteermatch及关联Volunteer
        multiset(
            select(Volunteermatch.VOLUNTEERMATCH.fields(), Volunteer.VOLUNTEER.fields())
                .from(Volunteermatch.VOLUNTEERMATCH)
                .leftJoin(Volunteer.VOLUNTEER)
                    .on(Volunteer.VOLUNTEER.VOLUNTEER_ID.eq(Volunteermatch.VOLUNTEERMATCH.VOLUNTEER_ID))
                .where(Volunteermatch.VOLUNTEERMATCH.STUDENT_ID.eq(Student.STUDENT.STUDENT_ID))
        ).as("matches").convertFrom(recordStream -> 
            recordStream.map(record -> {
                Volunteermatch match = record.into(Volunteermatch.VOLUNTEERMATCH).into(Volunteermatch.class);
                if (match != null) {
                    Volunteer volunteer = record.into(Volunteer.VOLUNTEER).into(Volunteer.class);
                    match.setVolunteer(volunteer);
                }
                return match;
            })
        )
    )
    .from(Student.STUDENT)
    .fetch(record -> {
        Student student = record.into(Student.STUDENT).into(Student.class);
        // 直接获取嵌套的匹配列表
        student.setVolunteermatches(record.get("matches", List.class));
        return student;
    });

关键注意事项 #

• 确保你的实体类（Student/Volunteermatch/Volunteer）有默认构造函数，或者用jOOQ的ConstructorMapper自定义构造映射。
• 左连接会产生null的Volunteermatch或Volunteer记录，一定要做null检查，避免空指针异常。
• 如果字段名和实体类属性名不匹配，可以自定义RecordMapper手动映射每个字段。

内容的提问来源于stack exchange，提问作者Rico