在线教育项目总结（一）-使用MP的QueryWrapper进行自定义sql与提升嵌套查询效率。

一、使用条件构造器自定义SQL

在在线教育项目的学习过程中，遇到一个需要展示课程详细信息的需求，这个课程信息页面需要展示课程title、课程一级类别、二级类别、讲师姓名、课时数、课程价格、课程封面、课程购买量、浏览量、课程状态（已发布、未发布）和课程创建时间。

1.1、所需依赖

   <properties>
       <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
       <encoding>UTF-8</encoding>
       <java.version>1.8</java.version>
       <maven.compiler.source>1.8</maven.compiler.source>
       <maven.compiler.target>1.8</maven.compiler.target>
       <mybatis-plus.version>3.3.1</mybatis-plus.version>
       <swagger.version>2.9.2</swagger.version>
       <mysql.version>8.0.19</mysql.version>
   </properties>
<dependencies>
	<dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-web</artifactId>
       </dependency>
       <!--mybatis-plus-->
       <dependency>
           <groupId>com.baomidou</groupId>
           <artifactId>mybatis-plus-boot-starter</artifactId>
       </dependency>
       <dependency>
           <groupId>com.baomidou</groupId>
           <artifactId>mybatis-plus-generator</artifactId>
       </dependency>
       <!--mysql-->
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
       </dependency>
       <!--swagger-->
       <dependency>
           <groupId>io.springfox</groupId>
           <artifactId>springfox-swagger2</artifactId>
           <exclusions>
               <exclusion>
                   <groupId>io.swagger</groupId>
                   <artifactId>swagger-annotations</artifactId>
               </exclusion>
               <exclusion>
                   <groupId>io.swagger</groupId>
                   <artifactId>swagger-models</artifactId>
               </exclusion>
           </exclusions>
       </dependency>
       <dependency>
           <groupId>io.springfox</groupId>
           <artifactId>springfox-swagger-ui</artifactId>
       </dependency>
       <dependency>
           <groupId>com.github.xiaoymin</groupId>
           <artifactId>swagger-bootstrap-ui</artifactId>
           <version>1.9.6</version>
       </dependency>
       <dependency>
           <groupId>io.swagger</groupId>
           <artifactId>swagger-annotations</artifactId>
           <version>1.5.21</version>
       </dependency>
       <dependency>
           <groupId>io.swagger</groupId>
           <artifactId>swagger-models</artifactId>
           <version>1.5.21</version>
       </dependency>
       <!--lombok用来简化实体类：需要安装lombok插件-->
       <dependency>
           <groupId>org.projectlombok</groupId>
           <artifactId>lombok</artifactId>
       </dependency>
       <!-- org.apache.commons.lang3.StringUtils -->
       <dependency>
           <groupId>org.apache.commons</groupId>
           <artifactId>commons-lang3</artifactId>
           <version>3.7</version>
       </dependency>
</dependencies>

1.2、实体类

该业务所涉及的实体类如下所示

1、BaseEntity

这个实体类定义了实体类的共同属性

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class BaseEntity implements Serializable {

    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "讲师ID")
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private String id;

    @ApiModelProperty(value = "创建时间")
    @TableField(fill = FieldFill.INSERT)
    private Date gmtCreate;

    @ApiModelProperty(value = "更新时间")
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date gmtModified;
}

2、Course

课程实体类

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("edu_course")
@ApiModel(value="Course对象", description="课程")
public class Course extends BaseEntity {
    private static final long serialVersionUID=1L;
    /***
     * 课程未发布
     */
    public static final String COURSE_DRAFT = "Draft";
    /***
     * 课程已发布
     */
    public static final String COURSE_NORMAL = "Normal";
    
    @ApiModelProperty(value = "课程讲师ID")
    private String teacherId;

    @ApiModelProperty(value = "课程专业ID")
    private String subjectId;

    @ApiModelProperty(value = "课程专业父级ID")
    private String subjectParentId;

