Understanding PageHelper Pagination Issues and ThreadLocal Pitfalls in Java Backend Development

The author shares several puzzling problems encountered after integrating PageHelper into a Java backend project, including duplicate user registration, dropdown lists returning only five items, and password‑reset errors caused by an unexpected LIMIT clause.

Root cause analysis reveals that PageHelper relies on a ThreadLocal to store pagination parameters, which are set by startPage() and later retrieved during SQL interception.

Key code snippets:

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

The startPage() method builds a PageDomain from the request, then calls PageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable) , storing the Page object in a static ThreadLocal<Page> LOCAL_PAGE .

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

During MyBatis execution, the PageInterceptor.intercept() method checks dialect.skip(...) , which obtains the current Page via PageHelper.getLocalPage() . If a page is present, pagination logic (including count queries and ExecutorUtil.pageQuery ) is applied; otherwise the original query runs.

Even non‑paginated SQL can be affected if the ThreadLocal is not cleared, causing stray LIMIT clauses to be appended.

PageHelper attempts to clean up in a finally block by invoking dialect.afterAll() , which ultimately calls clearPage() to remove the thread‑local value:

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

However, if an exception occurs before the finally block executes, the ThreadLocal may remain polluted, leading to intermittent errors depending on thread reuse in containers like Tomcat or Netty.

Best practices recommended: always execute the SQL immediately after startPage() , and optionally call clearPage() manually before methods that should not be paginated, while avoiding manual clearing in methods that require pagination.

In summary, the article provides a deep dive into PageHelper’s inner workings, highlights common pitfalls caused by ThreadLocal misuse, and offers practical advice to prevent hard‑to‑track pagination bugs in Java backend services.