Mybatis + PageHelper分页查询1:N遇到的坑

现有实体表培训计划o_training_plan和课程表(o_course)，及其映射表o_plan_course

通过mybaits的collection实现一对多映射。直接贴代码

    <resultMap type="TrainingPlan" id="TrainingPlanMap">
        <id property="planId" column="plan_id" jdbcType="BIGINT"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="cover" column="cover" jdbcType="VARCHAR"/>
        <result property="category" column="category" jdbcType="BIGINT"/>
        <result property="intro" column="intro" jdbcType="VARCHAR"/>
        <result property="begin" column="begin" jdbcType="DATE"/>
        <result property="end" column="end" jdbcType="DATE"/>
        
        <collection property="courseList" javaType="java.util.ArrayList" ofType="Course"
                    column="plan_id" select="selectCourseListByPlanId">
        </collection>
    </resultMap>


    <select id="selectCourseListByPlanId" resultMap="CourseMap">
        select c.course_id, c.course_name, c.cover, c.course_type, c.course_category, c.intro 
        from <include refid="tableName" /> c inner join o_plan_course pc on c.course_id = pc.course_id
        where pc.plan_id = #{planId}
    </select>

    <resultMap type="Course" id="CourseMap">
        <id property="courseId" column="course_id" jdbcType="BIGINT"/>
        <result property="courseName" column="course_name" jdbcType="VARCHAR"/>
        <result property="cover" column="cover" jdbcType="VARCHAR"/>
        <result property="courseType" column="course_type" jdbcType="VARCHAR"/>
        <result property="courseCategory" column="course_category" jdbcType="BIGINT"/>
        <result property="intro" column="intro" jdbcType="VARCHAR"/>
    </resultMap>

注意：这种写法会造成'1+N'问题，大数据集不推荐使用

还有个注意点，service层返回list如果要进行vo转换，一定要保留PageInfo信息，不然就会导致总条数一直等当前页条数

修复前代码,  pageNum传啥，分页结果total就显示多少，明显不对

PageHelper.startPage(pageNum, pageSize, orderBy)；
List<TrainingPlan> tpList = trainingPlanMapper.selectTrainingPlanList(trainingPlan);
        if(StringUtils.isEmpty(tpList)){
            return Collections.EMPTY_LIST;
        }
//转VO
List<TrainingPlanVo> voList = tpList.stream().map(TrainingPlanVo::new).collect(Collectors.toList());
Result.success(voList);

修复后

PageHelper.startPage(pageNum, pageSize, orderBy)；
List<TrainingPlan> tpList = trainingPlanMapper.selectTrainingPlanList(trainingPlan);
        if(StringUtils.isEmpty(tpList)){
            return Collections.EMPTY_LIST;
        }
//转VO
List<TrainingPlanVo> voList = tpList.stream().map(TrainingPlanVo::new).collect(Collectors.toList());
PageInfo<TrainingPlan> oriPageInfo = new PageInfo(tpList);
PageInfo<TrainingPlanVo > pageInfo = new PageInfo(voList);
BeanUtils.copyBeanProp(pageInfo, oriPageInfo);
Result.success(pageInfo.getList());

pagehelper已经提示不支持上面这种情况的查询了。Mybatis-PageHelper/Important.md at master · pagehelper/Mybatis-PageHelper · GitHub