【数据篇】SpringBoot 整合 MyBatis-Plus 实现分页查询
站长
· 阅读数 46
写在最前
本文在【数据篇】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. 测试接口
执行 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. 测试接口
执行 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
}
转载自:https://juejin.cn/post/7232193628566028349