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中
一般,配置分页拦截器
@Configuration public class MybatisPlusConfig{ @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { // 1.创建MybatisPlusInterceptor拦截器 MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 2.创建分页拦截器 PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(); paginationInnerInterceptor.setOptimizeJoin(true); paginationInnerInterceptor.setDbType(DbType.MYSQL); paginationInnerInterceptor.setOverflow(true); interceptor.addInnerInterceptor(paginationInnerInterceptor); OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor = new OptimisticLockerInnerInterceptor(); interceptor.addInnerInterceptor(optimisticLockerInnerInterceptor); return interceptor; } }
@Override public List<TestEntity> getTestPage() { IPage<TestEntity> ipage = new Page<>(1,5); List<TestEntity> testList = testMapper.selectPage(ipage,null).getRecords(); return testList; }
转 : https://blog.csdn.net/StartedatAOP/article/details/109645540