Boost MyBatis Efficiency with the PageHelper Pagination Plugin

MyBatis-PageHelper is a universal pagination plugin for MyBatis that supports many physical databases, including HSQLDB, MySQL, Oracle, PostgreSQL, SQLite, DB2, and more.

static {
    // Register aliases
    registerDialectAlias("hsqldb", HsqldbDialect.class);
    registerDialectAlias("h2", HsqldbDialect.class);
    registerDialectAlias("phoenix", HsqldbDialect.class);
    registerDialectAlias("postgresql", PostgreSqlDialect.class);
    registerDialectAlias("mysql", MySqlDialect.class);
    registerDialectAlias("mariadb", MySqlDialect.class);
    registerDialectAlias("sqlite", MySqlDialect.class);
    registerDialectAlias("herddb", HerdDBDialect.class);
    registerDialectAlias("oracle", OracleDialect.class);
    registerDialectAlias("oracle9i", Oracle9iDialect.class);
    registerDialectAlias("db2", Db2Dialect.class);
    registerDialectAlias("informix", InformixDialect.class);
    // Resolve informix-sqli #129, keep the above
    registerDialectAlias("informix-sqli", InformixDialect.class);
    registerDialectAlias("sqlserver", SqlServerDialect.class);
    registerDialectAlias("sqlserver2012", SqlServer2012Dialect.class);
    registerDialectAlias("derby", SqlServer2012Dialect.class);
    // DM database
    registerDialectAlias("dm", OracleDialect.class);
    // Alibaba PPAS
    registerDialectAlias("edb", OracleDialect.class);
    // Shentong database
    registerDialectAlias("oscar", OscarDialect.class);
    registerDialectAlias("clickhouse", MySqlDialect.class);
    // HighGo database
    registerDialectAlias("highgo", HsqldbDialect.class);
    // Xugu database
    registerDialectAlias("xugu", HsqldbDialect.class);
}

If your database is not listed, you can configure a custom dialectAlias property; the plugin will select the appropriate implementation based on the JDBC URL.

<property name="dialectAlias" value="oracle=com.github.pagehelper.dialect.helper.OracleDialect"/>

How to use?

Import method

Two ways: directly add the JAR or use Maven.

For Maven, add the following dependency to pom.xml:

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>latest</version>
</dependency>

Configure interceptor

1. Add the plugin in the MyBatis configuration XML:

<!-- plugins must appear in the correct order -->
<plugins>
    <!-- com.github.pagehelper is the package of PageHelper -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <!-- configure parameters here -->
        <property name="param1" value="value1"/>
    </plugin>
</plugins>

2. Configure the interceptor in a Spring bean:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <!-- other configurations -->
    <property name="plugins">
        <array>
            <bean class="com.github.pagehelper.PageInterceptor">
                <property name="properties">
                    <value>
                        params=value1
                    </value>
                </property>
            </bean>
        </array>
    </property>
</bean>

Plugin parameters

MyBatis-PageHelper provides many optional parameters. Some commonly used ones are:

helperDialect

offsetAsPageNum

rowBoundsWithCount

pageSizeZero

reasonable

params

supportMethodsArguments

autoRuntimeDialect

closeConn

These parameters control how pagination behaves, such as treating RowBounds offsets as page numbers, performing count queries, handling zero page size, enforcing reasonable page numbers, mapping custom parameter names, supporting method arguments, auto‑detecting dialects at runtime, and closing temporary connections.

Calling methods

Six different invocation styles are demonstrated:

// 1. RowBounds style
List<User> list = sqlSession.selectList("x.y.selectIf", null, new RowBounds(0, 10));

// 2. Mapper interface style (recommended)
PageHelper.startPage(1, 10);
List<User> list = userMapper.selectIf(1);

// 3. OffsetPage style
PageHelper.offsetPage(1, 10);
List<User> list = userMapper.selectIf(1);

// 4. Parameter method style with supportMethodsArguments=true
public interface CountryMapper {
    List<User> selectByPageNumSize(@Param("user") User user,
                                 @Param("pageNum") int pageNum,
                                 @Param("pageSize") int pageSize);
}
List<User> list = userMapper.selectByPageNumSize(user, 1, 10);

// 5. Parameter object style (pageNum and pageSize inside User)
public class User {
    private Integer pageNum;
    private Integer pageSize;
    // other fields
}
List<User> list = userMapper.selectByPageNumSize(user);

// 6. ISelect interface (JDK6/7) and lambda (JDK8) style
Page<User> page = PageHelper.startPage(1, 10).doSelectPage(new ISelect() {
    @Override
    public void doSelect() {
        userMapper.selectGroupBy();
    }
});
// JDK8 lambda
Page<User> page = PageHelper.startPage(1, 10).doSelectPage(() -> userMapper.selectGroupBy());

// Count query example
long total = PageHelper.count(() -> userMapper.selectLike(user));

Using this plugin can significantly reduce the amount of pagination code you need to write, especially for beginners.