    @ApiModelProperty(value = "课程标题")
    private String title;

    @ApiModelProperty(value = "课程销售价格，设置为0则可免费观看")
    private BigDecimal price;

    @ApiModelProperty(value = "总课时")
    private Integer lessonNum;

    @ApiModelProperty(value = "课程封面图片路径")
    private String cover;

    @ApiModelProperty(value = "销售数量")
    private Long buyCount;

    @ApiModelProperty(value = "浏览数量")
    private Long viewCount;

    @ApiModelProperty(value = "乐观锁")
    private Long version;

    @ApiModelProperty(value = "课程状态 Draft未发布  Normal已发布")
    private String status;
}

3、Teacher

教师实体类

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("edu_teacher")
@ApiModel(value="Teacher对象", description="讲师")
public class Teacher extends BaseEntity {

    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "讲师姓名")
    private String name;

    @ApiModelProperty(value = "讲师简介")
    private String intro;

    @ApiModelProperty(value = "讲师资历,一句话说明讲师")
    private String career;

    @ApiModelProperty(value = "头衔 1高级讲师 2首席讲师")
    private Integer level;

    @ApiModelProperty(value = "讲师头像")
    private String avatar;

    @ApiModelProperty(value = "排序")
    private Integer sort;

    @ApiModelProperty(value = "入驻时间")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
    private Date joinDate;
    
    @ApiModelProperty(value = "逻辑删除 1（true）已删除， 0（false）未删除")
    @TableLogic
    private Integer isDeleted;
}

4、Subject

课程分类实体类

一级分类和二级分类共用一个实体类，如果分类对象的parentId为0，证明其是一级分类，如果不为0，证明为二级分类。

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("edu_subject")
@ApiModel(value="Subject对象", description="课程科目")
public class Subject extends BaseEntity {

    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "类别名称")
    private String title;

    @ApiModelProperty(value = "父ID")
    private String parentId;

    @ApiModelProperty(value = "排序字段")
    private Integer sort;
}

5、CourseVo

这个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;
}

6、CourseQueryVo

该业务还需要用到分页查询与条件查询，所以引入一个用于构造条件查询的查询实体类

@Data
public class CourseQueryVo implements Serializable {
    private static final long serialVersionUID = 1L;
    private String title;
    private String teacherId;
    private String subjectParentId;
    private String subjectId;
}

1.3、Controller层

定义一个CourseListPage接口，需要传入三个参数：

• page：当前页码
• limit：每页记录数
• courseQueryVo：由查询条件封装成的查询对象

@Slf4j
@CrossOrigin
@RestController
@Api(tags = "课程管理控制器")
@RequestMapping("/admin/edu/course")
public class CourseController {
    @Autowired
    private CourseService courseService;
    
    @ApiOperation("课程分页列表")
    @GetMapping("list/{page}/{limit}")
    public R courseListPage(@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);
    }
}

1.4、Service实现类

使用QueryWrapper组装sql的步骤如下：

• 分别取出查询对象中的查询属性并判断是否为空
• 若不为空，则使用条件构造器根据属性类型构造查询条件
• 在使用条件构造器构造查询条件时需要使用表别名.属性名的形式构造条件
• 将组装好的条件构造器传入Mapper层

@Service
public class CourseServiceImpl extends ServiceImpl<CourseMapper, Course> implements CourseService {
    @Autowired
    private VideoMapper videoMapper;
    @Autowired
    private ChapterMapper chapterMapper;
    @Autowired
    private CommentMapper commentMapper;
    @Autowired
    private CourseCollectMapper courseCollectMapper;

    @Autowired
    private CourseMapper courseMapper;
    
