MyBatis pagination plugin PageHelper eliminates the need to embed pagination logic or parameters directly in SQL statements. Users only need to specify pagination rules before invoking DAO layer methods to automatically implement physical pagination, greatly simplifying pagination query development for MyBatis projects.

Core Features

• Lightweight setup: Pagination capability can be enabled in MyBatis projects with minimal configuration, no additional business code entrusion
• Multi-database support: Compatible with almost all mainstream relational databases, adapting to different dialect pagination syntax automatically
• Comprehensive functionality: Supports basic pagination, custom sorting, physical pagination, memory pagination and other common pagination scenarios
• Flexible customization: Provides rich configuration parameters to adjust pagination behavior according to actual business needs

Standard PageHelper Usage Flow

1. Import PageHelper dependency in the project build file
2. Configure the PageInterceptor plugin in the MyBatis global configuration file, specify the corresponding database dialect
3. Call PageHelper.startPage(pageNum, pageSize) immediately before executing the query mapper method to set pagination parameters
4. Wrap the returned query result list with PageInfo to obtain complete pagination metadata including total records, total pages, etc.

Usage Example

<!-- Maven dependency configuration in pom.xml -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>latest-stable</version>
</dependency>

<!-- MyBatis global configuration plugin registration -->
<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <property name="dialect" value="mysql"/>
    </plugin>
</plugins>

// Business layer pagination query code
public List<Customer> queryCustomerByPage(int currentPage, int pageLimit) {
    PageHelper.startPage(currentPage, pageLimit);
    List<Customer> customerList = customerMapper.selectAllCustomers();
    PageInfo<Customer> pageData = new PageInfo<>(customerList);
    return pageData.getList();
}

Below we implement a custom pagination plugin with the same core capabilities as PageHelper based on MyBatis interceptor mechanism.

Pagination Data Entity Definition

package com.example.mybatis.plugin.entity;

/**
 * Pagination parameter container
 */
public class PaginationParam {
    /**
     * Current page number
     */
    private Integer currentPage;
    /**
     * Number of records per page
     */
    private Integer pageLimit;

    public PaginationParam(Integer currentPage, Integer pageLimit) {
        this.currentPage = currentPage;
        this.pageLimit = pageLimit;
    }

    // Getter and Setter methods omitted
}

package com.example.mybatis.plugin.entity;

/**
 * Complete pagination result metadata
 */
public class PaginationResult extends PaginationParam {
    /**
     * Total number of pages
     */
    private Integer totalPages;
    /**
     * Total number of matching records
     */
    private Long totalRecords;

    public PaginationResult(PaginationParam param, Integer totalPages, Long totalRecords) {
        super(param.getCurrentPage(), param.getPageLimit());
        this.totalPages = totalPages;
        this.totalRecords = totalRecords;
    }

    // Getter and Setter methods omitted
}

Pagination Parameter Management Utility

Use ThreadLocal to store pagination parameters bound to the current thread to avoid parameter confusion in multi-threaded scenarios, and clean up data in time to prevent memory leaks.

package com.example.mybatis.plugin.util;

import com.example.mybatis.plugin.entity.PaginationParam;
import com.example.mybatis.plugin.entity.PaginationResult;

public class CustomPageHelper {
    /**
     * Store pagination parameters for current thread
     */
    private static final ThreadLocal<PaginationParam> PAGINATION_PARAM_HOLDER = new ThreadLocal<>();
    /**
     * Store pagination result metadata for current thread
     */
    private static final ThreadLocal<PaginationResult> PAGINATION_RESULT_HOLDER = new ThreadLocal<>();

    /**
     * Enable pagination with specified parameters
     * @param currentPage Target page number, defaults to 1 if null
     * @param pageLimit Number of records per page, defaults to Integer.MAX_VALUE if null
     */
    public static void startPage(Integer currentPage, Integer pageLimit) {
        currentPage = currentPage == null || currentPage <= 0 ? 1 : currentPage;
        pageLimit = pageLimit == null || pageLimit <= 0 ? Integer.MAX_VALUE : pageLimit;
        PAGINATION_PARAM_HOLDER.set(new PaginationParam(currentPage, pageLimit));
    }

    /**
     * Clear pagination parameters for current thread
     */
    public static void clearPaginationParam() {
        if (PAGINATION_PARAM_HOLDER.get() != null) {
            PAGINATION_PARAM_HOLDER.remove();
        }
    }

    /**
     * Get pagination parameters for current thread
     */
    public static PaginationParam getPaginationParam() {
        return PAGINATION_PARAM_HOLDER.get();
    }

    /**
     * Save pagination result metadata
     */
    public static void setPaginationResult(PaginationResult result) {
        PAGINATION_RESULT_HOLDER.set(result);
    }

    /**
     * Get and clear pagination result metadata
     */
    public static PaginationResult getPaginationResult() {
        PaginationResult result = PAGINATION_RESULT_HOLDER.get();
        PAGINATION_RESULT_HOLDER.remove();
        return result;
    }
}

Pagination Interceptor Implementation

package com.example.mybatis.plugin.interceptor;

