MyBatis中 collection 的两种使用方法常见问题

现在有两张表：

1.classinfo-班级信息表

CREATE TABLE `classinfo` (
  `class_id` varchar(32) NOT NULL COMMENT '班级编号',
  `class_name` varchar(32) DEFAULT NULL COMMENT '班级名称',
  `CREATED_BY` varchar(32) DEFAULT NULL COMMENT '创建人',
  `CREATED_TIME` datetime DEFAULT NULL COMMENT '创建时间',
  `UPDATED_BY` varchar(32) DEFAULT NULL COMMENT '更新人',
  `UPDATED_TIME` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='班级信息表';

2.studentinfo-学生表

CREATE TABLE `studentinfo` (
  `student_id` varchar(32) NOT NULL COMMENT '学号',
  `student_name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `class_id` varchar(32) DEFAULT NULL COMMENT '班级编号',
  `CREATED_BY` varchar(32) DEFAULT NULL COMMENT '创建人',
  `CREATED_TIME` datetime DEFAULT NULL COMMENT '创建时间',
  `UPDATED_BY` varchar(32) DEFAULT NULL COMMENT '更新人',
  `UPDATED_TIME` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`student_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='学生表';

现在要查班级列表，接口要求如下：

控制层：

@PostMapping(value = "YR100201")
    @ApiOperation(value = "班级列表查询")
    public Result getClassList(@ApiParam(value = "班级编号") @RequestParam String[] classId,
                               @ApiParam(value = "班级名称") @RequestParam String className
            ){
        return classinfoService.getClassList(classId, className);
    }

业务层：

@Override
    public Result getClassList(String[] classId, String className) {
        List<Classinfo> classinfos= classinfoMapper.getClassList(classId, className);
        return Result.success(classinfos);
    }

持久层：

ClassinfoMapper.java

List<Classinfo> getClassList(String[] classId, String className);

ClassinfoMapper.xml-方案一

<resultMap type="com.yurun.student_demo.entity.Classinfo" id="classinfo">
        <result column="classId" property="classId"/>
        <result column="className" property="className"/>
        <result column="createdBy" property="createdBy"/>
        <result column="createdTime" property="createdTime"/>
        <result column="updatedBy" property="updatedBy"/>
        <result column="updatedTime" property="updatedTime"/>
        <collection property="studentinfo" ofType="com.yurun.student_demo.entity.Studentinfo" >
            <result column="s_studentId" property="studentId"/>
            <result column="s_studentName" property="studentName"/>
            <result column="s_classId" property="classId"/>
            <result column="s_createdBy" property="createdBy"/>
            <result column="s_createdTime" property="createdTime"/>
            <result column="s_updatedBy" property="updatedBy"/>
            <result column="s_updatedTime" property="updatedTime"/>
        </collection>
    </resultMap>

<select id="getClassList" resultMap="classinfo">
        SELECT a.class_id AS classId, a.class_name AS className,  a.CREATED_BY AS createdBy,
        a.CREATED_TIME AS createdTime, a.UPDATED_BY AS updatedBy, a.UPDATED_TIME AS updatedTime,

        s.student_id AS s_studentId, s.student_name AS s_studentName,s.class_id AS s_classId,
        s.CREATED_BY AS s_createdBy,s.CREATED_TIME AS s_createdTime, s.UPDATED_BY AS s_updatedBy,
        s.UPDATED_TIME AS s_updatedTime
        FROM classinfo a
        LEFT JOIN  studentinfo s ON a.class_id = s.class_id
        <where>
            <if test="classId != null and classId.length != 0">
                a.class_id in
                <foreach collection="classId" item="id" index="index" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="className != null and !''.equals(className)">
                AND a.class_name = #{className}
            </if>
        </where>
        ORDER BY a.class_id ASC
    </select>

ClassinfoMapper.xml-方案二

<resultMap type="com.yurun.student_demo.entity.Classinfo" id="classinfo">
        <result column="classId" property="classId"/>
        <result column="className" property="className"/>
        <result column="createdBy" property="createdBy"/>
        <result column="createdTime" property="createdTime"/>
        <result column="updatedBy" property="updatedBy"/>
        <result column="updatedTime" property="updatedTime"/>
        <collection property="studentinfo" javaType="java.util.List"
            column="classId" select="com.yurun.student_demo.mapper.StudentinfoMapper.getStudentByClassId"
        >
        </collection>
    </resultMap>
    
    <select id="getClassList" resultMap="classinfo">
        SELECT a.class_id AS classId, a.class_name AS className,  a.CREATED_BY AS createdBy,
        a.CREATED_TIME AS createdTime, a.UPDATED_BY AS updatedBy, a.UPDATED_TIME AS updatedTime

        FROM classinfo a
        <where>
            <if test="classId != null and classId.length != 0">
                a.class_id in
                <foreach collection="classId" item="id" index="index" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="className != null and !''.equals(className)">
                AND a.class_name = #{className}
            </if>
        </where>
        ORDER BY a.class_id ASC
    </select>

StudentinfoMapper.java

    List<Studentinfo> getStudentByClassId(@Param("classId")String[] classId);

StudentinfoMapper.xml

    <select id="getStudentByClassId" resultType="com.yurun.student_demo.entity.Studentinfo">
        SELECT a.student_id AS studentId, a.student_name AS studentName,a.class_id AS classId, a.CREATED_BY AS createdBy,
               a.CREATED_TIME AS createdTime, a.UPDATED_BY AS updatedBy, a.UPDATED_TIME AS updatedTime
        FROM studentinfo a
        WHERE a.class_id = #{classId}
    </select>

---

分析：

1.效率问题

修改业务层暴力测试效率：

@Override
    public Result getClassList(String[] classId, String className) {
        long start = System.currentTimeMillis();
        List<Classinfo> classinfos = null;
        for (int i = 0; i < 1000; i++) {
            classinfos= classinfoMapper.getClassList(classId, className);
        }
        System.out.println("方案一耗时： = " + (System.currentTimeMillis()-start));
        return Result.success(classinfos);
    }

方案一测试结果（测试三次）：

第一次：

第二次：

第三次：

方案二测试结果（测试三次）：

第一次：

第二次：

第三次：

2.分页问题

修改业务层

@Override
    public Result getClassList(String[] classId, String className) {
        PageHelper.startPage(1 , 2);
        List<Classinfo> classinfos= classinfoMapper.getClassList(classId, className);
        return Result.success(classinfos);
    }

方案一测试结果（主表分页查询 不正确）：

方案二测试结果（主表分页查询 正确）：

总结

方案一需要执行至少三次sql语句，开启三次事务才能完成班级列表查询请求。

方案二需要执行一次sql语句，开启一次事务就能完成班级列表查询请求。

方案二比方案一的效率要 高，但是在使用的时候，方案一的代码可重用性要 高 。

使用PageHelper或者Mybatis-Plus的分页插件进行分页的时候，方案一主表分页 查询错误 ，方案二主表分页 查询正确。

---

欢迎关注公众号：c_302888524