【PageHelper】详解

_ZYF_

已于 2023-04-03 17:28:42 修改

阅读量3.6k

点赞数 2

文章标签： mybatis java spring boot

于 2023-04-03 17:08:23 首次发布

原文链接：https://blog.youkuaiyun.com/u014240299/article/details/120613600

版权

你知道的越多,你不知道的也越多

使用过Mybatis框架的亲故们肯定都听说过PageHelper这个分页神器吧?
简单的一句话PageHelper.startPage(pageNo,pageLimit)就可以帮我们实现分页!
YYDS有没有?废话不多说,开始探索奥秘吧.

PageHelper

日常使用

由于目前很多项目都基于SpringBoot,引入PageHelper也是极其的方便.这里不提供业务代码.相信聪明的你肯定会自行百度,或者直接拿日常项目里的代码作为学习样本.
来来来,我这里随手写了一个demo,主要就是一个分页查询. 上核心代码:

@Service
public class UserService {
<span class="token annotation punctuation">@Autowired</span>
<span class="token keyword">private</span> <span class="token class-name">UserMapper</span> userMapper<span class="token punctuation">;</span>

/**
* 依据用户昵称进行模糊分页查询
*
* @param name
* @param page
* @param limit
* @return
*/
public PageInfo<User> findPageUsersByName(String name, int page, int limit) {
PageHelper.startPage(page, limit);
List<User> users = userMapper.selectByName(name);
PageInfo<User> pageUsers = new PageInfo<>(users);
return pageUsers;
}
}

对应的UserMapper.xml的文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yuki.mybatisdemo.dao.UserMapper">
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>resultMap</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>BaseResultMap<span class="token punctuation">"</span></span> <span class="token attr-name">type</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>com.yuki.mybatisdemo.entity.User<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>id<span class="token punctuation">"</span></span> <span class="token attr-name">jdbcType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>INTEGER<span class="token punctuation">"</span></span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>id<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>nickname<span class="token punctuation">"</span></span> <span class="token attr-name">jdbcType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>VARCHAR<span class="token punctuation">"</span></span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>nickName<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>tel<span class="token punctuation">"</span></span> <span class="token attr-name">jdbcType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>VARCHAR<span class="token punctuation">"</span></span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>tel<span class="token punctuation">"</span></span> <span class="token punctuation">/&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>resultMap</span><span class="token punctuation">&gt;</span></span>

<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>select</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>selectByName<span class="token punctuation">"</span></span> <span class="token attr-name">parameterType</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>java.util.Map<span class="token punctuation">"</span></span> <span class="token attr-name">resultMap</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>BaseResultMap<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>bind</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span>nickname<span class="token punctuation">"</span></span> <span class="token attr-name">value</span><span class="token attr-value"><span class="token punctuation attr-equals">=</span><span class="token punctuation">"</span> <span class="token punctuation">'</span>%<span class="token punctuation">'</span>+name+<span class="token punctuation">'</span>%<span class="token punctuation">'</span> <span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
    select id,nickname,tel from user where nickname like #{nickname}
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>select</span><span class="token punctuation">&gt;</span></span>

</mapper>

首先先说下,demo里使用的MySQL数据库啊. 因为会涉及到后面底层方言需要选择哪种数据库实现来处理一些逻辑,比如分页sql的拼接方式.

这里我们需要先观察下,进行查询的时候,底层到底执行了哪些SQL:
在这里插入图片描述
额,看出来了, 执行了2条SQL.
奇怪了, UserMapper.xml里的SQL明明只有一条啊,并且根本没有任何的分页参数.
别挠头啦,这就是PageHelper搞的事情嘛~

源码剖析

提前说下实现的原理,方便后续的源码分析阶段.

注意: 这里我使用MySQL数据库!!!

原理:

调用PageHelper.startPage(pageNo,pageLimit)的时候,就已经静悄悄地把我们的分页参数存储到一个变量 ThreadLocal<Page> LOCAL_PAGE;
执行userMapper进行查询,实际上是被一个叫做PageInterceptor.java拦截到了,执行了它重写的interceptor方法. 这里涉及到MyBatis里的拦截器原理,本文不重点说明了,相信聪明你肯定已经知道啦;
该方法里,主要是做了如下事情:
(1) 获取到MappedStatement, 拿到业务写好的sql, 将sql改造成select count(0) 并执行查询,并将执行结果存到了LOCAL_PAGE里的Page里的total属性.
(2) 获取到我们自己写在xml里的sql , 并append一些分页sql段,然后执行, 将执行结果存到了LOCAL_PAGE里的Page里的list属性.Page 其实是ArrayList的子类.
可以看出,结果都是封装到了Page里, 最后交由PageInfo,可以获取到总条数,总页数,是否还有下一页等参数.

接下去就是一步一步地验证啦.

