PageHelper underlying source code to understand

The more you know, the more you don't know

Friends who have used the Mybatis framework must have heard of PageHelper, a pagination artifact?
In a simple sentence, PageHelper.startPage(pageNo,pageLimit) can help us realize paging!
Did YYDS? No more nonsense, let's start exploring the mystery

Daily use

At present, many projects are based on SpringBoot, so it is very convenient to introduce PageHelper. Business code is not provided here. I believe that smart you will Baidu by yourself, or directly take the code in daily projects as a learning sample
Come on, I wrote a demo here, which is mainly a paging query. The upper core code:

public class UserService {

    private UserMapper userMapper;
     * Fuzzy paging query based on user nickname
     * @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;

Corresponding UserMapper.xml file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-// Mapper 3.0//EN" "">
<mapper namespace="com.yuki.mybatisdemo.dao.UserMapper">

    <resultMap id="BaseResultMap" type="com.yuki.mybatisdemo.entity.User">
        <result column="id" jdbcType="INTEGER" property="id" />
        <result column="nickname" jdbcType="VARCHAR" property="nickName" />
        <result column="tel" jdbcType="VARCHAR" property="tel" />

    <select id="selectByName" parameterType="java.util.Map" resultMap="BaseResultMap">
        <bind name="nickname" value=" '%'+name+'%' "/>
        select id,nickname,tel from user where nickname like #{nickname}

First, let's talk about the MySQL database used in the demo. Because it involves the underlying dialect, which database implementation needs to be selected to process some logic, such as the splicing method of paging sql

Here, we need to observe what SQL is executed at the bottom when querying:

Well, I see. Two SQL statements were executed
Strangely, there is only one SQL in UserMapper.xml, and there are no paging parameters at all
Don't scratch your head. That's what PageHelper is doing~

Source code analysis

Say the principle of implementation in advance to facilitate the subsequent source code analysis stage

Note: Here I use MySQL database!!!


  • When PageHelper.startPage(pageNo,pageLimit) is called, our paging parameters have been quietly stored in a variable ThreadLocal < Page > local_ PAGE;
  • Executing userMapper to query is actually intercepted by a method called, and the interceptor method rewritten by it is executed. This involves the interceptor principle in MyBatis. This article will not focus on it. I believe you must know it;
  • In this method, the following things are mainly done:
    (1) Get the MappedStatement, get the sql written by the business, transform the sql into select count(0), execute the query, and save the execution results to local_ total attribute in Page in Page
    (2) Get our own sql written in xml, append some paging sql segments, and then execute, and save the execution results to LOCAL_PAGE is actually a subclass of ArrayList
  • It can be seen that the results are encapsulated in the Page, and finally handed over to PageInfo to obtain the parameters such as the total number of entries, the total number of pages, and whether there is a next Page

The next step is to verify it step by step

1. Paging parameter storage

The main thing is to catch PageHelper.startPage and directly look at the source code:

    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<E> oldPage = getLocalPage();
        if (oldPage != null && oldPage.isOrderByOnly()) {

        return page;

In fact, the main thing is to give the paging parameters we give to the Page, and then instance the Page and store it. Where is it stored?

public abstract class PageMethod {
    protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal();

    public PageMethod() {

    protected static void setLocalPage(Page page) {

Obviously, it's in ThreadLocal

Think about why ThreadLocal? As we all know, it can be used for context value transmission
For example, in a Request, it must be processed by multiple methods. If multiple methods need to use a variable, they can be put into ThreadLocal. When each method wants to use it, get(). In this way, each method does not need to be declared in the input parameter list!

Continue to see how to execute sql

2. Interceptor reconstruction

2.1 total statistics

In fact, you must know that sql is executed through the underlying interceptor. The corresponding interceptor is PageInterceptor. Let's look at the definition of the following class header:

    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";

    public PageInterceptor() {

It's easy to understand that the query method of the Executor is intercepted. After all, the underlying query of MyBatis actually calls the Executor#query with SqlSession? Dealing with the database is what the Executor does

So if someone asks you which method to intercept later, you can pat him on the chest and tell him: Executor#query method

Next, we need to focus on intercept and use the debug method for parsing, so the direct mapping description is as follows:

A little explanation: the Invocation here is actually passed in by Plugin#invoke. It is mainly that there are three input parameters in the JDK dynamic agent. Plugin encapsulates the input parameters into the Invocation again, and then throws them to the Interceptor

Therefore, the input parameters of the Executor#query method can be obtained in the interceptor, which means that we can get the currently executing mappedstatement (with SQL inside) in the interceptor~

Go back to the interceptor and continue to see where the total number of queries were made:

Look at the executeAutoCount method:

Summary: the MappedStatement parsed in UserMapper.xml can obtain the sql written in the XML from boundsql. Getsql(), and then submit it to CCJSqlParserUtil.parse(sql) for parsing into an implementation class of the Statement

Under the understanding of the Select structure, there is a SelectBody, which also has several implementation classes:

2.2 paging query

Call back intercept to see how to page

The logic of the above statistics is very similar to:
(1) Transform sql
Total number of Statistics: this.dialect.getCountSql
Paging: this.dialect.getPageSql
The bottom layer is to use PageHelper first, and then implement it to the specific implementation class
Q: Why use PageHelper?
A: PageHelper is the core interface. It needs to store not only paging parameters, but also results

(2) executor executes sql

(3) Save results to Page

Here, focus on this.dialect.getPageSql

Go back to the interceptor method and see if the executed resultList is really saved in ThreadLocal

Follow Page

In fact, the source code here is almost the same... Wait, since ThreadLocal is used, it needs to be remove d after normal operations are used. Hey, some. finally
In the code block

Core classes: PageHelper, pageinterceptor, page


Then why write the last sentence: pageinfo < user > pageusers = new pageinfo < > (users)
To get paging information?

Thinking behind

  • What happens if PageHelper.start is written after the mapper query? The mapper query will be executed according to the original sql without any paging operation;
    Note here: since ThreadLocal is thread related, PageHelper.start can also be used across methods. Examples:

  • Since ThreadLocal is remove d every time it is queried, a mapper query corresponds to a PageHelper.start;

    Corresponding sql:

Keywords: Java Database MySQL SQL

Added by Joeker on Wed, 06 Oct 2021 00:31:37 +0300