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

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

1.1、所需依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

<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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@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

课程实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
@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

教师实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@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,证明为二级分类。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@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类用于定义需要展示在课程详情页面的属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@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

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

1
2
3
4
5
6
7
8
@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:由查询条件封装成的查询对象
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@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层
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
@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)**注解

1
2
3
4
5
@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}**

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?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、结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
{
"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语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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层

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

2、Service实现类

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

3、Mapper.xml文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<!-- 使用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实体类

1
2
3
4
5
6
7
8
9
@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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@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

1
2
3
4
5
6
7
8
@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

1
2
3
4
5
6
7
8
9
10
11
12
@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、控制器层

1
2
3
4
5
6
7
8
9
10
11
@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

1
2
3
4
5
6
7
8
9
10
@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列表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@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;
}