1. 分页参数存储

主要是接住了PageHelper.startPage,直接看源码:

    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());
        }
    <span class="token function">setLocalPage</span><span class="token punctuation">(</span>page<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">return</span> page<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

其实主要就是把我们给的分页参数给到Page,然后实例Page并存储起来,存到哪了?

public abstract class PageMethod {
    protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal();
<span class="token keyword">public</span> <span class="token class-name">PageMethod</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{<!-- --></span>
<span class="token punctuation">}</span>

<span class="token keyword">protected</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">setLocalPage</span><span class="token punctuation">(</span><span class="token class-name">Page</span> page<span class="token punctuation">)</span> <span class="token punctuation">{<!-- --></span>
    LOCAL_PAGE<span class="token punctuation">.</span><span class="token function">set</span><span class="token punctuation">(</span>page<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

很显然了,就是ThreadLocal里.

思考下为什么用ThreadLocal ? 想必大家都知道, 可以用来做上下文值传递.
打个比方,在一个Request中,肯定是需要经过多个method处理, 如果多个method都需要使用到某个变量, 就可以放入到ThreadLocal ,各个method想用的时候就get().这样每个method 就不用在入参列表里声明啦!

继续看执行sql到底是怎么做的

2. 拦截器改造SQL

2.1 统计总数

其实肯定是知道通过拦截器底层执行sql的, 对应的拦截器就是PageInterceptor. 先来看下类头部的定义:

@Intercepts({@Signature(
    type = Executor.class,
    method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
), @Signature(
    type = Executor.class,
    method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}
)})
public class PageInterceptor implements Interceptor {
    protected Cache<String, MappedStatement> msCountMap = null;
    private Dialect dialect;
    private String default_dialect_class = "com.github.pagehelper.PageHelper";
    private Field additionalParametersField;
    private String countSuffix = "_COUNT";
<span class="token keyword">public</span> <span class="token class-name">PageInterceptor</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{<!-- --></span>
<span class="token punctuation">}</span>

拦截了Executor的query方法,其实也很好理解,毕竟MyBatis 底层查询其实就是借助SqlSession调用Executor#query嘛? 和数据库打交道都是Executor做的事情.

所以如果以后有人问你,拦截的是哪个方法,你可以拍着胸脯告诉他: Executor#query方法.

接下去,我们需要重点看下intercept,解析去采用debug方式,所以直接贴图说明:
在这里插入图片描述
稍微说明下: 这里的Invocation 其实是Plugin#invoke传入来的. 主要就是JDK动态代理的时候, 有三个入参, Plugin 将入参进行二次封装到Invocation里,然后再丢给了Interceptor.

所以Executor#query 方法的入参都可以在拦截器里拿到,也就意味着,我们可以在拦截器里能拿到当前正执行的MappedStatement(内有SQL)哦~

回到拦截器里,继续看下在哪里进行了总条数查询:
在这里插入图片描述
看下executeAutoCount方法:

总结: UserMapper.xml里解析出来的MappedStatement, 从获取到BoundSql, boundSql.getSql()可以获取到xml里写的sql, 然后交由CCJSqlParserUtil.parse(sql)解析成Statement的一个实现类Select

Select结构了解下,有一个SelectBody, 同样也有好几个实现类:
在这里插入图片描述

2.2 分页查询

再回调intercept看下如何分页
在这里插入图片描述
上述统计总条数的逻辑很像:
(1) 改造sql
统计总条数: this.dialect.getCountSql
分页: this.dialect.getPageSql
底层都是先借助PageHelper, 然后再落实到具体的实现类.
Q: 为什么要借助PageHelper?
A: PageHelper算核心接口了,不光要存储分页参数,还要存储结果

(2) executor 执行sql

(3)结果存入到Page

这里重点看下this.dialect.getPageSql
在这里插入图片描述

回到拦截器方法,看下执行的resultList是否真的存到了ThreadLocal里吧.

关注下Page

其实到这里,源码看的差不多了吧… 等等,既然使用到了ThreadLocal , 常规操作要使用之后,就要remove的啊. 嘿嘿,有的有的. 在 finally
代码块里哦

在这里插入图片描述

核心类: PageHelper , PageInterceptor, Page .
在这里插入图片描述

3.PageInfo

那为什么最后还要写一句:PageInfo<User> pageUsers = new PageInfo<>(users)
才能获取到分页信息呢?

在这里插入图片描述

背后思考

如果PageHelper.start写在了mapper查询后面会怎样? mapper查询会按照原sql执行,不会做任何的分页操作;
这里注意: 由于ThreadLocal是和线程有关的,所以PageHelper.start 也可以跨方法使用.举例子说明:
由于ThreadLocal 每次查询的时候都会被remove掉,所以一次mapper查询对应一次PageHelper.start;

对应的sql: