一对多的SQL到底怎么写？

兜兜转转，又回到最初的起点~ hahhahaha.. （给彬哥和志文??)

抛出问题

工作中，我们经常会遇到一对多的查表需求，如：查询一个学生的考试成绩（学生表：学号,姓名,性别,年龄,学院 课程表：课程编码,课程名,考试分数,学号）

我们想把student和course这种一对多的关系封装成如下对象（用作后端返回前端的数据：体现一对多关系，且根据需求返回部分字段）：

@Data
public class StudentScoreResp {
    private Integer stuCode;

    private String stuName;

    private List<Course> courseList;

    @Data
    public static class Course {
        private Integer couCode;

        private String couName;

        private Integer couScore;
    }
}
复制代码

常见的处理操作有两种：

1. 两次单表查询（先student表再course表）
2. 一次联合查询（利用mybatis的resultMap标签实现自动封装）

我们今天就讨论一下这两种查询方式。

单表查询 vs 自动封装

对于单表查询和自动封装两种方式，我们讨论两个条件：

1. 查询条件是否同时student字段和course字段；
2. 查询结果是否需要分页。

这样，对于任何一种查询方式，都需要考虑四种情况：只含stu字段,不分页、只含stu字段,分页、全部字段,不分页 、全部字段,分页。

（这个表格，本来我是想列出来详细比较一下两者的区别的，但当我把所有的思路都实现一遍后，突然就觉得没必要了）

单表查询：

只含stu字段,不分页

@Test
public void testA1() {
    StudentQuery studentQuery = new StudentQuery();
    studentQuery.setStuAge(18);
    studentQuery.setStuDept("CS");
    List<Student> studentList = studentInfoService.queryStudent(studentQuery);
    List<Integer> studentCodeList = studentList.stream().map(Student::getStuCode).collect(Collectors.toList());
    Map<Integer, List<Course>> courseMap = studentInfoService.queryCourse(studentCodeList, new CourseQuery())
            .stream().collect(Collectors.groupingBy(Course::getStuCode));

    // 封装数据，将前端需要的字段进行返回，而不是将库中查出的全部字段返回（可抽取为公共方法）
    List<StudentScoreResp> respList = Lists.newArrayList();
    studentList.forEach(student -> {
        StudentScoreResp resp = new StudentScoreResp();
        BeanUtils.copyProperties(student, resp);
        if (courseMap.containsKey(student.getStuCode())) {
            resp.setCourseList(courseMap.get(student.getStuCode()).stream().map(course -> {
                StudentScoreResp.Course courseResp = new StudentScoreResp.Course();
                BeanUtils.copyProperties(course, courseResp);
                return courseResp;
            }).collect(Collectors.toList()));
        }
        respList.add(resp);
    });

    respList.forEach(System.out::println);
}
复制代码

总结：先根据stuAge和stuDept字段查出符合的studentList,再根据studentCodeList字段查出courseList封装成courseMap，最后将两者封装到respList返回。

只含stu字段,分页

分页查询时，前端给后端传pageNo和pageSize，后端不仅返回结果集，同时返回符合查询条件的总数totalSize用于前端分页展示数据！

@Test
public void testA2() {
    StudentQuery studentQuery = new StudentQuery();
    studentQuery.setStuAge(18);
    int pageNo = 1, pageSize = 2;
    Page<Student> studentPage = studentInfoService.queryStudentPage(studentQuery, pageNo, pageSize);
    List<Integer> studentCodeList = studentPage.getResult().stream().map(Student::getStuCode).collect(Collectors.toList());
    Map<Integer, List<Course>> courseMap = studentInfoService.queryCourse(studentCodeList, new CourseQuery())
            .stream().collect(Collectors.groupingBy(Course::getStuCode));

    // 封装数据，将前端需要的字段进行返回，而不是将库中查出的全部字段返回（可抽取为公共方法）
    List<StudentScoreResp> respList = Lists.newArrayList();
    studentPage.getResult().forEach(student -> {
        StudentScoreResp resp = new StudentScoreResp();
        BeanUtils.copyProperties(student, resp);
        if (courseMap.containsKey(student.getStuCode())) {
            resp.setCourseList(courseMap.get(student.getStuCode()).stream().map(course -> {
                StudentScoreResp.Course courseResp = new StudentScoreResp.Course();
                BeanUtils.copyProperties(course, courseResp);
                return courseResp;
            }).collect(Collectors.toList()));
        }
        respList.add(resp);
    });

    System.out.println("Total number：" + studentPage.getTotal());
    respList.forEach(System.out::println);
}
复制代码

