Java学习：使用MyBatis Plus的分页插件和QueryWrapper结合自定义mapper xml实现多表关联查询

Vo：

/**
 * 用来返回给前端展示列表的数据实体
 */
@Data
public class CourseVo implements Serializable {

    private static final long serialVersionUID = 1L;
    private String id;
    private String title;
    private String subjectParentTitle;
    private String subjectTitle;
    private String teacherName;
    private Integer lessonNum;
    private String price;
    private String cover;
    private Long buyCount;
    private Long viewCount;
    private String status;
    private String gmtCreate;
}

/**
 * 专门用来接受课程列表查询参数的实体
 */
@Data
public class CourseQueryVo implements Serializable {

    private static final long serialVersionUID = 1L;

    private String title;
    private String teacherId;
    private String subjectParentId;
    private String subjectId;
}

Controller：

 public R index(
            @ApiParam(value = "当前页码", required = true)
            @PathVariable Long page,

            @ApiParam(value = "每页记录数", required = true)
            @PathVariable Long limit,

            @ApiParam(value = "查询对象")
                    CourseQueryVo courseQueryVo){

        IPage<CourseVo> pageModel = courseService.selectPage(page, limit, courseQueryVo);
        List<CourseVo> records = pageModel.getRecords();
        long total = pageModel.getTotal();
        return  R.ok().data("total", total).data("rows", records);
    }

Service：

IPage<CourseVo> selectPage(Long page, Long limit, CourseQueryVo courseQueryVo);

 public IPage<CourseVo> selectPage(Long page, Long limit, CourseQueryVo courseQueryVo) {
        QueryWrapper<CourseVo> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("c.gmt_create");

        String title = courseQueryVo.getTitle();
        String teacherId = courseQueryVo.getTeacherId();
        String subjectParentId = courseQueryVo.getSubjectParentId();
        String subjectId = courseQueryVo.getSubjectId();

        if (!StringUtils.isEmpty(title)) {
            queryWrapper.like("c.title", title);
        }

        if (!StringUtils.isEmpty(teacherId) ) {
            queryWrapper.eq("c.teacher_id", teacherId);
        }

        if (!StringUtils.isEmpty(subjectParentId)) {
            queryWrapper.eq("c.subject_parent_id", subjectParentId);
        }

        if (!StringUtils.isEmpty(subjectId)) {
            queryWrapper.eq("c.subject_id", subjectId);
        }

        Page<CourseVo> pageParam = new Page<>(page, limit);
        //放入分页参数和查询条件参数，mp会自动组装
        List<CourseVo> records = baseMapper.selectPageByCourseQueryVo(pageParam, queryWrapper);
        pageParam.setRecords(records);
        return pageParam;
    }

Mapper：

List<CourseVo> selectPageByCourseQueryVo(//mp会自动组装分页参数
                                             Page<CourseVo> pageParam,
                                             //mp会自动组装queryWrapper：
                                             //@Param(Constants.WRAPPER) 和 xml文件中的 ${ew.customSqlSegment} 对应
                                             @Param(Constants.WRAPPER) QueryWrapper<CourseVo> queryWrapper);

Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.guli.service.edu.mapper.CourseMapper">
    <sql id="columns">
     c.id,
     c.title,
     c.lesson_num AS lessonNum,
     CONVERT(c.price, DECIMAL(8,2)) AS price,
     c.cover,
     c.buy_count AS buyCount,
     c.view_count AS viewCount,
     c.status,
     c.gmt_create AS gmtCreate,
     t.name AS teacherName,
     s1.title AS subjectParentTitle,
     s2.title AS subjectTitle
    </sql>

    <sql id="tables">
        edu_course c
        LEFT JOIN edu_teacher t ON c.teacher_id = t.id
        LEFT JOIN edu_subject s1 ON c.subject_parent_id = s1.id
        LEFT JOIN edu_subject s2 ON c.subject_id = s2.id
    </sql>

    <select id="selectPageByCourseQueryVo" resultType="com.atguigu.guli.service.edu.entity.vo.CourseVo">
        SELECT
        <include refid="columns" />
        FROM
        <include refid="tables" />
        ${ew.customSqlSegment}
    </select>
</mapper>

重点：
MybatisPlus会将查询参数构成的条件和分页的page及limit自动组装到Sql中
以后进行多表联合查询时，可以使用以下方法来进行数据的查找与筛选
自动组装需要搭配：

 @Param(Constants.WRAPPER) 放置于Mapper层接口的查询参数前，详细见上面代码例子

 ${ew.customSqlSegment} 将此行代码放置于Mapper文件的语句中，MybatisPlus将会为我们自动将 @Param(Constants.WRAPPER) 注解后的查询参数自动组装到Sql中