pagehelper-分页插件_pagehele-优快云博客

本文介绍PageHelper在SpringBoot+Mybatis+Druid环境下的集成与使用，通过PageHelper简化分页查询，避免重复编写SQL，提高开发效率。文章详细解释了PageHelper的工作原理，包括配置、代码实现及性能影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

说明

前端在展示数据列表时需要分页，通常后端是通过两个sql查询数据，一个查询总数、另一个通过limit,start查询当前页具体数据。这样会导致后端类似的sql写两遍。通过pagehelper可以只写一条查询sql,pagehelper通过解析sql组装查询总数的sql从而查询出总数。

集成方式

springboot+mybatis+druid+pagehelper

1. 添加依赖

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
</dependency>
<dependency>
   <groupId>org.mybatis.spring.boot</groupId>
   <artifactId>mybatis-spring-boot-starter</artifactId>
   <version>1.3.1</version>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.1.0</version>
</dependency>
<dependency>
   <groupId>com.github.pagehelper</groupId>
   <artifactId>pagehelper-spring-boot-starter</artifactId>
   <version>1.2.3</version>
</dependency>

2. property配置

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/yueding?characterEncoding=UTF-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialize=false
spring.datasource.tomcat.min-idle=1
spring.datasource.tomcat.max-active=20
spring.datasource.tomcat.max-wait=6000
spring.datasource.tomcat.time-between-eviction-runs-millis=60000
spring.datasource.tomcat.min-evictable-idle-time-millis=300000
spring.datasource.tomcat.validation-query=SELECT 1
spring.datasource.tomcat.test-while-idle=true
spring.datasource.tomcat.test-on-borrow=false
spring.datasource.tomcat.test-on-return=false

pagehelper.helper-dialect=mysql
pagehelper.offset-as-page-num=true
pagehelper.row-bounds-with-count=true
pagehelper.reasonable=false

3. 代码编写

查询之前使用PageHelper启动分页查询
查询后可通过page.getTotal()获取记录总数

Page<?> page = PageHelper.startPage(pageIndex,pageSize);
List<GroupEntity> list = groupMapper.selectAllGroup(groupName);
data.put("data",list);
data.put("pageIndex",pageIndex);
data.put("pageSize",pageSize);
data.put("total",page.getTotal());

原理

启动时读取配置信息,添加拦截器拦截query方法
startPage时将相关分页信息存入到当前线程ThreadLocal中

public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
    Page<E> page = new Page(pageNum, pageSize, count);
    page.setReasonable(reasonable);
    page.setPageSizeZero(pageSizeZero);
    Page<E> oldPage = getLocalPage();
    if(oldPage != null && oldPage.isOrderByOnly()) {
        page.setOrderBy(oldPage.getOrderBy());
    }
    setLocalPage(page);
    return page;
}

如果需要查询总数，组装查询总数sql

public String getSmartCountSql(String sql, String name) {
    Statement stmt = null;
    if(sql.indexOf("/*keep orderby*/") >= 0) {
        return this.getSimpleCountSql(sql);
    } else {
        try {
            stmt = CCJSqlParserUtil.parse(sql);
        } catch (Throwable var8) {
            return this.getSimpleCountSql(sql);
        }
        Select select = (Select)stmt;
        SelectBody selectBody = select.getSelectBody();
        try {
            this.processSelectBody(selectBody);
        } catch (Exception var7) {
            return this.getSimpleCountSql(sql);
        }
        this.processWithItemsList(select.getWithItemsList());
        this.sqlToCount(select, name);
        String result = select.toString();
        return result;
    }
}
public String getSimpleCountSql(String sql, String name) {
    StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
    stringBuilder.append("select count(");
    stringBuilder.append(name);
    stringBuilder.append(") from (");
    stringBuilder.append(sql);
    stringBuilder.append(") tmp_count");
    return stringBuilder.toString();
}
public void sqlToCount(Select select, String name) {
    SelectBody selectBody = select.getSelectBody();
    List<SelectItem> COUNT_ITEM = new ArrayList();
    COUNT_ITEM.add(new SelectExpressionItem(new Column("count(" + name + ")")));
    if(selectBody instanceof PlainSelect && this.isSimpleCount((PlainSelect)selectBody)) {
        ((PlainSelect)selectBody).setSelectItems(COUNT_ITEM);
    } else {
        PlainSelect plainSelect = new PlainSelect();
        SubSelect subSelect = new SubSelect();
        subSelect.setSelectBody(selectBody);
        subSelect.setAlias(TABLE_ALIAS);
        plainSelect.setFromItem(subSelect);
        plainSelect.setSelectItems(COUNT_ITEM);
        select.setSelectBody(plainSelect);
    }
}