🍀 前言
博客地址:
👋 简介
本章节详细介绍如何通过Mybatis-Plus分页查询数据库中的数据。
本章节不需要前置准备,继续使用之前的测试类,数据库表进行操作。
📖 正文
1 前置准备
1.1 添加测试数据
角色表中当前数据太少,我们添加几条数据来
@Test
public void addRoles() {
for (int i = 1; i <= 20; i++) {
Role role = Role
.builder()
.roleName("游客" + i + "号")
.roleCode("tourist" + i)
.build();
roleMapper.insert(role);
}
}
1.2 分页插件配置
在项目中创建一个config
包,存放配置类,并在该包下创建一个MybatisPlusConfig
配置类
package com.power.mpdemo.config;
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 power
* @time 2023/12/21 11:04
* @Description
*/
@Configuration
public class MybatisPlusConfig {
/**
* 分页插件
*
* @return
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
}
2 Mapper接口
// 分页查询,page 用于设置需要查询的页数,以及每页展示数据量,wrapper 用于组装查询条件
IPage<T> selectPage(IPage<T> page, Wrapper<T> queryWrapper);
// 同上,区别是用 map 来接受查询的数据
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, Wrapper<T> queryWrapper);
参数介绍:
IPage<T> page
:分页查询条件(可以为 RowBounds.DEFAULT)
2.1 selectPage
查询第1页,每页5条数据
@Test
public void selectByPage() {
LambdaQueryWrapper<Role> wrapper = Wrappers.<Role>lambdaQuery()
.like(Role::getRoleName, "游客");
// 查询第1页,每页5条数据
IPage<Role> page = new Page<>(1, 5);
IPage<Role> selectPage = roleMapper.selectPage(page, wrapper);
System.out.println(selectPage);
System.out.println("总数:" + selectPage.getTotal());
System.out.println("总页数:" + selectPage.getPages());
System.out.println("每页数量:" + selectPage.getSize());
System.out.println("当前页:" + selectPage.getCurrent());
System.out.println("数据:" + selectPage.getRecords());
}
// com.baomidou.mybatisplus.extension.plugins.pagination.Page@45843650
// 总数:20
// 总页数:4
// 每页数量:5
// 当前页:1
// 数据:[
// Role(id=5, roleName=游客1号, roleCode=tourist1, description=null, createTime=2023-12-22T19:23, updateTime=2023-12-22T19:23, isDeleted=0),
// Role(id=6, roleName=游客2号, roleCode=tourist2, description=null, createTime=2023-12-22T19:23, updateTime=2023-12-22T19:23, isDeleted=0),
// Role(id=7, roleName=游客3号, roleCode=tourist3, description=null, createTime=2023-12-22T19:23, updateTime=2023-12-22T19:23, isDeleted=0),
// Role(id=8, roleName=游客4号, roleCode=tourist4, description=null, createTime=2023-12-22T19:23, updateTime=2023-12-22T19:23, isDeleted=0),
// Role(id=9, roleName=游客5号, roleCode=tourist5, description=null, createTime=2023-12-22T19:23, updateTime=2023-12-22T19:23, isDeleted=0)
// ]
实际执行的SQL
SELECT COUNT(*) AS total FROM tb_role WHERE (role_name LIKE '%游客%')
SELECT
id,role_name,role_code,description,create_time,update_time,is_deleted
FROM
tb_role
WHERE (role_name LIKE '%游客%')
LIMIT 10
2.2 selectMapsPage
和selectPage
的区别在于map
查询第2页,每页5条数据
@Test
public void selectByPage() {
LambdaQueryWrapper<Role> wrapper = Wrappers.<Role>lambdaQuery()
.select(Role::getId,Role::getRoleName,Role::getRoleCode)
.like(Role::getRoleName, "游客");
// 查询第2页,每页5条数据
IPage<Map<String,Object>> page = new Page<>(2, 5);
IPage<Map<String, Object>> mapPage = roleMapper.selectMapsPage(page, wrapper);
System.out.println(mapPage);
System.out.println("总数:" + mapPage.getTotal());
System.out.println("总页数:" + mapPage.getPages());
System.out.println("每页数量:" + mapPage.getSize());
System.out.println("当前页:" + mapPage.getCurrent());
System.out.println("数据:" + mapPage.getRecords());
}
// com.baomidou.mybatisplus.extension.plugins.pagination.Page@4998e74b
// 总数:20
// 总页数:4
// 每页数量:5
// 当前页:2
// 数据:[
// {role_name=游客6号, role_code=tourist6, id=10},
// {role_name=游客7号, role_code=tourist7, id=11},
// {role_name=游客8号, role_code=tourist8, id=12},
// {role_name=游客9号, role_code=tourist9, id=13},
// {role_name=游客10号, role_code=tourist10, id=14}
// ]
实际执行的SQL
SELECT COUNT(*) AS total FROM tb_role WHERE (role_name LIKE '%游客%')
SELECT id,role_name,role_code FROM tb_role WHERE (role_name LIKE '%游客%') LIMIT 5,5
3 Service接口
// 无条件分页查询
IPage<T> page(IPage<T> page);
// 条件分页查询
IPage<T> page(IPage<T> page, Wrapper<T> queryWrapper);
// 无条件分页查询
IPage<Map<String, Object>> pageMaps(IPage<T> page);
// 条件分页查询
IPage<Map<String, Object>> pageMaps(IPage<T> page, Wrapper<T> queryWrapper);
3.1 page
如果传入第二个参数条件构造器,那么结果受到条件影响,如果不带参数,那么对所有数据信息分页查询,本列中没有传入第二个参数,所以,总条数是数据库所有数据的总数
@Test
public void selectByPage() {
// LambdaQueryWrapper<Role> wrapper = Wrappers.<Role>lambdaQuery()
// .select(Role::getId,Role::getRoleName,Role::getRoleCode)
// .like(Role::getRoleName, "游客");
// 查询第1页,每页5条数据
IPage<Role> page = new Page<>(1, 5);
IPage<Role> roleIPage = roleService.page(page);
System.out.println(roleIPage);
System.out.println("总数:" + roleIPage.getTotal());
System.out.println("总页数:" + roleIPage.getPages());
System.out.println("每页数量:" + roleIPage.getSize());
System.out.println("当前页:" + roleIPage.getCurrent());
System.out.println("数据:" + roleIPage.getRecords());
}
// com.baomidou.mybatisplus.extension.plugins.pagination.Page@18d11527
// 总数:24
// 总页数:5
// 每页数量:5
// 当前页:1
// 数据:[
// Role(id=1, roleName=超级管理员, roleCode=ADMINISTROTER, description=超级管理员, createTime=2023-12-22T13:51:13, updateTime=2023-12-22T13:51:13, isDeleted=0),
// Role(id=2, roleName=测试1号, roleCode=test01, description=null, createTime=2023-12-22T13:51:13, updateTime=2023-12-22T13:51:13, isDeleted=0),
// Role(id=3, roleName=测试2号, roleCode=test02, description=null, createTime=2023-12-22T13:51:13, updateTime=2023-12-22T13:51:13, isDeleted=0),
// Role(id=4, roleName=测试3号, roleCode=test03, description=null, createTime=2023-12-22T13:51:13, updateTime=2023-12-22T13:51:13, isDeleted=0),
// Role(id=5, roleName=游客1号, roleCode=tourist1, description=null, createTime=2023-12-22T19:23, updateTime=2023-12-22T19:23, isDeleted=0)
// ]
实际执行的SQL
SELECT COUNT(*) AS total FROM tb_role
SELECT
id,role_name,role_code,description,create_time,update_time,is_deleted
FROM
tb_role
LIMIT 5
3.2 pageMaps
参数与page
方法一致
@Test
public void selectByPage() {
LambdaQueryWrapper<Role> wrapper = Wrappers.<Role>lambdaQuery()
.select(Role::getId,Role::getRoleName,Role::getRoleCode)
.like(Role::getRoleName, "游客");
// 查询第2页,每页5条数据
IPage<Map<String,Object>> page = new Page<>(2, 5);
IPage<Map<String, Object>> mapPage = roleService.pageMaps(page, wrapper);
System.out.println(mapPage);
System.out.println("总数:" + mapPage.getTotal());
System.out.println("总页数:" + mapPage.getPages());
System.out.println("每页数量:" + mapPage.getSize());
System.out.println("当前页:" + mapPage.getCurrent());
System.out.println("数据:" + mapPage.getRecords());
}
// com.baomidou.mybatisplus.extension.plugins.pagination.Page@2b08772d
// 总数:20
// 总页数:4
// 每页数量:5
// 当前页:2
// 数据:[
// {role_name=游客6号, role_code=tourist6, id=10},
// {role_name=游客7号, role_code=tourist7, id=11},
// {role_name=游客8号, role_code=tourist8, id=12},
// {role_name=游客9号, role_code=tourist9, id=13},
// {role_name=游客10号, role_code=tourist10, id=14}
// ]
实际执行的SQL
SELECT COUNT(*) AS total FROM tb_role WHERE (role_name LIKE '%游客%')
SELECT id,role_name,role_code FROM tb_role WHERE (role_name LIKE '%游客%') LIMIT 5,5