在queryStudentPage方法中我们使用了PageHelper分页插件来实现单表的自动分页功能，同时返回数据除了结果集还有totalSize，非常好用！

@Override
public Page<Student> queryStudentPage(StudentQuery query, int pageNo, int pageSize) {
    return PageHelper.startPage(pageNo, pageSize).doSelectPage(() -> {
        StudentExample example = buildStudentExample(query);
        studentMapper.selectByExample(example);
    });
}
复制代码

总结：借助pageHelper插件非常方便的实现了对单表student的自动分页。

全部字段,不分页

@Test
public void testA3() {
    StudentQuery studentQuery = new StudentQuery();
    studentQuery.setStuAge(18);
    studentQuery.setStuDept("CS");
    List<Student> studentList = studentInfoService.queryStudent(studentQuery);
    CourseQuery courseQuery = new CourseQuery();
    courseQuery.setCouName("数据结构");
    List<Integer> studentCodeList = studentList.stream().map(Student::getStuCode).collect(Collectors.toList());
    // 年龄>=18学院为CS的同学不一定都有数据结构课程成绩，所有我们需要先查出含有数据结构的同学，再查出这个同学所有的课程成绩
    List<Integer> filterStudentCodeList = studentInfoService.queryCourse(studentCodeList, courseQuery)
            .stream().map(Course::getStuCode).distinct().collect(Collectors.toList());
    Map<Integer, List<Course>> courseMap = studentInfoService.queryCourse(filterStudentCodeList, new CourseQuery())
            .stream().collect(Collectors.groupingBy(Course::getStuCode));

    // 封装数据，将前端需要的字段进行返回，而不是将库中查出的全部字段返回（可抽取为公共方法）
    List<StudentScoreResp> respList = Lists.newArrayList();
    studentList.forEach(student -> {
        if (courseMap.containsKey(student.getStuCode())) {
            StudentScoreResp resp = new StudentScoreResp();
            BeanUtils.copyProperties(student, resp);
            resp.setCourseList(courseMap.get(student.getStuCode()).stream().map(course -> {
                StudentScoreResp.Course courseResp = new StudentScoreResp.Course();
                BeanUtils.copyProperties(course, courseResp);
                return courseResp;
            }).collect(Collectors.toList()));
            respList.add(resp);
        }
    });

    respList.forEach(System.out::println);
}
复制代码

总结：我们的目的是查出年龄>=18学院为CS且含有数据结构成绩同学的所有成绩，有点绕?（不过很符合业务场景）。就需要查两次course表：一次查出含有数据结构成绩的同学，再一次查出这些同学的所有成绩。

全部字段,分页

warning：这里的查询是含有数据结构成绩的同学才被返回，因此无法使用pageHelper或者连表+limit直接分页得到结果。