    @Override
    public IPage<CourseVo> selectPage(Long page, Long limit, CourseQueryVo courseQueryVo) {
        //组装查询条件
        QueryWrapper<CourseVo> courseVoQueryWrapper = new QueryWrapper<>();
        courseVoQueryWrapper.orderByDesc("c.gmt_create");
        //四个查询条件如下
        String title = courseQueryVo.getTitle();
        String teacherId = courseQueryVo.getTeacherId();
        String subjectParentId = courseQueryVo.getSubjectParentId();
        String subjectId = courseQueryVo.getSubjectId();

        if (StringUtils.isNotBlank(title)) {
            courseVoQueryWrapper.like("c.title", title);
        }
        if (StringUtils.isNotBlank(teacherId)) {
            courseVoQueryWrapper.eq("c.teacher_id", teacherId);
        }
        if (StringUtils.isNotBlank(subjectParentId)) {
            courseVoQueryWrapper.eq("c.subject_parent_id", subjectParentId);
        }
        if (StringUtils.isNotBlank(subjectId)) {
            courseVoQueryWrapper.eq("c.subject_id", subjectId);
        }
        //组装分页
        Page<CourseVo> pageParam = new Page<>(page, limit);

        //执行查询
        List<CourseVo> courseVoList = courseMapper.selectPageByQueryVo(pageParam, courseVoQueryWrapper);
        pageParam.setRecords(courseVoList);
        return pageParam;
    }

}

1.5、Mapper层

1、Mapper接口

对于Service层中传入的条件构造器，需要使Mybatis-plus能够识别，此时我们需要在Mapper层中的条件构造器对象前添加**@Param(Constants.WRAPPER)**注解

@Repository
public interface CourseMapper extends BaseMapper<Course> {
    List<CourseVo> selectPageByQueryVo(Page<CourseVo> pageParam,
                                       @Param(Constants.WRAPPER) QueryWrapper<CourseVo> courseVoQueryWrapper);
}

2、Mapper.xml文件

除去Mapper接口添加的**@Param(Constants.WRAPPER)注解外，我们还需要在自定义的sql语句后添加${ew.customSqlSegment}**

<?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.hzx.grain.service.edu.mapper.CourseMapper">
    <select id="selectPageByQueryVo" resultType="com.hzx.grain.service.edu.entity.vo.CourseVo">
        SELECT
            <include refid="courseVoColumns"/>
        FROM
            <include refid="tables"/>
        ${ew.customSqlSegment}
    </select>
    <!-- 抽取sql片段 -->
    <sql id="courseVoColumns">
           c.id,
           c.title,
           c.lesson_num AS lessonNum,
           c.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片段：与course表连接的其余表 -->
    <sql id="tables">
            guli_edu.edu_course c
        	LEFT JOIN guli_edu.edu_teacher t ON c.teacher_id = t.id
	        LEFT JOIN guli_edu.edu_subject s1 ON c.subject_parent_id = s1.id
	        LEFT JOIN guli_edu.edu_subject s2 ON c.subject_id = s2.id
    </sql>
</mapper>

3、注意事项

需要mybatis-plus版本 >= 3.0.7

1.6、测试

打开Swagger进行测试

1、结果

