MyBatis-Plus教程 - 9 分页查询

当数据库表中的数据很多的时候，查询数据的时候就需要分页。

我们一般在 SQL 中使用 LIMIT 关键字，来限制查询的结果，从而实现分页的功能。

在分页之前，现在数据库的表中插入一些数据：

INSERT INTO `tb_user`   
(`id`, `username`, `password`, `email`, `age`, `create_time`, `update_time`)  
VALUES   
(1001, 'user1', 'pwd12345', 'user1@foooor.com', 25, NOW(), NOW()),  
(1002, 'user2', 'pwd67890', 'user2@foooor.com', 30, NOW(), NOW()),  
(1003, 'user3', 'pwd112233', 'user3@foooor.com', 28, NOW(), NOW()),  
(1004, 'user4', 'pwd445566', 'user4@foooor.com', 35, NOW(), NOW()),  
(1005, 'user5', 'pwd778899', 'user5@foooor.com', 22, NOW(), NOW()),  
(1006, 'user6', 'pwd001122', 'user6@foooor.com', 29, NOW(), NOW()),  
(1007, 'user7', 'pwd334455', 'user7@foooor.com', 34, NOW(), NOW()),  
(1008, 'user8', 'pwd667788', 'user8@foooor.com', 27, NOW(), NOW()),  
(1009, 'user9', 'pwd990011', 'user9@foooor.com', 32, NOW(), NOW()),  
(1010, 'user10', 'pwd223344', 'user10@foooor.com', 24, NOW(), NOW()),  
(1011, 'user11', 'pwd556677', 'user11@foooor.com', 26, NOW(), NOW()),  
(1012, 'user12', 'pwd889900', 'user12@foooor.com', 31, NOW(), NOW()),  
(1013, 'user13', 'pwd11223344', 'user13@foooor.com', 23, NOW(), NOW()),  
(1014, 'user14', 'pwd55667788', 'user14@foooor.com', 36, NOW(), NOW()),  
(1015, 'user15', 'pwd99001122', 'user15@foooor.com', 21, NOW(), NOW()),  
(1016, 'user16', 'pwd33445566', 'user16@foooor.com', 30, NOW(), NOW()),  
(1017, 'user17', 'pwd77889900', 'user17@foooor.com', 25, NOW(), NOW()),  
(1018, 'user18', 'pwd11223355', 'user18@foooor.com', 29, NOW(), NOW()),  
(1019, 'user19', 'pwd55667799', 'user19@foooor.com', 34, NOW(), NOW()),  
(1020, 'user20', 'pwd99001133', 'user20@foooor.com', 28, NOW(), NOW()),  
(1021, 'user21', 'pwd22334466', 'user21@foooor.com', 32, NOW(), NOW()),  
(1022, 'user22', 'pwd66778811', 'user22@foooor.com', 26, NOW(), NOW()),  
(1023, 'user23', 'pwd00112244', 'user23@foooor.com', 31, NOW(), NOW()),  
(1024, 'user24', 'pwd44556677', 'user24@foooor.com', 24, NOW(), NOW()),  
(1025, 'user25', 'pwd88990011', 'user25@foooor.com', 27, NOW(), NOW()),  
(1026, 'user26', 'pwd33445588', 'user26@foooor.com', 35, NOW(), NOW()),  
(1027, 'user27', 'pwd77889922', 'user27@foooor.com', 23, NOW(), NOW()),  
(1028, 'user28', 'pwd11223366', 'user28@foooor.com', 30, NOW(), NOW()),  
(1029, 'user29', 'pwd55667700', 'user29@foooor.com', 25, NOW(), NOW()),  
(1030, 'user30', 'pwd99001144', 'user30@foooor.com', 29, NOW(), NOW()),  
(1031, 'user31', 'pwd22334477', 'user31@foooor.com', 33, NOW(), NOW()),  
(1032, 'user32', 'pwd66778833', 'user32@foooor.com', 26, NOW(), NOW()),  
(1033, 'user33', 'pwd00112255', 'user33@foooor.com', 31, NOW(), NOW()),  
(1034, 'user34', 'pwd44556688', 'user34@foooor.com', 28, NOW(), NOW()),  
(1035, 'user35', 'pwd88990022', 'user35@foooor.com', 32, NOW(), NOW()),  
(1036, 'user36', 'pwd33445599', 'user36@foooor.com', 24, NOW(), NOW()),  
(1037, 'user37', 'pwd77889933', 'user37@foooor.com', 27, NOW(), NOW()),  
(1038, 'user38', 'pwd11223377', 'user38@foooor.com', 35, NOW(), NOW()),  
(1039, 'user39', 'pwd55667711', 'user39@foooor.com', 23, NOW(), NOW()),  
(1040, 'user40', 'pwd99001155', 'user40@foooor.com', 26, NOW(), NOW()),  
(1041, 'user41', 'pwd22334488', 'user41@foooor.com', 30, NOW(), NOW()),  
(1042, 'user42', 'pwd66778844', 'user42@foooor.com', 25, NOW(), NOW()),  
(1043, 'user43', 'pwd00112266', 'user43@foooor.com', 29, NOW(), NOW()),  
(1044, 'user44', 'pwd44556699', 'user44@foooor.com', 34, NOW(), NOW()),  
(1045, 'user45', 'pwd88990033', 'user45@foooor.com', 28, NOW(), NOW());

9.1 简单分页

我们可以直接在 QueryWrapper 条件对象上使用 last() 方法，在查询语句的最后追加 LIMIT 限制信息。

举个栗子：

// 1.创建条件对象
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();

// 2.设置条件
queryWrapper.last("limit 0, 10");

// 3.查询
List<User> userList = userMapper.selectList(queryWrapper);
log.info("查询结果:{}", userList);

last() 方法的参数会拼接到 SQL 语句的最后，所以生成的 SQL 如下：

SELECT id,username,password,age,email,create_time,update_time FROM tb_user limit 0, 10

但是这种分页的方式太简陋了，没有封装分页的信息，例如总页数、当前第几页等。

内容未完......

查看完整内容 →