【数据篇】SpringBoot 整合 MyBatis-Plus 实现分页查询

写在最前

本文在【数据篇】SpringBoot 整合 MyBatis-Plus 增强 MyBatis ，基友搭配，效率翻倍基础上使用 MyBatis-Plus 实现分页查询。

构建查询数据

-- 创建岗位信息表
CREATE TABLE `sys_post` (
  `post_id` bigint NOT NULL AUTO_INCREMENT COMMENT '岗位ID',
  `post_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '岗位编码',
  `post_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '岗位名称',
  `post_sort` int NOT NULL COMMENT '岗位排序',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '是否删除  -1：已删除  0：正常',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '更新人',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注信息',
  PRIMARY KEY (`post_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='岗位信息表';
 
-- 向员工表插入数据
INSERT INTO `sys_post` VALUES (1, 'user', '员工', 2, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');
INSERT INTO `sys_post` VALUES (2, 'cto', 'cto', 0, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '秃头大佬');
INSERT INTO `sys_post` VALUES (3, 'user', '董事长', -1, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '资本家');
INSERT INTO `sys_post` VALUES (4, 'user1', '员工1', 3, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');
INSERT INTO `sys_post` VALUES (5, 'user2', '员工2', 4, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');
INSERT INTO `sys_post` VALUES (6, 'user3', '员工3', 5, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');
INSERT INTO `sys_post` VALUES (7, 'user4', '员工4', 6, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');
INSERT INTO `sys_post` VALUES (8, 'user5', '员工5', 7, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');
INSERT INTO `sys_post` VALUES (9, 'user6', '员工6', 8, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');
INSERT INTO `sys_post` VALUES (10, 'user7', '员工7', 9, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');
INSERT INTO `sys_post` VALUES (11, 'user8', '员工8', 10, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人');

Mybatis-Plus Page

该类继承了 IPage 类，实现了 简单分页模型 如果你要实现自己的分页模型可以继承 Page 类或者实现 IPage 类

属性名	类型	默认值	描述
records	List	emptyList	查询数据列表
total	Long	0	查询列表总记录数
size	Long	10	每页显示条数，默认 10
current	Long	1	当前页
orders	List	emptyList	排序字段信息，允许前端传入的时候，注意 SQL 注入问题，可以使用 SqlInjectionUtils.check(...) 检查文本
optimizeCountSql	boolean	true	自动优化 COUNT SQL 如果遇到 jSqlParser 无法解析情况，设置该参数为 false
optimizeJoinOfCountSql	boolean	true	自动优化 COUNT SQL 是否把 join 查询部分移除
searchCount	boolean	true	是否进行 count 查询，如果指向查询到列表不要查询总记录数，设置该参数为 false
maxLimit	Long		单页分页条数限制
countId	String		xml 自定义 count 查询的 statementId

基于 Wrapper 分页查询

Demo 地址：mingyue-springboot-mybatis-plus

1. 添加岗位实体

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * 岗位管理
 *
 * @author Strive
 * @date 2022-03-15 17:18:40
 */
@Data
@TableName("sys_post")
@EqualsAndHashCode(callSuper = true)
@Schema(description = "岗位信息表")
public class MingYuePost extends BaseEntity {

	private static final long serialVersionUID = -8744622014102311894L;

	/**
	 * 岗位ID
	 */
	@TableId(type = IdType.ASSIGN_ID)
	@Schema(description = "岗位ID")
	private Long postId;

	/**
	 * 岗位编码
	 */
	@Schema(description = "岗位编码")
	private String postCode;

	/**
	 * 岗位名称
	 */
	@Schema(description = "岗位名称")
	private String postName;

	/**
	 * 岗位排序
	 */
	@Schema(description = "岗位排序")
	private Integer postSort;

	/**
	 * 是否删除 -1：已删除 0：正常
	 */
	@Schema(description = "是否删除  -1：已删除  0：正常")
	private String delFlag;

	/**
	 * 备注信息
	 */
	@Schema(description = "备注信息")
	private String remark;

}

2. 添加岗位 Mapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.csp.mingyue.mybatisPlus.model.MingYuePost;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author Strive
 */
@Mapper
public interface MingYuePostMapper extends BaseMapper<MingYuePost> {
}

3. 添加岗位 Service

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.csp.mingyue.mybatisPlus.mapper.MingYuePostMapper;
import com.csp.mingyue.mybatisPlus.model.MingYuePost;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

/**
 * @author Strive
 * @date 2023/5/12 10:22
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class MingYuePostService extends ServiceImpl<MingYuePostMapper, MingYuePost> {
}

4. 提供分页查询接口

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.csp.mingyue.mybatisPlus.model.MingYuePost;
import com.csp.mingyue.mybatisPlus.service.MingYuePostService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @author Strive
 * @date 2023/5/12 10:21
 */
@Api(tags = "岗位模块")
@RestController
@RequiredArgsConstructor
@RequestMapping("/post")
public class MingYuePostController {

    private final MingYuePostService mingYuePostService;

    @ApiOperation("根据用户ID查询用户信息")
    @GetMapping("/page")
    public ResponseEntity<IPage<MingYuePost>> page(Page<MingYuePost> page) {
        return ResponseEntity.ok(mingYuePostService.page(page));
    }

}

5. 新增分页拦截器

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author Strive
 * @date 2023/5/12 15:51
 */
@Configuration
public class MyBatisPlusConfig {
    /**
     * 分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

6. 测试接口

http://127.0.0.1:8080/post/page?size=5&current=1&orders%5B0%5D.column=post_sort&orders%5B0%5D.asc=true

执行 SQL 如下：

SELECT post_id, post_code, post_name, post_sort, del_flag, remark, create_by, create_time, update_by, update_time FROM sys_post ORDER BY post_sort ASC LIMIT 5

返回数据如下：

{
    "records": [
        {
            "createBy": "admin",
            "createTime": "2023-03-16T13:14:20",
            "updateBy": "admin",
            "updateTime": "2023-06-26T13:14:20",
            "postId": 3,
            "postCode": "boss",
            "postName": "董事长",
            "postSort": -1,
            "delFlag": "0",
            "remark": "资本家"
        },
        {
            "createBy": "admin",
            "createTime": "2023-03-16T13:14:20",
            "updateBy": "admin",
            "updateTime": "2023-06-26T13:14:20",
            "postId": 2,
            "postCode": "cto",
            "postName": "cto",
            "postSort": 0,
            "delFlag": "0",
            "remark": "秃头大佬"
        },
        {
            "createBy": "admin",
            "createTime": "2023-03-16T13:14:20",
            "updateBy": "admin",
            "updateTime": "2023-06-26T13:14:20",
            "postId": 1,
            "postCode": "user",
            "postName": "员工",
            "postSort": 2,
            "delFlag": "0",
            "remark": "打工人"
        },
        ...
    ],
    "total": 11,
    "size": 5,
    "current": 1,
    "orders": [
        {
            "column": "post_sort",
            "asc": true
        }
    ],
    "optimizeCountSql": true,
    "searchCount": true,
    "countId": null,
    "maxLimit": null,
    "pages": 3
}

基于 SQL 分页查询

1. 添加分页查询 Mapper

/**
  * 分页查询岗位信息
  * @param page 分页参数
  * @param mingYuePost
  * @return
  */
IPage<MingYuePost> selectPageSql(@Param("page") Page page,@Param("query") MingYuePost mingYuePost);

2. 编写 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.csp.mingyue.mybatisPlus.mapper.MingYuePostMapper">
    <resultMap id="mingYuePostMap" type="com.csp.mingyue.mybatisPlus.model.MingYuePost">
        <id property="postId" column="post_id"/>
        <result property="postCode" column="post_code"/>
        <result property="postName" column="post_name"/>
        <result property="postSort" column="postSort"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <select id="selectPageSql" resultMap="mingYuePostMap">
        SELECT p.post_id,
               p.post_name,
               p.post_code,
               p.post_sort as postSort,
               p.del_flag,
               p.create_time,
               p.update_time,
               p.update_by,
               p.create_by,
               p.remark
        FROM sys_post p
        <where>
            p.del_flag = '0'
            <if test="query.postName != null and query.postName != ''">
                <bind name="postNameLike" value="'%' + query.postName + '%'" />
                and p.post_name LIKE  #{postNameLike}
            </if>
        </where>
        <if test="null != page.orders">
            <include refid="dynamicOrder"></include>
        </if>
    </select>

    <!-- 动态排序 -->
    <sql id="dynamicOrder">
        ORDER BY
        <choose>
            <when test="page.orders != null and page.orders.size > 0">
                <foreach collection="page.orders" item="order" separator=",">
                    ${order.column}
                    <choose>
                        <when test="order.asc == true">
                            asc
                        </when>
                        <otherwise>
                            desc
                        </otherwise>
                    </choose>
                </foreach>
            </when>
        </choose>
    </sql>
</mapper>

3. 编写 Service

public IPage<MingYuePost> pageSql(Page page, MingYuePost mingYuePost) {
        return mingYuePostMapper.selectPageSql(page, mingYuePost);
}

4. 编写接口

@ApiOperation("分页查询岗位信息(SQl)")
@GetMapping("/pageSql")
public ResponseEntity<IPage<MingYuePost>> pageSql(Page page, MingYuePost mingYuePost) {
  return ResponseEntity.ok(mingYuePostService.pageSql(page, mingYuePost));
}

5. 测试接口

http://127.0.0.1:8080/post/pageSql?postName=%E5%91%98%E5%B7%A5&orders%5B0%5D.column=postSort&orders%5B0%5D.asc=false&size=5&current=1

执行 SQL 如下：

SELECT p.post_id, p.post_name, p.post_code, p.post_sort AS postSort, p.del_flag, p.create_time, p.update_time, p.update_by, p.create_by, p.remark FROM sys_post p WHERE p.del_flag = '0' AND p.post_name LIKE '%员工%' ORDER BY postSort DESC, postSort DESC LIMIT 5

返回数据如下：

{
    "records": [
        {
            "createBy": "admin",
            "createTime": "2023-03-16T13:14:20",
            "updateBy": "admin",
            "updateTime": "2023-06-26T13:14:20",
            "postId": 11,
            "postCode": "user8",
            "postName": "员工8",
            "postSort": 10,
            "delFlag": "0",
            "remark": "打工人"
        },
        {
            "createBy": "admin",
            "createTime": "2023-03-16T13:14:20",
            "updateBy": "admin",
            "updateTime": "2023-06-26T13:14:20",
            "postId": 10,
            "postCode": "user7",
            "postName": "员工7",
            "postSort": 9,
            "delFlag": "0",
            "remark": "打工人"
        },
        {
            "createBy": "admin",
            "createTime": "2023-03-16T13:14:20",
            "updateBy": "admin",
            "updateTime": "2023-06-26T13:14:20",
            "postId": 9,
            "postCode": "user6",
            "postName": "员工6",
            "postSort": 8,
            "delFlag": "0",
            "remark": "打工人"
        },
      	...
    ],
    "total": 0,
    "size": 10,
    "current": 1,
    "orders": [
        {
            "column": "postSort",
            "asc": false
        }
    ],
    "optimizeCountSql": true,
    "searchCount": true,
    "countId": null,
    "maxLimit": null,
    "pages": 0
}