@Test
public void testA4() {
    StudentQuery studentQuery = new StudentQuery();
    studentQuery.setStuAge(18);
    List<Student> studentList = studentInfoService.queryStudent(studentQuery);
    CourseQuery courseQuery = new CourseQuery();
    courseQuery.setCouName("数据");
    List<Integer> studentCodeList = studentList.stream().map(Student::getStuCode).collect(Collectors.toList());
    // 年龄>=18学院为CS的同学不一定都有数据结构课程成绩，所有我们需要先查出含有数据结构的同学，再查出这个同学所有的课程成绩
    List<Integer> filterStudentCodeList = studentInfoService.queryCourse(studentCodeList, courseQuery)
            .stream().map(Course::getStuCode).distinct().collect(Collectors.toList());
    int pageNo = 1, pageSize = 2;
    int offset = (pageNo - 1) * pageSize, lastIndex = Math.min(offset + pageSize, filterStudentCodeList.size());
    Map<Integer, List<Course>> courseMap = studentInfoService.queryCourse(filterStudentCodeList.subList(offset, lastIndex),
            new CourseQuery()).stream().collect(Collectors.groupingBy(Course::getStuCode));

    // 封装数据，将前端需要的字段进行返回，而不是将库中查出的全部字段返回（可抽取为公共方法）
    List<StudentScoreResp> respList = Lists.newArrayList();
    studentList.forEach(student -> {
        if (courseMap.containsKey(student.getStuCode())) {
            StudentScoreResp resp = new StudentScoreResp();
            BeanUtils.copyProperties(student, resp);
            resp.setCourseList(courseMap.get(student.getStuCode()).stream().map(course -> {
                StudentScoreResp.Course courseResp = new StudentScoreResp.Course();
                BeanUtils.copyProperties(course, courseResp);
                return courseResp;
            }).collect(Collectors.toList()));
            respList.add(resp);
        }
    });

    System.out.println("Total number:" + filterStudentCodeList.size());
    respList.forEach(System.out::println);
}
复制代码

总结：「全部字段,分页」的实现是在testA3的基础上，进行第二次查course表符合条件同学的所有成绩时，只查需要返回的数据。

自动封装

面对疾风吧~ 本以为是上乘功法?

刚开始做项目的时候，需要从数据库中查出一对多的关系，当时就查了很多同事大佬写的代码，发现大家怎么都不用mybatis的resultMap标签直接连表操作查出一对多的关系，思考人生…

只含stu字段,不分页

@Test
public void testB1() {
    StudentScoreQuery query = new StudentScoreQuery();
    query.setStuAge(18);
    query.setStuDept("CS");
    List<StudentScore> studentScoreList = studentInfoService.queryStudentScore(query);

    // 封装数据，将前端需要的字段进行返回，而不是将库中查出的全部字段返回
    List<StudentScoreResp> respList = Lists.newArrayList();
    studentScoreList.forEach(studentScore -> {
        StudentScoreResp resp = new StudentScoreResp();
        BeanUtils.copyProperties(studentScore.getStudent(), resp);
        resp.setCourseList(studentScore.getCourseList().stream().map(course -> {
            StudentScoreResp.Course courseResp = new StudentScoreResp.Course();
            BeanUtils.copyProperties(course, courseResp);
            return courseResp;
        }).collect(Collectors.toList()));
        respList.add(resp);
    });

    studentScoreList.forEach(System.out::println);
}
复制代码

其中queryStudentScore方法是一个连表操作，BizStudentMapper.xml配置如下：

<resultMap id="QueryStudentScoreMap" type="org.balloon.model.StudentScore">
    <id column="stu_code" jdbcType="INTEGER" property="stuCode"/>
    <association property="student" javaType="org.balloon.model.bean.Student">
        <constructor></constructor>
        <id column="stu_code" jdbcType="INTEGER" property="stuCode"/>
        <result column="stu_name" jdbcType="VARCHAR" property="stuName"/>
        <result column="stu_sex" jdbcType="VARCHAR" property="stuSex"/>
        <result column="stu_age" jdbcType="INTEGER" property="stuAge"/>
        <result column="stu_dept" jdbcType="VARCHAR" property="stuDept"/>
    </association>
    <collection property="courseList" ofType="org.balloon.model.bean.Course">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="cou_code" jdbcType="INTEGER" property="couCode"/>
        <result column="cou_name" jdbcType="VARCHAR" property="couName"/>
        <result column="cou_score" jdbcType="INTEGER" property="couScore"/>
        <result column="stu_code" jdbcType="INTEGER" property="stuCode"/>
    </collection>
