Back to the beginning ~ hahhahaha.. (To Brother Bin and Chi Man 👍🏻)

Throw out problem

In our work, we often encounter one-to-many table lookup requirements, such as: query a student’s test score (student table: student NUMBER, name, gender, age, school curriculum schedule: course code, course name, test score, student number)

We want to encapsulate the one-to-many relationship between student and course into the following object (used by the back end to return data from the front end: representing a one-to-many relationship and returning some fields as required) :

@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;

        privateInteger couScore; }}Copy the code

There are two common processing operations:

  1. Two single table queries (firststudentThe table againcourseTable)
  2. A federated query (leveragemybatistheresultMapLabel automatic packaging)

We will discuss these two types of queries today.

Single table query vs automatic encapsulation

For both single-table queries and auto-encapsulation, we discuss two conditions:

  1. Check whether the conditions are the samestudentFields andcourseField;
  2. Whether query results need to be paginated.

Thus, for any query, there are four cases to consider: stU field only, no paging, STU field only, paging, all fields, no paging, all fields, and paging.

Is dubious Contains only STU fields, not paginated Contains only STU fields, paginated All fields, not paginated All fields, paginated
Single table query
Automatic packaging

(This is a table that I wanted to list and compare the differences in detail, but when I implemented all the ideas, it suddenly didn’t feel necessary.)

Single table query:

Contains only STU fields, not paginated

@Test
public void testA1(a) {
    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));

    // Encapsulate data, return fields needed by the front end, instead of returning all fields found in the library (can be extracted as a public method)
    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);
}
Copy the code

Conclusion: Check the stuAge and stuDept fields for the studentList. Check the courseList field for the courseMap field. Check the courseMap field for the respList field.

Contains only STU fields, paginated

Paging query, front end to back end pass pageNo and pageSize, back end not only return the result set, at the same time return the totalSize of the query conditions for front end paging display data!

@Test
public void testA2(a) {
    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));

    // Encapsulate data, return fields needed by the front end, instead of returning all fields found in the library (can be extracted as a public method)
    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("The Total number." + studentPage.getTotal());
    respList.forEach(System.out::println);
}
Copy the code

In the queryStudentPage method we use PageHelper plugin to achieve a single table automatic paging function, and return data in addition to the result set totalSize, very good!

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

Summary: The pageHelper plugin is very convenient to implement automatic pagination of single table student.

All fields, not paginated

@Test
public void testA3(a) {
    StudentQuery studentQuery = new StudentQuery();
    studentQuery.setStuAge(18);
    studentQuery.setStuDept("CS");
    List<Student> studentList = studentInfoService.queryStudent(studentQuery);
    CourseQuery courseQuery = new CourseQuery();
    courseQuery.setCouName("Data structure");
    List<Integer> studentCodeList = studentList.stream().map(Student::getStuCode).collect(Collectors.toList());
    // Age >=18 school CS students may not have data structure course scores, so we need to check the data structure of the students first, and then check all their course scores
    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));

    // Encapsulate data, return fields needed by the front end, instead of returning all fields found in the library (can be extracted as a public method)
    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);
                returncourseResp; }).collect(Collectors.toList())); respList.add(resp); }}); respList.forEach(System.out::println); }Copy the code

Summary: Our goal is to find all grades of students whose age >=18 college is CS and contains data structure grades, somewhat circumvented 🐶 (but very suitable for business scenario). You need to look up the course table twice: once for students with data structure scores, and again for all of their scores.

All fields, paginated

Warning: This query will only be returned if the result of the query contains the result of the data structure. Therefore, you cannot use pageHelper or page_limit to get the result.

@Test
public void testA4(a) {
    StudentQuery studentQuery = new StudentQuery();
    studentQuery.setStuAge(18);
    List<Student> studentList = studentInfoService.queryStudent(studentQuery);
    CourseQuery courseQuery = new CourseQuery();
    courseQuery.setCouName("Data");
    List<Integer> studentCodeList = studentList.stream().map(Student::getStuCode).collect(Collectors.toList());
    // Age >=18 school CS students may not have data structure course scores, so we need to check the data structure of the students first, and then check all their course scores
    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));

    // Encapsulate data, return fields needed by the front end, instead of returning all fields found in the library (can be extracted as a public method)
    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);
                returncourseResp; }).collect(Collectors.toList())); respList.add(resp); }}); System.out.println("Total number:" + filterStudentCodeList.size());
    respList.forEach(System.out::println);
}
Copy the code

