After reading the interesting math book, I began to fill the pit with technology

Usually in business, data need to be paging query, so that each SQL statement is limited, a lot of repeated code, and each time you need to calculate the offset in the code, slightly troublesome.

MyBatis (MyBatis, MyBatis, MyBatis, MyBatis, MyBatis, MyBatis, MyBatis)

Simple 🌰

References are made in the project

Most JavaWeb projects use maven and gradle for project management

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>The latest version</version>
</dependency>... the compile group: / / https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper 'com.github. Pagehelper ', name: 'pageHelper ', version: '5.1.4'Copy the code

Configure in Mybatis -config or Spring

mybatis:
<plugins>
    <! -- com.github. Pagehelper specifies the package name of pageHelper -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <! Use the following method to configure the parameters:
        <property name="param1" value="value1"/>
	</plugin>
</plugins>

spring:
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <! -- Automatically scan mapping.xml file -->
        <property name="mapperLocations" value="classpath:mapping/*.xml">
        </property>
        <! -- Configure paging plug-in -->
        <property name="plugins">
            <array>
                <bean class="com.github.pagehelper.PageInterceptor">
                    <property name="properties">
                        <value>
                            reasonable=true
                        </value>
                    </property>
                </bean>
            </array>
        </property>
    </bean>
Copy the code

I used the second configuration method (Spring), and more detailed configuration instructions can be found in the Github documentation.

Mapper query statement

For simplicity, single-table queries are used

<select id="selectByUserId" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List" />
    FROM course_user
    WHERE user_id = #{userId, jdbcType=INTEGER}
</select>
Copy the code

Code using

The second implementation recommended by the official website is used in the code when querying,

// With this method, the next first select method is paginated
PageHelper.startPage(1.5);
List<CourseUser> list = mapper.selectByUserId(userId);
// Select * from Page
      
       ; // select * from Page
       
      
// Wrap the result with PageInfo
PageInfo page = new PageInfo(list);
// Get the total number
int count = page.getTotal();
Copy the code

Paging plug-in, source code implementation principle

This plugin supports many databases, including Oracle, MySQL, and mongdb. MySQL is commonly used, and MySQL is also used as the persistence layer in test projects. The pagination plugin can be adapted to MySQL through the autoDialect.

MyBatis plug-in Interceptor for SQL rewrite, can be seen from the source code

Statistics of the number

Interceptors entry: com. Making. Pagehelper. PageInterceptor# intercept

For statistical quantity method: com. Making. Pagehelper) parser. CountSqlParser# getSmartCountSql (Java. Lang. String, Java. Lang. String)

/** * get the intelligent countSql **@param sql
 * @paramName Column name, default 0 *@return* /
public String getSmartCountSql(String sql, String name) {
    / / parse SQL
    Statement stmt = null;
    // Use comment prefixes when special SQL does not need to remove order BY
    if(sql.indexOf(KEEP_ORDERBY) >= 0) {return getSimpleCountSql(sql);
    }
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (Throwable e) {
        // Return the count statement in the normal way if it cannot be parsed
        return getSimpleCountSql(sql);
    }
    Select select = (Select) stmt;
    SelectBody selectBody = select.getSelectBody();
    try {
        // Handle body- go to order by
        processSelectBody(selectBody);
    } catch (Exception e) {
        // Do not remove order BY when SQL contains group by
        return getSimpleCountSql(sql);
    }
    // handle with- order by
    processWithItemsList(select.getWithItemsList());
    // process the query as count
    sqlToCount(select, name);
    String result = select.toString();
    return result;
}
Copy the code

With this method, remove the args parameter, then change it to count(*), and change it to SQL for the number of counts.

Paging query

Specific call methods: com. Making. Pagehelper. The dialect. Helper. MySqlDialect# getPageSql

@Override
public String getPageSql(String sql, Page page, CacheKey pageKey) {
    StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
    sqlBuilder.append(sql);
    // Check if it is the first page. If it is the first page, do not set the offset
    if (page.getStartRow() == 0) {
        sqlBuilder.append(" LIMIT ? ");
    } else {
        sqlBuilder.append(" LIMIT ?, ? ");
    }
    pageKey.update(page.getPageSize());
    return sqlBuilder.toString();
}
Copy the code

From here, you can see that instead of paging through memory, the plug-in does physical paging by modifying the SQL.

conclusion

This plugin has no intrusive code, and the implementation idea is very elegant, reflecting the idea of the interceptor. The final result is not posted, generally the query of total and paging data is enough.

The resources

1 Paging plugin address Github