import com.example.mybatis.plugin.entity.PaginationParam;
import com.example.mybatis.plugin.entity.PaginationResult;
import com.example.mybatis.plugin.util.CustomPageHelper;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

@Intercepts(@Signature(
    type = StatementHandler.class,
    method = "prepare",
    args = {Connection.class, Integer.class}
))
public class CustomPaginationInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MetaObject statementMeta = SystemMetaObject.forObject(invocation.getTarget());
        String rawSql = (String) statementMeta.getValue("boundSql.sql");
        SqlCommandType sqlType = (SqlCommandType) statementMeta.getValue("delegate.mappedStatement.sqlCommandType");

        // Skip non-query statements
        if (sqlType != SqlCommandType.SELECT) {
            return invocation.proceed();
        }

        PaginationParam param = CustomPageHelper.getPaginationParam();
        // Skip queries without pagination configuration
        if (param == null) {
            return invocation.proceed();
        }

        // Modify raw SQL to add pagination clause (MySQL dialect example)
        int offset = (param.getCurrentPage() - 1) * param.getPageLimit();
        String paginatedSql = String.format("%s LIMIT %d, %d", rawSql.trim(), offset, param.getPageLimit());
        statementMeta.setValue("boundSql.sql", paginatedSql);

        // Calculate and save pagination metadata
        calculatePaginationMeta(invocation, statementMeta, rawSql, param);
        // Clear pagination parameters to avoid affecting subsequent queries
        CustomPageHelper.clearPaginationParam();

        return invocation.proceed();
    }

    /**
     * Query total number of matching records and calculate pagination metadata
     */
    private void calculatePaginationMeta(Invocation invocation, MetaObject statementMeta, String rawSql, PaginationParam param) throws Throwable {
        Connection connection = (Connection) invocation.getArgs()[0];
        // Generate count query SQL
        String trimmedSql = rawSql.trim();
        String countSql = String.format("SELECT COUNT(*) %s", trimmedSql.substring(trimmedSql.toLowerCase().indexOf("from")));

        PreparedStatement countStmt = connection.prepareStatement(countSql);
        ParameterHandler paramHandler = (ParameterHandler) statementMeta.getValue("delegate.parameterHandler");
        paramHandler.setParameters(countStmt);

        ResultSet rs = countStmt.executeQuery();
        long totalRecords = 0L;
        if (rs.next()) {
            totalRecords = rs.getLong(1);
        }
        rs.close();
        countStmt.close();

        int totalPages = (int) Math.ceil((double) totalRecords / param.getPageLimit());
        CustomPageHelper.setPaginationResult(new PaginationResult(param, totalPages, totalRecords));
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        // Support custom configuration parameters if needed
    }
}

MyBatis Configuration Example

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias type="com.example.mybatis.entity.Customer" alias="Customer"/>
    </typeAliases>

    <plugins>
        <plugin interceptor="com.example.mybatis.plugin.interceptor.CustomPaginationInterceptor"/>
    </plugins>

    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="your_password"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/CustomerMapper.xml"/>
    </mappers>
</configuration>

Pagination Test Code

package com.example.mybatis.test;

import com.example.mybatis.mapper.CustomerMapper;
import com.example.mybatis.plugin.entity.PaginationResult;
import com.example.mybatis.plugin.util.CustomPageHelper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;

public class PaginationPluginTest {
    private SqlSession session;

    @Before
    public void initSession() throws IOException {
        InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(configStream);
        session = sessionFactory.openSession(true);
    }

    @After
    public void closeSession() {
        if (session != null) {
            session.close();
        }
    }

    @Test
    public void testFullTablePagination() {
        CustomerMapper customerMapper = session.getMapper(CustomerMapper.class);
        // Query first page, 3 records per page
        CustomPageHelper.startPage(1, 3);
        System.out.println("First page query result: " + customerMapper.selectAll());
        PaginationResult firstPageMeta = CustomPageHelper.getPaginationResult();
        System.out.println("First page metadata: " + firstPageMeta);

        System.out.println("====================================");
        // Query second page, 3 records per page
        CustomPageHelper.startPage(2, 3);
        System.out.println("Second page query result: " + customerMapper.selectAll());
        PaginationResult secondPageMeta = CustomPageHelper.getPaginationResult();
        System.out.println("Second page metadata: " + secondPageMeta);
    }

    @Test
    public void testConditionalPagination() {
        CustomerMapper customerMapper = session.getMapper(CustomerMapper.class);
        // Query female customers, first page 4 records
        CustomPageHelper.startPage(1, 4);
        System.out.println("Female customers first page: " + customerMapper.selectByGender("female"));
        PaginationResult femalePageMeta = CustomPageHelper.getPaginationResult();
        System.out.println("Female customer pagination metadata: " + femalePageMeta);

        System.out.println("====================================");
        // Query male customers, first page 2 records
        CustomPageHelper.startPage(1, 2);
        System.out.println("Male customers first page: " + customerMapper.selectByGender("male"));
        PaginationResult malePageMeta = CustomPageHelper.getPaginationResult();
        System.out.println("Male customer pagination metadata: " + malePageMeta);
    }
}