</resultMap>

<select id="queryStudentScore" resultMap="QueryStudentScoreMap">
    SELECT
    t1.stu_code,
    t1.stu_name,
    t1.stu_sex,
    t1.stu_age,
    t1.stu_dept,
    t2.id,
    t2.cou_code,
    t2.cou_name,
    t2.cou_score
    FROM student AS t1
    INNER JOIN course AS t2
    ON t1.stu_code = t2.stu_code
    <where>
        <if test="query.stuAge != null">
            AND t1.stu_age >= #{query.stuAge}
        </if>
        <if test="query.stuDept != null">
            AND t1.stu_dept LIKE concat('%', #{query.stuDept}, '%')
        </if>
        <if test="query.couName != null">
            AND t2.cou_name LIKE concat('%', #{query.couName}, '%')
        </if>
    </where>
</select>
复制代码

痛点：当我们的student和course对象新增/修改/删除字段时，我们需要修改resultMap标签中引用的属性，且修改sql语句中的列名（这也是我放弃自动封装方法的原因，因为在项目初期，需求会经常变动，这种情况及其频繁）

优化：其实resultMap标签中引用的属性已经在studentMapper.xml和courseMapper.xml中定义过了，所以我们直接引用（对于sql中的列名，却没有好的解决方法，呜呜呜）。如下：

<resultMap id="QueryStudentScoreMap" type="org.balloon.model.StudentScore">
    <id column="stu_code" jdbcType="INTEGER" property="stuCode"/>
    <association property="student" resultMap="org.balloon.model.dao.StudentMapper.BaseResultMap"/>
    <collection property="courseList" ofType="org.balloon.model.bean.Course"
                resultMap="org.balloon.model.dao.CourseMapper.BaseResultMap"/>
</resultMap>
复制代码

关于resultMap的复用（列的复用感觉实用性不大）：
MapMyBatis 使用另一个 mapper 中的 resultMap 和 sql

总结：相比较单表查询的方法来说代码十分简洁，但是需要新增xml配置，emmm…

只含stu字段,分页

@Test
public void testB2() {
    StudentScoreQuery query = new StudentScoreQuery();
    query.setStuAge(18);
    query.setStuDept("CS");
    List<StudentScore> studentScoreList = studentInfoService.queryStudentScore(query);
    int pageNo = 1, pageSize = 1;
    int offSet = (pageNo - 1) * pageSize, lastIndex = Math.min(offSet + pageSize, studentScoreList.size());

    // 封装数据，将前端需要的字段进行返回，而不是将库中查出的全部字段返回
    List<StudentScoreResp> respList = Lists.newArrayList();
    studentScoreList.subList(offSet, lastIndex).forEach(studentScore -> {
        StudentScoreResp resp = new StudentScoreResp();
        BeanUtils.copyProperties(studentScore.getStudent(), resp);
        resp.setCourseList(studentScore.getCourseList().stream().map(course -> {
            StudentScoreResp.Course courseResp = new StudentScoreResp.Course();
            BeanUtils.copyProperties(course, courseResp);
            return courseResp;
        }).collect(Collectors.toList()));
        respList.add(resp);
    });
    
    System.out.println("Total number:" + studentScoreList.size());
    studentScoreList.forEach(System.out::println);
}
复制代码

总结：这里需要手动分页，不能像单表查询那样自动分页，影响不大（其实也可，但是要改变动态sql的写法，参见：一对多分页的SQL到底应该怎么写？）

全部字段,不分页

@Test
public void testB3() {
    StudentScoreQuery query = new StudentScoreQuery();
    query.setStuAge(18);
    query.setStuDept("CS");
    query.setCouName("英语");
    List<StudentScore> studentScoreList = studentInfoService.queryStudentScore(query);
    List<Integer> studentCodeList = studentScoreList.stream().map(StudentScore::getStuCode).collect(Collectors.toList());
    Map<Integer, List<Course>> courseMap = studentInfoService.queryCourse(studentCodeList, new CourseQuery())
            .stream().collect(Collectors.groupingBy(Course::getStuCode));

    // 封装数据，将前端需要的字段进行返回，而不是将库中查出的全部字段返回
    List<StudentScoreResp> respList = Lists.newArrayList();
    studentScoreList.forEach(studentScore -> {
        StudentScoreResp resp = new StudentScoreResp();
        BeanUtils.copyProperties(studentScore.getStudent(), resp);
        resp.setCourseList(courseMap.get(studentScore.getStuCode()).stream().map(course -> {
            StudentScoreResp.Course courseResp = new StudentScoreResp.Course();
            BeanUtils.copyProperties(course, courseResp);
            return courseResp;
        }).collect(Collectors.toList()));
        respList.add(resp);
    });

    studentScoreList.forEach(System.out::println);
}
复制代码

总结：对比testA3方法，我们发现其实都是先查出符合条件的同学，再查他的全部课程成绩，然后封装数据方式都是一模一样。

全部字段,分页

warning：这里的查询是含有数据结构成绩的同学才被返回，因此无法使用pageHelper或者连表+limit直接分页得到结果。

@Test
public void testB4() {
    StudentScoreQuery query = new StudentScoreQuery();
    query.setStuAge(18);
    query.setStuDept("CS");
    query.setCouName("英语");
    List<StudentScore> studentScoreList = studentInfoService.queryStudentScore(query);
    List<Integer> studentCodeList = studentScoreList.stream().map(StudentScore::getStuCode).collect(Collectors.toList());
    int pageNo = 1, pageSize = 1;
    int offset = (pageNo - 1) * pageSize, lastIndex = Math.min(offset + pageSize, studentScoreList.size());
    Map<Integer, List<Course>> courseMap = studentInfoService.queryCourse(studentCodeList.subList(offset, lastIndex), new CourseQuery())
            .stream().collect(Collectors.groupingBy(Course::getStuCode));

    // 封装数据，将前端需要的字段进行返回，而不是将库中查出的全部字段返回
    List<StudentScoreResp> respList = Lists.newArrayList();
    studentScoreList.subList(offset, lastIndex).forEach(studentScore -> {
        StudentScoreResp resp = new StudentScoreResp();
        BeanUtils.copyProperties(studentScore.getStudent(), resp);
        resp.setCourseList(courseMap.get(studentScore.getStuCode()).stream().map(course -> {
            StudentScoreResp.Course courseResp = new StudentScoreResp.Course();
            BeanUtils.copyProperties(course, courseResp);
            return courseResp;
        }).collect(Collectors.toList()));
        respList.add(resp);
    });

    System.out.println("Total number:" + studentScoreList.size());
    studentScoreList.forEach(System.out::println);
}
复制代码

总结：在testB3的基础上，进行了手动分页…

思考总结?

当我把单表查询（先student表再course表）和联合查询（利用mybatis的resultMap标签实现自动封装）的所有情况都实现之后，我的心里有了答案：

1. 单表查询和联合查询的实质区别就是：一对多的关系是否要自己实现（自己实现：两条sql；自动封装：一条sql）；
2. 相较于联合查询而言，单表查询更加灵活（查找时如果考虑排序?），而且后续可以把封装成一对多的关系代码提取成公共方法；
3. 在项目初期，需求经常会发生变动，涉及到数据库增/删/改字段时，联合查询就要手动修改resultMap标签中引用的属性，且修改sql语句中的列名（就这一条，就快恶心死我了…）。

???‍♀️? 我选择单表查询来实现一对多！

番外篇

如果你对我的观点不同意，请狠狠的点它：Mybatis 高级结果映射 ResultMap Association Collection，你将mybatis的上乘功法！