PageHelper 实现分页查询以及其实现原理

发布于：2023-01-04 ⋅ 阅读:(856) ⋅ 点赞:(0)

介绍

常规分页,我们可以在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;
    }

}

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布