Why PageHelper Can Break Your MyBatis Queries and How to Fix It

Unexpected Issues When Using PageHelper

After years of not using PageHelper, the author encountered several puzzling problems in a new project that integrates this pagination tool. The symptoms included duplicate user registration being allowed, dropdown lists returning only five rows despite more data, and password‑reset operations failing with a SQL error about an unexpected LIMIT 5 clause.

Root Cause: Hidden LIMIT Injection via ThreadLocal

All the above anomalies stem from PageHelper automatically appending a LIMIT clause to SQL statements based on pagination parameters stored in a ThreadLocal. When the pagination context is not cleared correctly, subsequent requests inherit the stale parameters, causing unintended limits.

Typical Code Patterns

else if (UserConstants.NOT_UNIQUE.equals(userService.checkUserNameUnique(username))
    || "匿名用户".equals(username)) {
    // 注册用户已存在
    msg = "注册用户'" + username + "'失败";
}

The method checkUserNameUnique(username) checks the database for an existing username:

<select id="checkUserNameUnique" parameterType="String" resultType="int">
    select count(1) from sys_user where user_name = #{userName} limit 1
</select>

How PageHelper Works

In a controller method the pagination is started with:

@GetMapping("/cms/cmsEssayList")
public TableDataInfo cmsEssayList(CmsBlog cmsBlog) {
    cmsBlog.setStatus("1"); // only published
    startPage();
    List<CmsBlog> list = cmsBlogService.selectCmsBlogList(cmsBlog);
    return getDataTable(list);
}

startPage()

reads pagination parameters from the HTTP request, builds a PageDomain, and calls

PageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable)

. The parameters are stored in a ThreadLocal<Page> LOCAL_PAGE:

protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal<Page>();

When a MyBatis mapper method is invoked, PageInterceptor intercepts the call. It retrieves the current Page via PageHelper.getLocalPage() and decides whether to apply pagination:

Page page = PageHelper.getLocalPage();
if (!dialect.skip(ms, parameter, rowBounds)) {
    // count query if needed
    Long count = count(...);
    if (!dialect.afterCount(count, parameter, rowBounds)) {
        return dialect.afterPage(new ArrayList(), parameter, rowBounds);
    }
    resultList = ExecutorUtil.pageQuery(...);
} else {
    resultList = executor.query(...);
}
return dialect.afterPage(resultList, parameter, rowBounds);

After the SQL execution, the interceptor’s finally block calls dialect.afterAll(), which eventually invokes clearPage() to remove the thread‑local pagination data:

public static void clearPage() {
    LOCAL_PAGE.remove();
}

Why Stale Pagination Persists

If startPage() is called but the subsequent SQL is never executed (e.g., an early return or an exception before the mapper call), the finally block may be skipped, leaving the ThreadLocal populated. When the same thread processes a later request that does not intend pagination, the leftover LIMIT is still appended, producing the observed errors.

Practical Recommendations

Always execute the SQL statement immediately after startPage(). The pagination context should not be left dangling.

If you must perform non‑paginated operations in the same thread after a paginated call, manually invoke clearPage() before those operations.

Avoid calling clearPage() before a legitimate pagination call, as it will remove the needed parameters and break pagination.

Thread Pool Influence

Web containers such as Tomcat reuse threads from a pool. If a thread retains stale pagination data, only the requests that happen to be processed by that thread will exhibit the bug, explaining why the issue appears intermittently.

Conclusion

Understanding PageHelper’s internal use of ThreadLocal and ensuring proper cleanup are essential to prevent hidden LIMIT clauses from corrupting queries. By following the guidelines above, developers can safely integrate PageHelper without encountering the described “奇葩” bugs.

Author: 书包肚肚 Source: juejin.cn/post/7125356642366914596