Paging principle of MyBatis

Writing purpose

Recently, I saw an article on the paging implementation principle of MyBatis. The article describes the use of ThreadLocal. In fact, I mainly want to see the clever use of ThreadLocal and how paging is implemented.

Source download

ChaiRongD/Demooo -

Source tracking

In fact, a simple paging is shown in the following code. Use the PageHelp object to set the paging parameters, and then pass the queried List object as a parameter into the PageInfo object to get the results of the paging object.

    public Object page() {
        //Query the third page, with three entries per page
        PageHelper.startPage(3 , 3);
        List<Temperature> temperatures = temperatureDao.selectByExample(null);
        //Get the paged result object
        PageInfo<Temperature> resPage = new PageInfo<>(temperatures);
        return resPage;

PageHelper.startPage method

After that, you will locate the startPage method of PageMethod. The content of the method is to create a page object containing paging parameters and put it in ThreadLocal.

     * Start paging
     * @param pageNum      Page number
     * @param pageSize     Display quantity per page
     * @param count        count query
     * @param reasonable   Paging rationalization, default configuration when null
     * @param pageSizeZero true When pageSize=0, all results are returned. When false, paging is performed. When null, the default configuration is used
    public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
        //Build a page object that contains paging parameters
        //Build a page object that contains paging parameters
        //Build a page object that contains paging parameters
        Page<E> page = new Page<E>(pageNum, pageSize, count);
        //When orderBy has been executed
        Page<E> oldPage = getLocalPage();
        if (oldPage != null && oldPage.isOrderByOnly()) {
        //Put the page object in ThreadLocal
        //Put the page object in ThreadLocal
        //Put the page object in ThreadLocal
        return page;

Real execution logic

Execute Dao select method

List<Temperature> temperatures = temperatureDao.selectByExample(null);

Next, skip directly to the intercept method of PageInterceptor

    public Object intercept(Invocation invocation) throws Throwable {
        try {
            //Omit content, omit content, omit content

            List resultList;
            //Step 1: call the method to determine whether paging is required. If not, return the result directly
            if (!dialect.skip(ms, parameter, rowBounds)) {
                //Judge whether count query is required
                if (dialect.beforeCount(ms, parameter, rowBounds)) {
                    //Step 2: query the total number of entries
                    Long count = count(executor, ms, parameter, rowBounds, resultHandler, boundSql);
                    //The total number of queries processed. When it returns true, the paging query will continue, and when it returns false, it will be returned directly
                    //Step 3: save the total number of entries
                    if (!dialect.afterCount(count, parameter, rowBounds)) {
                        //When the total number of queries is 0, empty results will be returned directly
                        return dialect.afterPage(new ArrayList(), parameter, rowBounds);
               //Step 4: execute paging query
                resultList = ExecutorUtil.pageQuery(dialect, executor,
                        ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
            } else {
                //The parameter value of rowBounds is used. When the paging plug-in is not used for processing, the default memory paging is still supported
                resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
            //Step 5: package results
            return dialect.afterPage(resultList, parameter, rowBounds);
        } finally {
            if(dialect != null){

Step 1: determine whether to page

First, check whether paging is required according to the skip method of PageHelper. The judgment condition is whether there is a page object in ThreadLocal because PageHelper Put the startpage method into ThreadLocal and put the page object into ThreadLocal, so it will be judged as paging here

Step 2: query the total number of entries

Method will locate the code of the count method of PageInterceptor

count = ExecutorUtil.executeAutoCount(dialect, executor, countMs, parameter, boundSql, rowBounds, resultHandler);

The executeAutoCount method is as follows:,

1) First, splice count statements according to query statements (select * from table where a -- > select count ("0") from table where a)

2) Then execute SQL

3) Get the count result

Step 3: save the total number of entries

First, get the page object from ThreadLocal, then put the total number count in the page object, and then judge whether it is necessary to query according to the total number and paging conditions. For example, there are 10 records in total, 10 records per page, and you check page 2, so there is no need to query, so 11-20 records do not exist.

public boolean afterCount(long count, Object parameterObject, RowBounds rowBounds) {
        //Get the page object in ThreadLocal
        Page page = getLocalPage();
        //Save count object
        if (rowBounds instanceof PageRowBounds) {
            ((PageRowBounds) rowBounds).setTotal(count);
        //When PageSize < 0, the paging query is not executed
        //When pageSize = 0, subsequent queries need to be executed, but paging will not be performed
        if (page.getPageSize() < 0) {
            return false;
        //Judge whether it is necessary to query according to the total quantity and your paging conditions
        return count > ((page.getPageNum() - 1) * page.getPageSize());

Step 4: execute paging query

First, get the paged SQL (slect * from table - > select * from table limit?,?), Then execute to get the result

How do I get paging SQL? Simple enough to have no friends

public String getPageSql(String sql, Page page, CacheKey pageKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
        if (page.getStartRow() == 0) {
            sqlBuilder.append(" LIMIT ? ");
        } else {
            sqlBuilder.append(" LIMIT ?, ? ");
        return sqlBuilder.toString();

Step 5: package results

Or put the query results into the page object in TheadLocal, and then return the page object. At this time, the page object contains the query object set, the number of pages and the page number.

public Object afterPage(List pageList, Object parameterObject, RowBounds rowBounds) {
        Page page = getLocalPage();
        if (page == null) {
            return pageList;

        return page;

Construct PageInfo object

First of all, it should be clear that the temperatures object in the following code is of type Page (Page < E > extends ArrayList < E >), which integrates the ArrayList object.

Let's look at the construction method of PageInfo. It's really a shock. First, the list parameter passes in the Page object. You can get the total, pageNum, pageSize and the data set of the current Page from the Page object to further calculate whether it is the first Page, last Page and other unnecessary paging information.

public PageInfo(List<T> list, int navigatePages) {
        //Forcibly convert the list object to the page object, and then obtain the total number of objects
        if (list instanceof Page) {
            Page page = (Page) list;
            //Get current page
            this.pageNum = page.getPageNum();
            //Gets the size of each page
            this.pageSize = page.getPageSize();

            this.pages = page.getPages();
            this.size = page.size();
            //Since the result is > startRow, the actual need is + 1
            if (this.size == 0) {
                this.startRow = 0;
                this.endRow = 0;
            } else {
                this.startRow = page.getStartRow() + 1;
                //Calculate the actual endRow (special on the last page)
                this.endRow = this.startRow - 1 + 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 = 0;
            this.endRow = list.size() > 0 ? list.size() - 1 : 0;
        if (list instanceof Collection) {
            this.navigatePages = navigatePages;
            //Calculate navigation page
            //Calculate the front and back pages, the first page and the last page
            //Judge page boundary


Paging process

First, the paging parameters will be encapsulated into Page objects and put into ThreadLocal

Then perform splicing conversion according to SQL (select * from table where a) - > (select count("0") from table where a) and (select * from table where a limit?,?)

With the total number of entries, the current page number of pageNum, the size of each page of pageSize and the data of the current page, you can calculate other unnecessary information of paging (whether it is the first page, the last page and the total number of pages)

Use of ThreadLocal object

Clever use of ThreadLocal (big)

Keywords: Back-end

Added by homchz on Fri, 04 Feb 2022 06:33:13 +0200