mybatis-plus整合SpringBoot之——分页插件使用

Mybatis-plus整合SpringBoot之——分页插件使用

前言

mybatis-plus中的分页插件学习记录：

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、使用步骤

1.引入依赖

这里使用3.1.1.tmp版本

<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.3.1.tmp</version>
</dependency>

2.配置分页插件拦截器

/**
 * MyBatisPlus的分页配置
 * @Author: yyh
 * Date: 2022/5/10
 * Time: 21:15
 */
@Configuration
public class MyBatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

3.controller层、service层、mapper层

controller

package com.yyh.server.controller;

/**
 *
 * @author yyh
 * @since 2022-04-30
 */
@RestController
@RequestMapping("/employee/basic")
public class EmployeeController {

    @Autowired
    private IEmployeeService employeeService;

    /**
     * 分页获取员工：
     *  前台可以根据Employee实体类中的属性传入搜索参数，并且还可以传入查询的页码以及每页的数量，
     *  默认是第1页，且每页是10条数据
     * @param currentPage：当前页码
     * @param pageSize：每页数据条数
     * @param employee：用于接收搜索参数的实体类
     * @param beginDateScope：员工的入职日期范围，这里专门使用一个LocalDate[]数组接收时间范围
     * @return
     */
    @ApiModelProperty(value = "获取员工，分页查询")
    @GetMapping("/")
    public RespPageBean getEmployees(@RequestParam(defaultValue = "1") Integer currentPage,
                                     @RequestParam(defaultValue = "10") Integer pageSize,
                                     
                                     /*@RequestBody*/ Employee employee,
                                     LocalDate[] beginDateScope
                                     /*HttpServletRequest request*/) {
        System.out.println("============");
        return employeeService.getEmployeesByPage(currentPage, pageSize, employee, beginDateScope);
    }
}

[注意]：不能使用@RequestBody修饰Employee employee、LocalDate[] beginDateScope。因为此处是get类型请求，get是没有请求体的。若为get类型请求的请求参数，加上@RequestBody注解，在用swagger测试的时候，该参数Employee employee会是以表单的形式提交，而get没请求体，所以不符合http协议规范。若使用postman测试的时候，get类型的接口，压根是不能选择传body的。

service

package com.yyh.server.service.impl;

/**
 *
 * @author yyh
 * @since 2022-04-30
 */
@Service
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService {

    @Autowired
    private EmployeeMapper employeeMapper;

    @Override
    public RespPageBean getEmployeesByPage(Integer currentPage, Integer pageSize, Employee employee, LocalDate[] beginDateScope) {

        //开启分页
        Page<Employee> page = new Page<>(currentPage, pageSize);
        //调用mapper层查询方法
        IPage<Employee> employeesByPage = employeeMapper.getEmployeesByPage(page, employee, beginDateScope);
        long total = employeesByPage.getTotal();
        List<Employee> records = employeesByPage.getRecords();
        RespPageBean respPageBean = new RespPageBean((int) total, records);
        return respPageBean;
    }
}

mapper

package com.yyh.server.mapper;

/**
 *
 * @author yyh
 * @since 2022-04-30
 */
public interface EmployeeMapper extends BaseMapper<Employee> {


    /**
     * 分页查询员工：
     *  这里使用的是mybatis-plus的分页工具，所以在mapper层的分页查询方法中
     *  传入了分页对象page，mybatis-plus会自动利用aop技术在sql语句中添加上分页查询的参数
     * @param page
     * @param employee
     * @param beginDateScope
     * @return
     */
    IPage<Employee> getEmployeesByPage(Page<Employee> page,
                                       @Param("employee") Employee employee,
                                       @Param("beginDateScope") LocalDate[] beginDateScope);
}

SQL语句

