Mastering MyBatis PageHelper: Avoid Common Pagination Pitfalls

How to Use PageHelper

PageHelper is a MyBatis‑Plus plugin that provides convenient pagination. It stores the requested page number and size in a Page object saved in a ThreadLocal variable, then the MyBatis interceptor merges these parameters with the original SQL to generate a paginated query.

Common usage patterns include:

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

// Mapper method style (recommended)
PageHelper.startPage(1, 10);
List<Country> list = countryMapper.selectIf(1);

// Offset style
PageHelper.offsetPage(1, 10);
List<Country> list = countryMapper.selectIf(1);

// Parameter method
public interface CountryMapper {
    List<Country> selectByPageNumSize(@Param("user") User user,
                                     @Param("pageNum") int pageNum,
                                     @Param("pageSize") int pageSize);
}

// Parameter object (pageNum and pageSize inside User)
public class User {
    private Integer pageNum;
    private Integer pageSize;
}

// ISelect lambda (JDK8)
Page<Country> page = PageHelper.startPage(1, 10)
    .doSelectPage(() -> countryMapper.selectGroupBy());

// Count query
long total = PageHelper.count(() -> countryMapper.selectLike(country));
</code>

PageHelper Principle

The core mechanism creates a Page object with the supplied page number and size, stores it in ThreadLocal, and later the PageInterceptor retrieves it. The interceptor checks whether the SQL is a SELECT, appends pagination clauses, and finally removes the Page object from ThreadLocal to avoid memory leaks.

During interception, the plugin determines if pagination is required, obtains the Page information, and modifies the original SQL accordingly. Only queries are processed; other statements bypass pagination.

Precautions

Prefer calling

PageHelper.startPage(page, size)

immediately before the MyBatis query. Placing the call far from the query may leave the Page object in ThreadLocal, causing unintended pagination in subsequent queries.

Unsafe example (may leak ThreadLocal):

<code>PageHelper.startPage(1, 10);
List<Country> list;
if (param1 != null) {
    list = countryMapper.selectIf(param1);
} else {
    list = new ArrayList<>();
}
</code>

Safe example (call is adjacent to the query):

<code>List<Country> list;
if (param1 != null) {
    PageHelper.startPage(1, 10);
    list = countryMapper.selectIf(param1);
} else {
    list = new ArrayList<>();
}
</code>

The original issue described in the article stemmed from using

PageHelper.startPage(page, size)

with incorrect parameters, causing the first page to work but the second page to return no data because the ThreadLocal Page object was not cleared.