MyBatis paging plugin PageHelper in-depth analysis and practice guide

1. Why do I need a paging plugin?

In database operations, paging queries are one of the most common requirements. There are usually two traditional pagination methods:

• Memory paging: Query all data at once and then paging it in memory
• SQL pagination: Implement pagination through SQL's LIMIT, ROWNUM and other syntax

The first method will cause memory overflow and performance problems when the data is large, and the second method, although it is efficient, requires writing complex and database-specific SQL statements. As an excellent ORM framework, MyBatis does not provide a unified pagination solution itself, which is the background of the birth of PageHelper.

2. Introduction to PageHelper

PageHelper is a MyBatis paging plug-in developed by domestic developers, with the following features:

• Supports multiple databases (MySQL, Oracle, PostgreSQL, etc.)
• Easy to use, just set the paging parameters before querying
• Physical paging to avoid memory overflow
• Supports multiple paging methods
• Open source free, active community

3. PageHelper integration and configuration

3.1 Add dependencies

The Maven project adds the following dependencies:

<dependency>
    <groupId></groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.3.2</version> <!-- Use the latest version -->
</dependency>

3.2 MyBatis configuration

Add plugins in MyBatis configuration file:

<plugins>
    <plugin interceptor="">
        <!-- Configure dialect，Default will automatically detect -->
        <property name="helperDialect" value="mysql"/>
        <!-- Rationalization of pagination，page number<=0When querying the first page，>=Query the last page when the largest page -->
        <property name="reasonable" value="true"/>
        <!-- SupportedMapperInterface parameters to pass paging parameters -->
        <property name="supportMethodsArguments" value="true"/>
        <!-- Always returnPageInfotype,Check by defaultPageParameters are no longer processed -->
        <property name="returnPageInfo" value="check"/>
    </plugin>
</plugins>

Spring Boot projects can be configured in:

# PageHelper Configuration-dialect=mysql
=true
-methods-arguments=true
=count=countSql

4. Basic usage method

4.1 The easiest way to use it

// Set paging parameters and query page 1, 10 items per page(1, 10);
// The first select method that follows will be paginatedList<User> users = ();
// Use PageInfo to wrap the resultsPageInfo<User> pageInfo = new PageInfo<>(users);

4.2 PageInfo object analysis

PageInfo contains rich pagination information:

// Current page();
// Number of per page();
// Number of current pages();
// Total records();
// Total page count();
// Result Set();
// Is it the first page();
// Is it the last page();

4.3 More usage methods

Parameter call:

// Interface methodList<User> selectByPage(@Param("name") String name, 
                       @Param("pageNum") int pageNum,
                       @Param("pageSize") int pageSize);

// Call method("Zhang San", 1, 10);

Use the RowBounds parameter:

RowBounds rowBounds = new RowBounds(0, 10);
List<User> users = (null, rowBounds);

5. Advanced Features and Best Practices

5.1 Paging Plugin Principle

PageHelper is implemented through MyBatis' Interceptor mechanism, and dynamically modify SQL statements before SQL execution:

1. Intercept Executor query method
2. Get the paging parameters
3. Rewrite SQL according to database dialect
4. Execute COUNT query to get the total number
5. Execute pagination SQL to get the result set
6. Encapsulation of paging results

5.2 Multi-table association query optimization

For complex SQL, PageHelper's COUNT query may be slow, and you can customize the COUNT query:

<select  resultMap="userWithRoleMap">
    select u.*, r.role_name from user u left join role r on u.role_id = 
</select>

<!-- CustomizecountQuery -->
<select  resultType="long">
    select count(1) from user u
</select>

5.3 Paging performance optimization

1. Set pageSize reasonably: Avoid excessive amount of data on a single page
2. Query only the necessary fields: Avoid SELECT *
3. Using indexes: Ensure that the WHERE conditions of paging query are indexed
4. Cache COUNT results: The total number of data that changes infrequently can be cached

5.4 Special database support

For different databases, PageHelper will automatically use different paging methods:

• MySQL: LIMIT
• Oracle: ROWNUM
• PostgreSQL: LIMIT OFFSET
• SQLServer: TOP

6. FAQs and Solutions

6.1 The pagination does not take effect

Possible reasons:

• No query was executed immediately after ()
• Methods are processed in advance by other interceptors
• Incorrect configuration

Solution:

// Make sure the call mode is correct(1, 10);
List<User> list = ();

6.2 Sorting issues

// Incorrect sorting method(1, 10);
("id desc"); // This line will not take effect
// Correct sorting(1, 10, "id desc");

6.3 Multi-data source configuration

For multi-data source systems, you need to configure PageHelper separately for each SqlSessionFactory:

@Bean
@ConfigurationProperties(prefix = "pagehelper")
public Properties pageHelperProperties() {
    return new Properties();
}

@Bean(name = "pageHelperInterceptor")
public PageInterceptor pageHelperInterceptor() {
    PageInterceptor pageInterceptor = new PageInterceptor();
    (pageHelperProperties());
    return pageInterceptor;
}

// Add plugin for each SqlSessionFactory@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
    (dataSource);
    // Add other configurations...    (new Interceptor[]{pageHelperInterceptor()});
    return ();
}

7. Comparison of PageHelper and MyBatis-Plus pagination

Select suggestions:

• If the project already uses MyBatis-Plus, it can be used directly with its pagination function
• PageHelper is a better choice if using native MyBatis

8. Summary

PageHelper is the most popular paging plug-in in the MyBatis ecosystem, and has the characteristics of simple and easy to use, powerful functions and excellent performance. Through the introduction of this article, you should have mastered it:

1. Basic principles and configuration methods of PageHelper
2. Various ways to use paging
3. Performance optimization tips
4. Frequently Asked Questions

In actual projects, the rational use of PageHelper can greatly improve development efficiency while ensuring the performance of paging query. It is recommended to choose the right configuration according to project needs and follow best practices to get the best experience.

The above is the detailed content of the in-depth analysis and practice guide for MyBatis paging plug-in PageHelper. For more information about MyBatis paging plug-in PageHelper, please pay attention to my other related articles!