{
  "success": true,
  "code": 20000,
  "message": "成功",
  "data": {
    "total": 3,
    "rows": [
      {
        "id": "1353316219423391746",
        "title": "Spring Boot框架入门教程",
        "subjectParentTitle": "后端开发",
        "subjectTitle": "Java",
        "teacherName": "刘德华",
        "lessonNum": 10,
        "price": "0.00",
        "cover": "https://edu-system-1010.oss-cn-shenzhen.aliyuncs.com/cover/2021/01/24/2afcb3fd-da73-40d1-ba41-4d6cf74c7e7a.jpg",
        "buyCount": 8,
        "viewCount": 0,
        "status": "Normal",
        "gmtCreate": "2021-01-24 20:18:25"
      },
      {
        "id": "1353313325668200450",
        "title": "Mybatis-Plus框架入门教程",
        "subjectParentTitle": "后端开发",
        "subjectTitle": "Java",
        "teacherName": "刘德华",
        "lessonNum": 10,
        "price": "1.00",
        "cover": "https://edu-system-1010.oss-cn-shenzhen.aliyuncs.com/cover/2021/01/24/804b29e5-4ca7-4819-9bac-eb34f65068ca.jpg",
        "buyCount": 9,
        "viewCount": 2,
        "status": "Normal",
        "gmtCreate": "2021-01-24 20:06:55"
      },
      {
        "id": "1353310049325309953",
        "title": "java--从入门到入土",
        "subjectParentTitle": "后端开发",
        "subjectTitle": "Java",
        "teacherName": "刘德华",
        "lessonNum": 10,
        "price": "6.00",
        "cover": "https://edu-system-1010.oss-cn-shenzhen.aliyuncs.com/cover/2021/01/24/d7c25aaa-5c09-4b46-a56d-5000f9265bc7.jpg",
        "buyCount": 10,
        "viewCount": 5,
        "status": "Normal",
        "gmtCreate": "2021-01-24 19:53:54"
      }
    ]
  }
}

2、执行的sql语句

使用Idea的Mybatis插件查看执行的sql语句

SELECT 
	c.id, 
	c.title, 
	c.lesson_num AS lessonNum, 
	c.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
FROM guli_edu.edu_course c
	LEFT JOIN guli_edu.edu_teacher t ON c.teacher_id = t.id
	LEFT JOIN guli_edu.edu_subject s1 ON c.subject_parent_id = s1.id
	LEFT JOIN guli_edu.edu_subject s2 ON c.subject_id = s2.id
WHERE c.title LIKE '%入门%'
	AND c.teacher_id = '1'
	AND c.subject_parent_id = '1352924754100617218'
	AND c.subject_id = '1352924754167726082'
ORDER BY c.gmt_create DESC
LIMIT 0, 3;

二、总结嵌套查询

2.1、使用子查询

1、Controller层

@GetMapping("nested")
@ApiOperation("嵌套分类数据列表")
public R nestedList() {
    //返回分类VO列表
    List<SubjectVo> subjectVoList = subjectService.nestedList();
    return R.ok().data("items",subjectVoList);
}

2、Service实现类

@Override
public List<SubjectVo> nestedList() {
    return subjectMapper.selectNestedListByParentId("0");
}

3、Mapper.xml文件

<!-- 使用ResultMap进行映射 -->
<resultMap id="subjectVoMap" type="com.hzx.grain.service.edu.entity.vo.SubjectVo">
    <!-- 将表中字段与类中属性一一映射 -->
    <id property="id" column="id" />
    <result property="title" column="title" />
    <result property="sort" column="sort" />
    <!-- 这里根据原来的方法进行嵌套查询 -->
    <collection property="children"
                column="id"
                select="selectNestedListByParentId"
                ofType="com.hzx.grain.service.edu.entity.vo.SubjectVo"/>
</resultMap>

<!-- 嵌套查询一级分类与二级分类并组装为SubjectVo列表的sql语句 -->
<select id="selectNestedListByParentId" resultMap="subjectVoMap">
    SELECT
     `id`,
     `sort`,
     `title`
    FROM
     guli_edu.edu_subject
    WHERE
     `parent_id` = #{parentId}
</select>

4、SubjectVo实体类

@Data
public class SubjectVo implements Serializable {
    private static final long serialVersionUID = 1L;

    private String id;
    private String title;
    private Integer sort;
    private List<SubjectVo> children = new ArrayList<>();
}

5、总结

这种使用子查询的嵌套查询实现简单，但查询效率会随着数据量增大而降低。

2.2、三层嵌套查询中使用冗余字段提高查询效率

该业务需求是：根据课程Id查询课程详情信息，包括课程信息、嵌套章节课时列表

即一个课程下有多个章节，而一个章节下又有多个小节

1、CourseVo

@Data
public class WebCourseVo implements Serializable {

