Analysis of the usage example of pagination query in springboot project

Spring Boot pagination query detailed explanation

This article explains in detail two mainstream paging solutions in Spring Boot:MyBatis + PageHelperandMyBatis PlusImplementation methods, principles, advantages and disadvantages and practical application scenarios. The content covers configuration, code examples, notes and comparison summary.

1. Overview of pagination query

Pagination query is the core requirement for handling large data sets in web development, and its essence isLoad data on demand, avoid performance problems caused by returning all data at once. The implementation methods are usually divided into two categories:

• Physical pagination: Directly limit the query scope through SQL (such asLIMIT）。
• Logical pagination: First query the full amount of data, and then intercept the paging in memory (not recommended).

Commonly used in Spring BootPhysical pagination, relying on the plug-in mechanism of MyBatis to dynamically modify SQL.

2. MyBatis + PageHelper paging scheme

1. Core dependency

<dependency>
    <groupId></groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.6</version>
</dependency>

2. Configuration parameters ()

pagehelper:
  helper-dialect: mysql       # Specify the database dialect (mysql/oracle/postgresql)  reasonable: true            # Rationalize paging parameters (automatically correct when out of range)  support-methods-arguments: true  # Support interface parameter passing paging

3. Pagination implementation code

Service layer

public PageInfo<User> getUsersByPage(int pageNum, int pageSize) {
    try {
        // Open paging: Take effect on the first query that comes into effect        (pageNum, pageSize);
        List<User> users = ();
        return new PageInfo<>(users);  // Contains information such as total number of characters, total pages, etc.    } finally {
        ();  // Clean up ThreadLocal    }
}

Mapper interface

@Select("SELECT * FROM user WHERE status = 1")
List<User> selectAll();

4. Paging principle

• ThreadLocal Passing parameters：()Save the paging parameters to the current threadThreadLocal。
• Interceptor rewrite SQL: MyBatis interceptor automatic splicingLIMIT offset, pageSize。
• Automatically execute COUNT query: After generating paging data, the total number of records will be automatically queried.

5. Things to note

• Call order：startPage()The query method must be closely followed, otherwise the pagination will not take effect.
• Thread safety: Asynchronous or multi-threaded scenarios require manual transmission of paging parameters.
• Performance optimization: Complex SQL customizable COUNT queries:

@Select("SELECT COUNT(*) FROM user WHERE status = 1")
Long countUsers();
// Specify a custom COUNT method(1, 10).count(true).setCountSql("countUsers");

3. MyBatis Plus paging solution

1. Core dependency

<dependency>
    <groupId></groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

2. Paging plugin configuration

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // Add a pagination interceptor and specify the database type        (new PaginationInnerInterceptor());
        return interceptor;
    }
}

3. Pagination implementation code

Service layer

public IPage<User> getUsersByPage(int pageNum, int pageSize) {
    // Create a paging object    Page<User> page = new Page<>(pageNum, pageSize);
    // Execute paging query (automatically process SQL)    return (page, new QueryWrapper<User>().eq("status", 1));
}

Mapper interface

public interface UserMapper extends BaseMapper<User> {
    // Inherit BaseMapper provides pagination methods by default}

4. Paging principle

• Built-in paging interceptor: Automatic identificationIPageParameters, rewrite SQL.
• Unified paging model:passIPage<T>Interface encapsulation paging parameters and results.
• Multi-database support:according toDbTypeGenerate different pagination SQL (such as Oracle's ROWNUM).

5. Things to note

• Wrapper Conditions: Pagination needs to be combinedQueryWrapperOr custom SQL.
• Performance optimization: Big data paging requires manual optimization COUNT query:

// Turn off automatic COUNT queryPage<User> page = new Page<>(pageNum, pageSize, false);
List<User> users = (page, wrapper);
// Manually execute COUNT query((wrapper));

4. Comparative summary

V. Best Practices and Frequently Asked Questions

1. Best Practices

• Simple pagination: Prioritize MyBatis Plus to reduce the amount of code.
• Complex SQL: Select PageHelper to control SQL flexibly.
• Performance optimization：
  • Add index (such asCREATE INDEX idx_status ON user(status)）。
  • avoidSELECT *, query only the necessary fields.
  • Use of large data pagingCursor pagingorDelayed association。

2. FAQ

Q1: The pagination does not take effect

• reason：startPage()Incorrect call order or interceptor is not configured.
• solve:make surestartPage()Call before querying the method to check dependencies and configuration.

Q2: Total number of digits is 0

• reason: COUNT query does not match the criteria or SQL error.
• solve: Manually specify the COUNT method or check query conditions.

Q3: Low performance

optimization：

-- original SQL（Poor performance）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- optimization SQL（Delayed association）
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 10;

6. Appendix: Complete code example

PageHelper Complete Example

// Service
public PageInfo<User> getUsers(int pageNum, int pageSize) {
    try {
        (pageNum, pageSize);
        List<User> users = ("active");
        return new PageInfo<>(users);
    } finally {
        ();
    }
}
// Mapper
@Select("SELECT * FROM user WHERE status = #{status}")
List<User> selectByCondition(String status);

MyBatis Plus complete example

// Service
public IPage<User> getUsers(int pageNum, int pageSize) {
    Page<User> page = new Page<>(pageNum, pageSize);
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    ("status", "active");
    return (page, wrapper);
}

This is the article about the use and analysis of pagination query in springboot project. For more related contents of pagination query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!