Solve the context problem of PageHelper, causing the SQL query results to be incorrect

Preface

The problem scenario is that occasionally the result set found by a query interface is incorrect, and the phenomenon is all missing numbers, and it is occasionally happening.

After code tracking and reproduction, it was found that it was a context problem caused by PageHelper not being used correctly.

1. What is the reason

The root of this problem usually occurs when the pageHelper's pagination context is not properly cleaned, causing the pagination context to affect subsequent query execution without explicitly calling startPage.

2. Possible causes and solutions

1. The context of PageHelper is unexpectedly inherited

existAfter the call, if you have conditional judgment that some SQL queries are not executed and other queries are still executed, the pagination context may be mistakenly passed to these queries.

PageHelperIn the background, thread local variables will be passed (ThreadLocal) to manage paging information. If the paging context is not cleaned up, subsequent queries may inherit the previous paging settings.

Solution:

use()Manually clean up the paging context to ensure that the paging information will not affect subsequent queries, especially when the conditional judgment does not execute SQL, avoid affecting subsequent queries.

// Call pagination(pageNum, pageSize);

// Conditional judgmentif (someCondition) {
    // SQL query not executed} else {
    // Execute SQL query    List<User> users = ();
}

// Clean up the pagination context to prevent subsequent queries();

In this way, even if some queries are not executed due to conditional judgment,()The paging context is cleaned to prevent subsequent queries (whether paging is paginated or not) from being affected.

2. Query order or context is not cleaned

If there is a pagination setting between multiple queries, but some of them are not executed,()It may still have an impact on subsequent queries, resulting in the context of the paging query whether they explicitly set up paging or not.

Solution:

• Make sure that each paging query is called()To clean up the pagination context.
• Make sure that each query is explicitly called beforestartPage。

// Pagination query 1(pageNum, pageSize);
List<User> users = ();
();  // Clean up the page context
// Pagination query 2(nextPageNum, nextPageSize);
List<Order> orders = ();
();  // Clean up the page context

3. Context not properly separated when using pagination between multiple methods

If you use pagination in multiple methods and you are not cleaning up the context between pagination queries (clearPage), the pagination settings may interfere with each other.

For example, if there is no query data in method A and query in method B, the pagination information may be inherited to method B.

Solution:

Make sure to explicitly call before each paging querystartPage, and clean the paging context after each paging query.

// Method A(pageNum, pageSize);
if (someCondition) {
    // No query execution} else {
    // Execute query    List<User> users = ();
}
(); // Clean up the page context
// Method B(nextPageNum, nextPageSize);
List<Order> orders = ();
(); // Clean up the page context

4. Pagination settings affect other queries

becausePageHelperis based on thread local variables (ThreadLocal) to manage the paging context, so if the paging settings are passed between methods but not cleaned up, subsequent queries may mistakenly inherit the paging settings, even if the queries themselves do not require paging.

Solution:

• Make sure to clean the paging context after the paging query.
• If the query does not set the paging, confirm that there is no implicit inheritance of the paging settings.

// explicitly call startPage before paging query(pageNum, pageSize);
List<User> users = ();

// Clean up the page context();

// Non-paged queryList<Order> orders = (); // This should no longer be affected by paging

Summarize

1. Clean up the page context: Called after each paging query()To clean up the pagination context and avoid affecting subsequent queries.
2. Make sure the paging settings take effect: Before each query, make sure that the paging settings have been called correctlystartPage。
3. Avoid conditional judgment affecting pagination: If the conditional judgment causes SQL query to not be executed, ensure that the paging context is cleaned up and avoid affecting subsequent queries.

In this way, you can ensure that the paging logic does not accidentally affect subsequent queries, even if some queries are not executed due to conditional judgments.

These are just personal experience. I hope you can give me a reference and I hope you can support me more.