<?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.yyh.server.mapper.EmployeeMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.yyh.server.pojo.Employee">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="gender" property="gender" />
        <result column="birthday" property="birthday" />
        <result column="idCard" property="idCard" />
        <result column="wedlock" property="wedlock" />
        <result column="nationId" property="nationId" />
        <result column="nativePlace" property="nativePlace" />
        <result column="politicId" property="politicId" />
        <result column="email" property="email" />
        <result column="phone" property="phone" />
        <result column="address" property="address" />
        <result column="departmentId" property="departmentId" />
        <result column="jobLevelId" property="jobLevelId" />
        <result column="posId" property="posId" />
        <result column="engageForm" property="engageForm" />
        <result column="tiptopDegree" property="tiptopDegree" />
        <result column="specialty" property="specialty" />
        <result column="school" property="school" />
        <result column="beginDate" property="beginDate" />
        <result column="workState" property="workState" />
        <result column="workID" property="workID" />
        <result column="contractTerm" property="contractTerm" />
        <result column="conversionTime" property="conversionTime" />
        <result column="notWorkDate" property="notWorkDate" />
        <result column="beginContract" property="beginContract" />
        <result column="endContract" property="endContract" />
        <result column="workAge" property="workAge" />
        <result column="salaryId" property="salaryId" />
    </resultMap>

    <resultMap id="EmployeeInfoMap" extends="BaseResultMap" type="com.yyh.server.pojo.Employee">
        <association property="nation" javaType="com.yyh.server.pojo.Nation">
            <id column="nid" property="id"/>
            <result column="nname" property="name"/>
        </association>
        <association property="politicsStatus" javaType="com.yyh.server.pojo.PoliticsStatus">
            <id column="psid" property="id"/>
            <result column="psname" property="name"/>
        </association>
        <association property="department" javaType="com.yyh.server.pojo.Department">
            <id column="did" property="id"/>
            <result column="dname" property="name"/>
        </association>
        <association property="joblevel" javaType="com.yyh.server.pojo.Joblevel">
            <id column="jid" property="id"/>
            <result column="jname" property="name"/>
        </association>
        <association property="position" javaType="com.yyh.server.pojo.Position">
            <id column="pid" property="id"/>
            <result column="pname" property="name"/>
        </association>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, gender, birthday, idCard, wedlock, nationId, nativePlace, politicId, email, phone, address, departmentId, jobLevelId, posId, engageForm, tiptopDegree, specialty, school, beginDate, workState, workID, contractTerm, conversionTime, notWorkDate, beginContract, endContract, workAge, salaryId
    </sql>
    
    <select id="getEmployeesByPage" resultMap="EmployeeInfoMap">
        SELECT
            e.*,
            n.id nid, n.`name` nname,
            d.id did, d.`name` dname,
            ps.id psid, ps.`name` psname,
            p.id pid, p.`name` pname,
            j.id jid, j.`name` jname
        FROM
            t_employee e,
            t_nation n,
            t_department d,
            t_politics_status ps,
            t_position p,
            t_joblevel j
        WHERE
            e.nationId = n.id
        AND
            e.departmentId = d.id
        AND
            e.politicId = ps.id
        AND
            e.posId = p.id
        AND
            e.jobLevelId = j.id
        <if test="employee.name != null and employee.name != ''">
            AND e.`name` LIKE CONCAT('%',#{employee.name},'%')
        </if>
        <if test="employee.politicId != null">
            AND ps.id = #{employee.politicId}
        </if>
        <if test="employee.nationId != null">
            AND n.id = #{employee.nationId}
        </if>
        <if test="employee.posId != null">
            AND p.id = #{employee.posId}
        </if>
        <if test="employee.jobLevelId != null">
            AND j.id = #{employee.jobLevelId}
        </if>
        <if test="employee.departmentId != null">
            AND d.id = #{employee.departmentId}
        </if>
        <if test="beginDateScope != null">
            AND e.beginDate BETWEEN  #{beginDateScope[0]} AND #{beginDateScope[1]}
        </if>
        ORDER BY e.id
    </select>

</mapper>

总结

使用mybatis-plus分页插件的时候，执行的是真正的物理分页。
首先会查询记录条数：

Preparing: SELECT COUNT(1) FROM t_employee e, t_nation n, t_department d, t_politics_status ps, t_position p, t_joblevel j WHERE e.nationId = n.id AND e.departmentId = d.id AND e.politicId = ps.id AND e.posId = p.id AND e.jobLevelId = j.id

再进行物理分页，直接在SQL语句中拼接limit：

SELECT e.*, n.id nid, n.`name` nname, d.id did, d.`name` dname, ps.id psid, ps.`name` psname, p.id pid, p.`name` pname, j.id jid, j.`name` jname FROM t_employee e, t_nation n, t_department d, t_politics_status ps, t_position p, t_joblevel j WHERE e.nationId = n.id AND e.departmentId = d.id AND e.politicId = ps.id AND e.posId = p.id AND e.jobLevelId = j.id ORDER BY e.id LIMIT ?,?

若直接使用BaseMapper接口中的selectPage()方法完成分页，实质上是先获得全部数据，缓存之后，再进行分页，但如果数据量过多时，查询全部数据是不可取的。
两种区别可以通过控制台打印的SQL语句区别。