介绍
常规分页,我们可以在sql语句中,添加limit等,来实现分页查询,但是比较繁琐
下面演示使用PageHelper来实现分页查询,使用时代码量极少
PageHelper是Mybatis提供的分页插件，目前支持Oracle,Mysql,MariaDB,SQLite等数据库。
原理
Pagehelper内部实现了一个PageInterceptor拦截器
Mybatis会加载这个拦截器到拦截器链中
在我们使用过程中先使用PageHelper.startPage这样的语句
会在当前线程上下文中设置一个ThreadLocal变量
再利用PageInterceptor这个分页拦截器拦截
当查询执行时,会从ThreadLocal中拿到分页的信息,如果有分页信息,则会拼装分页SQL（limit语句等）进行分页查询
最后再把ThreadLocal中的东西清除掉
在mapper声明时,直接使用List类型即可
但实际最终返回的数据类型为com.github.pagehelper.Page,此类继承自ArrayList
所以在执行 PageInfo pageInfo = new PageInfo<>(list) 的时候,pageInfo变量才会自动填充跟分页相关的数据
new PageInfo<>(list)最终调用的构造函数代码
public PageInfo(List<T> list, int navigatePages) {
super(list);
this.isFirstPage = false;
this.isLastPage = false;
this.hasPreviousPage = false;
this.hasNextPage = false;
if (list instanceof Page) {
Page page = (Page)list;
this.pageNum = page.getPageNum();
this.pageSize = page.getPageSize();
this.pages = page.getPages();
this.size = page.size();
if (this.size == 0) {
this.startRow = 0L;
this.endRow = 0L;
} else {
this.startRow = page.getStartRow() + 1L;
this.endRow = this.startRow - 1L + (long)this.size;
}
} else if (list instanceof Collection) {
this.pageNum = 1;
this.pageSize = list.size();
this.pages = this.pageSize > 0 ? 1 : 0;
this.size = list.size();
this.startRow = 0L;
this.endRow = list.size() > 0 ? (long)(list.size() - 1) : 0L;
}
if (list instanceof Collection) {
this.calcByNavigatePages(navigatePages);
}
}
依赖
<!-- MyBatis Spring Boot：数据访问层MyBatis编程 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<!-- PageHelper Spring Boot：MyBatis分页 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.0</version>
</dependency>
如不需要连接数据库,只是单纯的声明或调用PageHelper内的信息(一般在微服务中会用到),则添加
<!-- PageHelper Spring Boot：MyBatis分页 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.3.0</version>
</dependency>
基本使用
准备数据库数据
表
CREATE TABLE `order_tbl` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '订单id',
`user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户id',
`commodity_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品编码,也可以是商品id',
`count` int NULL DEFAULT 0 COMMENT '购买这个商品的数量',
`money` int NULL DEFAULT 0 COMMENT '订单金额',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 27 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
插入测试数据
insert into `order_tbl`(`id`,`user_id`,`commodity_code`,`count`,`money`) values
(1,'UU100','PU201',10,200),
(2,'UU100','PU201',10,200),
(3,'UU100','PU201',10,200),
(4,'UU100','PU201',10,200);
application配置文件
因为在表字段中存在下划线(_),所以使用此配置开启Mybatis自动转驼峰命名
mybatis:
configuration:
map-underscore-to-camel-case: true
1.创建实体类
package cn.tedu.csmall.commons.pojo.order.model;
import lombok.Data;
import java.io.Serializable;
@Data
public class Order implements Serializable {
private Integer id;
private String userId;
private String commodityCode;
private Integer count;
private Integer money;
}
2.编写持久层
在持久层编写全查相关mapper,此处直接使用注解,未使用xml去编写,真实业务环境中推荐使用xml去编写
package cn.tedu.csmall.order.webapi.mapper;
import cn.tedu.csmall.commons.pojo.order.model.Order;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface OrderMapper {
@Select("select id, user_id, commodity_code, count, money from order_tbl")
List<Order> listOrder();
}
3.编写业务逻辑层
3.1编写业务接口
package cn.tedu.csmall.order.service;
import cn.tedu.csmall.commons.pojo.order.model.Order;
import com.github.pagehelper.PageInfo;
import java.util.List;
public interface IOrderService {
PageInfo<Order> listOrder(Integer pageNum, Integer pageSize);
}
3.2编写业务实现类
package cn.tedu.csmall.order.webapi.service.impl;
import cn.tedu.csmall.commons.pojo.order.model.Order;
import cn.tedu.csmall.order.service.IOrderService;
import cn.tedu.csmall.order.webapi.mapper.OrderMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
@Slf4j
public class OrderServiceImpl implements IOrderService {
//装配持久层操作对象
@Autowired
private OrderMapper orderMapper;
@Override
public PageInfo<Order> listOrder(Integer pageNum, Integer pageSize) {
//设置分页要求,pageNum为页码从1开始,pageSize为单页尺寸
PageHelper.startPage(pageNum, pageSize);
//设置完分页信息后,会自动在下一次sql查询中,添加分页信息
//原sql语句:
//select id, user_id, commodity_code, count, money from order_tbl
//处理后为:
//select id, user_id, commodity_code, count, money from order_tbl limit (pageNum-1)*pageSize, pageSize
//此时查询的结果,实际上是一个Page对象(继承了ArrayList),可以使用List去声明,在此对象中,保存了分页相关的其他信息
List<Order> list = orderMapper.listOrder();
//所以在此处使用new PageInfo<>(list)时,pageInfo变量才会自动填充跟分页相关的数据
PageInfo<Order> pageInfo = new PageInfo<>(list);
return pageInfo;
}
}
3.3编写控制器
package cn.tedu.csmall.order.webapi.controller;
import cn.tedu.csmall.commons.pojo.order.model.Order;
import cn.tedu.csmall.order.service.IOrderService;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/base/order")
public class OrderController {
@Autowired
private IOrderService orderService;
//实际业务中通常是使用统一响应的,此处仅为测试,就不再进行包装
@PostMapping("/get")
public PageInfo<Order> listOrder(Integer pageNum, Integer pageSize) {
PageInfo<Order> pageInfo = orderService.listOrder(pageNum, pageSize);
return pageInfo;
}
}
测试
Postman测试结果
响应内容
{
"total": 4,
"list": [
{
"id": 1,
"userId": "UU100",
"commodityCode": "PU201",
"count": 10,
"money": 200
},
{
"id": 2,
"userId": "UU100",
"commodityCode": "PU201",
"count": 10,
"money": 200
},
{
"id": 3,
"userId": "UU100",
"commodityCode": "PU201",
"count": 10,
"money": 200
}
],
"pageNum": 1,
"pageSize": 3,
"size": 3,
"startRow": 1,
"endRow": 3,
"pages": 2,
"prePage": 0,
"nextPage": 2,
"isFirstPage": true,
"isLastPage": false,
"hasPreviousPage": false,
"hasNextPage": true,
"navigatePages": 8,
"navigatepageNums": [
1,
2
],
"navigateFirstPage": 1,
"navigateLastPage": 2
}
运行过程
调用2次接口,pageSize统一使用3,pageNum分别使用1跟2
查询第1页数据
2022-08-29 16:23:11.882 DEBUG 35624 --- [io-20002-exec-1] c.t.c.o.w.m.OrderMapper.listOrder_COUNT : ==> Preparing: SELECT count(0) FROM order_tbl
2022-08-29 16:23:11.899 DEBUG 35624 --- [io-20002-exec-1] c.t.c.o.w.m.OrderMapper.listOrder_COUNT : ==> Parameters:
2022-08-29 16:23:11.916 DEBUG 35624 --- [io-20002-exec-1] c.t.c.o.w.m.OrderMapper.listOrder_COUNT : <== Total: 1
2022-08-29 16:23:11.919 DEBUG 35624 --- [io-20002-exec-1] c.t.c.o.w.mapper.OrderMapper.listOrder : ==> Preparing: select id, user_id, commodity_code, count, money from order_tbl LIMIT ?
2022-08-29 16:23:11.920 DEBUG 35624 --- [io-20002-exec-1] c.t.c.o.w.mapper.OrderMapper.listOrder : ==> Parameters: 3(Integer)
2022-08-29 16:23:11.921 DEBUG 35624 --- [io-20002-exec-1] c.t.c.o.w.mapper.OrderMapper.listOrder : <== Total: 3
SELECT count(0) FROM order_tbl
select id, user_id, commodity_code, count, money from order_tbl LIMIT 3
查询第二页数据
2022-08-29 16:25:16.157 DEBUG 35624 --- [io-20002-exec-3] c.t.c.o.w.m.OrderMapper.listOrder_COUNT : ==> Preparing: SELECT count(0) FROM order_tbl
2022-08-29 16:25:16.158 DEBUG 35624 --- [io-20002-exec-3] c.t.c.o.w.m.OrderMapper.listOrder_COUNT : ==> Parameters:
2022-08-29 16:25:16.158 DEBUG 35624 --- [io-20002-exec-3] c.t.c.o.w.m.OrderMapper.listOrder_COUNT : <== Total: 1
2022-08-29 16:25:16.159 DEBUG 35624 --- [io-20002-exec-3] c.t.c.o.w.mapper.OrderMapper.listOrder : ==> Preparing: select id, user_id, commodity_code, count, money from order_tbl LIMIT ?, ?
2022-08-29 16:25:16.159 DEBUG 35624 --- [io-20002-exec-3] c.t.c.o.w.mapper.OrderMapper.listOrder : ==> Parameters: 3(Long), 3(Integer)
2022-08-29 16:25:16.160 DEBUG 35624 --- [io-20002-exec-3] c.t.c.o.w.mapper.OrderMapper.listOrder : <== Total: 1
SELECT count(0) FROM order_tbl
select id, user_id, commodity_code, count, money from order_tbl LIMIT 3,3
通过观察日志,可以得知运行的过程
查询分为2次,第一次查询总数量,第二次查询当前页面数据
优化
由于PageInfo返回信息较为复杂,数据过多,实际使用中,经常会自己包装响应类,来简化信息,一般只响应当前页码,总页数,总数量,以及数据
参考:
package cn.tedu.mall.common.restful;
import com.github.pagehelper.PageInfo;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
/**
* 分页数据封装
*/
@Data
public class JsonPage<T> implements Serializable {
private Integer pageIndex;
private Integer pageSize;
private Integer totalPage;
private Long total;
private List<T> list;
public static <T> JsonPage<T> restPage(PageInfo<T> pageInfo) {
JsonPage<T> result = new JsonPage<>();
result.setTotalPage(pageInfo.getPages());
result.setPageIndex(pageInfo.getPageNum());
result.setPageSize(pageInfo.getPageSize());
result.setTotal(pageInfo.getTotal());
result.setList(pageInfo.getList());
return result;
}
}
756
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