    private static final long serialVersionUID = 1L;
    private String id;
    private String title;
    private BigDecimal price;
    private Integer lessonNum;
    private String cover;
    private Long buyCount;
    private Long viewCount;
    private String description;
    private String teacherId;
    private String teacherName;
    private String intro;
    private String avatar;
    private String subjectLevelOneId;
    private String subjectLevelOne;
    private String subjectLevelTwoId;
    private String subjectLevelTwo;
}

2、ChapterVo

@Data
public class ChapterVo implements Serializable {
    private static final long serialVersionUID = 1L;
    private String id;
    private String title;
    private Integer sort;
    private List<VideoVo> children = new ArrayList<>();
}

3、VideoVo

@Data
public class VideoVo implements Serializable {

    private static final long serialVersionUID = 1L;

    private String id;
    private String title;
    private Boolean free;
    private Integer sort;

    private String videoSourceId;
}

4、控制器层

@GetMapping("get/{courseId}")
@ApiOperation("根据courseId查询课程详细信息")
public R getById(
        @ApiParam(value = "课程id",required = true)
        @PathVariable String courseId) {
    WebCourseVo courseVo = courseService.selectWebCourseVoById(courseId);

    //查询当前课程的嵌套章节和课时信息
    List<ChapterVo> chapterVoList = chapterService.nestedList(courseId);
    return R.ok().data("course",courseVo).data("chapterVoList",chapterVoList);
}

5、service层

CourseServiceImpl

@Transactional(rollbackFor = Exception.class)
@Override
public WebCourseVo selectWebCourseVoById(String id) {
    //更新课程浏览数
    Course course = baseMapper.selectById(id);
    course.setViewCount(course.getViewCount() + 1);
    baseMapper.updateById(course);
    //获取课程信息
    return baseMapper.selectWebCourseVoById(id);
}

ChapterServiceImpl

由于前面使用子查询导致查询速率低下，所以在这里使用video表冗余的CourseId字段来提高查询速率，步骤如下

• 通过Chapter类的courseId获取该课程下的所有章节对象
• 通过Video类冗余的courseId获取该课程下的所有小节对象
• 循环遍历该课程下的章节对象列表，通过Chapter对象的id和Video对象的chapterId字段来建立联系，组装Chapter对象的Video列表

@Override
public List<ChapterVo> nestedList(String courseId) {
    //1 通过courseId获取章节列表信息：List<Chapter>
    QueryWrapper<Chapter> chapterQueryWrapper = new QueryWrapper<>();
    chapterQueryWrapper.eq("course_id",courseId);
    List<Chapter> chapterList = chapterMapper.selectList(chapterQueryWrapper);
    //2 通过edu_video表中冗余的course_id获取属于这个课程的所有Video列表：List<Video>
    QueryWrapper<Video> videoQueryWrapper = new QueryWrapper<>();
    videoQueryWrapper.eq("course_id",courseId);
    List<Video> videoList = videoMapper.selectList(videoQueryWrapper);
    //3 先获取章节信息ChapterVo列表，再获取VideoVo列表
    List<ChapterVo> chapterVoList = new ArrayList<>();
    chapterList.forEach(chapter -> {
        ChapterVo chapterVo = new ChapterVo();
        BeanUtils.copyProperties(chapter,chapterVo);
        chapterVoList.add(chapterVo);
        List<VideoVo> videoVoList = new ArrayList<>();
        //4 循环组装章节信息列表
        videoList.forEach(video -> {
            //如果当前chapter的id的值等于video的chapterId值
            //证明当前video对象属于该Chapter对象
            if(StringUtils.equals(chapter.getId(),video.getChapterId())) {
                VideoVo videoVo = new VideoVo();
                BeanUtils.copyProperties(video,videoVo);
                videoVoList.add(videoVo);
            }
        });
        chapterVo.setChildren(videoVoList);
    });
    return chapterVoList;
}