20. Use and principle of PageHelper paging plug-in

Keywords: Programming Database SQL MySQL Mybatis

1. Logical paging and physical paging

Logical paging: also called memory paging. Logical paging depends on the code written by the programmer. What the database returns is not the paging result, but all the data, which is then passed by the programmer
         The code obtains the paging data, takes out all the data, first stores it in memory, then takes out the required data. The common operation is to query all the data from the database and store it at one time
         To the List collection, because the List collection is ordered, then get the data of the specified range according to the index
Physical paging: physical paging depends on a physical entity, which is the database. That is, paging is performed in the database when querying the database. For example, MySQL database provides
         With the limit keyword, programmers only need to write SQL statements with the limit keyword, and the database returns paging results
 Mybatis' RowBounds belongs to memory paging, and pageHelper belongs to physical paging

2. PageHelper use

// 1. Introduce dependency. When using spring boot configuration, there is an automatic configuration class PageHelperAutoConfiguration, which will automatically configure an Interceptor

// 2. Use in code
PageHelper.startPage(pageNo, pageSize, orderString);
//If you do not use the SpringBoot branch, you can also manually use the- config.xml Configure a PageInterceptor in
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql"/>
            <! -- this parameter defau lt s to false -- >
            <! -- when set to true, the first parameter offset of RowBounds will be used as pageNum page number -- >
            <! -- same effect as pageNum in startPage -- >
            <property name="offsetAsPageNum" value="true"/>
            <! -- this parameter defau lt s to false -- >
            <! -- when set to true, use RowBounds page to perform count query -- >
            <property name="rowBoundsWithCount" value="false"/>
            <! -- when set to true, if pageSize=0 or RowBounds.limit  =0 will find all the resu lt s -- >
            <! -- (equivalent to no paging query, but the returned resu lt is still Page type) -- >
            <property name="pageSizeZero" value="true"/>
            <! -- available in version 3.3.0 - Page parameter rationalization, default false disable -- >
            <! -- when rationalization is enabled, the first page will be queried if pagenum < 1, and the last page will be queried if pagenum > pages -- >
            <! -- when rationalization is disabled, if pagenum < 1 or pagenum > pages, null data will be returned -- >
            <property name="reasonable" value="false"/>
            <! -- version 3.5.0 is available - in order to support the startPage(Object params) method -- >
            <! -- added a 'params' parameter to configure parameter mapping, which is used to take values from Map or ServletRequest -- >
            <! -- pageNum,pageSize,count,pageSizeZero,reasonable can be configured. Defau lt values are used if no mapping is configured -- >
            <! -- do not copy the configuration without understanding the meaning -- >
            <property name="params" value="pageNum=start;pageSize=limit;"/>

3. How PageHelper works

// The prerequisite for creating this bean is MybatisAutoConfiguration
public class PageHelperAutoConfiguration {

    private List<SqlSessionFactory> sqlSessionFactoryList;

    private PageHelperProperties properties;

     * Accept additional properties of paging plug-ins
     * @return
    @ConfigurationProperties(prefix = PageHelperProperties.PAGEHELPER_PREFIX)
    public Properties pageHelperProperties() {
        return new Properties();

    public void addPageInterceptor() {
        PageInterceptor interceptor = new PageInterceptor();
        Properties properties = new Properties();
        //First, put the properties configured in general mode
        //When the special configuration is put in, because close conn uses the above method, the attribute name is close conn instead of closeConn, so an additional step is needed
        for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
	    // Manually add a PageInterceptor to Mybatis's plug-in chain

// 1. Setting
PageHelper.startPage(pageNo, pageSize, orderString);

// 2. Query SQL

// 3. Querying SQL will finally come here. The invoke method of SqlSessionTemplate
private class SqlSessionInterceptor implements InvocationHandler {

    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
	// After the sqlSession is created, it is found that the interceptor in its executor has a PageInterceptor, so let's take a look at its creation process
        SqlSession sqlSession = getSqlSession(
        try {
            Object result = method.invoke(sqlSession, args);
            return result;

// getSqlSession go in to this step and start to create sqlSession
session = sessionFactory.openSession(executorType);

// Go on to this step
final Executor executor = configuration.newExecutor(tx, execType);
return new DefaultSqlSession(configuration, executor, autoCommit);

// configuration.newExecutor(tx, execType) to this step
executor = (Executor) interceptorChain.pluginAll(executor);

// Go to this step to create a dynamic proxy object
public static Object wrap(Object target, Interceptor interceptor) {
    Map<Class<?>, Set<Method>> signatureMap = getSignatureMap(interceptor);
    Class<?> type = target.getClass();
    Class<?>[] interfaces = getAllInterfaces(type, signatureMap);
    if (interfaces.length > 0) {
        return Proxy.newProxyInstance(type.getClassLoader(),
                                      new Plugin(target, interceptor, signatureMap));
    return target;

// Execute query
Object result = method.invoke(sqlSession, args);

public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
     try {
         Set<Method> methods = signatureMap.get(method.getDeclaringClass());
         if (methods != null && methods.contains(method)) {
	     // This interceptor is a PageInterceptor. When you go here, you can perform paging logic. It will give your SQL assembly paging parameters
             return interceptor.intercept(new Invocation(target, method, args));
         return method.invoke(target, args);

Posted by ChetUbetcha on Mon, 25 May 2020 20:53:31 -0700