Conclusion: the implementation of “all fields, paging” is based on testA3, the second time to check all the scores of eligible students in the course table, only check the data to be returned.

Automatic packaging

In the face of high wind ~ thought is excellent method 😌

At the beginning of the project, we needed to find out one-to-many relationship from the database. At that time, WE checked the code written by many colleagues, and found that everyone did not use the resultMap label of Mybatis to directly connect the table operation to find one-to-many relationship, thinking about life…

Contains only STU fields, not paginated

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

    // Encapsulate the data and return the fields needed by the front end, instead of returning all the fields found in the library
    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);
}
Copy the code

QueryStudentScore is a table connection operation. Bizstudentmapper. XML is configured as follows:

<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>
Copy the code

Pain point: When we add/modify/delete fields to our Student and course objects, we need to change the attributes referenced in the resultMap tag and change the column names in the SQL statement (this is why I abandoned the automatic encapsulation method, because it happens very frequently in the early days of the project when requirements change frequently).

Optimisations: The attributes referenced in the resultMap tag are already defined in studentmapper. XML and coursemapper. XML, so we reference them directly. As follows:

<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>
Copy the code

About the reuse of resultMap (the reuse of columns is not practical) : MapMyBatis uses resultMap and SQL in another Mapper

Summary: Compared to the single-table query method of the code is very simple, but the need to add XML configuration, EMMM…

Contains only STU fields, paginated

@Test
public void testB2(a) {
    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());

    // Encapsulate the data and return the fields needed by the front end, instead of returning all the fields found in the library
    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);
}
Copy the code

Summary: Manual pagination is required, not automatic pagination like single-table query. The impact is not significant.

All fields, not paginated

@Test
public void testB3(a) {
    StudentScoreQuery query = new StudentScoreQuery();
    query.setStuAge(18);
    query.setStuDept("CS");
    query.setCouName("English");
    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));

    // Encapsulate the data and return the fields needed by the front end, instead of returning all the fields found in the library
    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);
}
Copy the code

Conclusion: Compared with the testA3 method, we found that in fact, the way of data encapsulation is exactly the same, which is to find out the eligible students first, then check all their course scores.

All fields, paginated

Warning: This query will only be returned if the result of the query contains the result of the data structure. Therefore, you cannot use pageHelper or page_limit to get the result.

@Test
public void testB4(a) {
    StudentScoreQuery query = new StudentScoreQuery();
    query.setStuAge(18);
    query.setStuDept("CS");
    query.setCouName("English");
    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));

    // Encapsulate the data and return the fields needed by the front end, instead of returning all the fields found in the library
    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);
}
Copy the code

Summary: Based on testB3, manual pagination is performed…

Thinking summary 🤔

When I implement all the cases of single table query (student table then course table) and joint query (using Mybatis resultMap tag to achieve automatic encapsulation), I have the answer in my mind:

  1. The essential difference between a single table query and a federated query is whether the one-to-many relationship should be implemented by itself (self-implementation: two SQL; Automatic encapsulation: one SQL);
  2. Compared to federated queries, single-table queries are more flexible (if sorting is considered when looking up 🤔), and you can subsequently extract the relational code encapsulated as one-to-many into a common method;
  3. Early in the project, requirements often change, and joint queries need to be manually modified when adding/deleting/changing fields to the databaseresultMapAttributes referenced in the tag and modifiedsqlThe column name in the statement (just this one, it’s disgusting to me…) .

🙋🙋🏻♀️ 🙋 I choose a single table query to implement one-to-many!

Set pieces

If you do not agree with my opinion, please severely click it: Mybatis Advanced result mapping ResultMap Association Collection, you will Mybatis excellent work method!