MyBaits is an excellent open-source persistence layer framework, with SQL statements separated from code, configuration oriented programming, good support for complex data mapping and dynamic SQL; MyBaits is an excellent persistence layer framework that supports customized SQL, stored procedures and advanced mapping. MyBatis avoids almost all JDBC code and manual setting of parameters and getting result sets. MyBatis can use simple XML or annotation for configuration and native Map to Map interface and Java POJOs(Plain Old Java Objects) to records in database.
MyBatis is usually used for paging in the following forms:

1. Logical paging: RowBounds

2. Physical paging: use LIMIT in SQL or use a third-party plug-in (PageHelper, etc.)

   Environment and introduction

   Import core dependencies:

 <!-- SpringBoot MyBatis starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.2.2</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

For the steps of setting up the environment, please refer to: Common starter s in SpringBoot Enterprises
This time, we mainly implement an interface org.apache.ibatis.plugin.Interceptor. There are three methods in the interface:

Object intercept(Invocation invocation) throws Throwable;

Object plugin(Object target);

void setProperties(Properties properties);

1. The intercept method is the main intercept execution method.
2. The plugin method determines whether the current object needs to generate a proxy object.

3. setProperties sets the runtime mybatis core configuration parameter method.
   Mybatis's interceptor implementation mechanism uses JDK's InvocationHandler. When we call the objects of parameterhandler, resultsethandler, statementhandler and executor, we actually use the object of Plugin, which implements the InvocationHandler interface.
   When we call the objects of parameterhandler, resultsethandler, statementhandler and executor,
   In fact, we use the object of Plugin, which implements the InvocationHandler interface.

   Custom paging implementation

   Create Pager entity object for paging. The entity content is as follows:

@Data
@ToString
public class Pager {
    /*Current page*/
    private int page;
    /*Size per page*/
    private int size;
    /*General record*/
    private long total;
    /*PageCount*/
    private int totalPage;
    /*Custom paging sql*/
    private String customSQL;
    /*Paging execution time*/
    private long executeTime;
}

The customSQL variable here is user-defined paging SQL. Many times, it is thought that SQL is too complex and associated with N tables. Obtaining N fields will cause too slow query time. The main purpose of adding this field is not to use the default paging quantity statistics in some complex SQL. You can remove the unnecessary fields according to SQL, and execute the SQL after connecting the unnecessary tables. Statistics of the number of pages.
Define the paging processing interface PagerHandler, as follows:

public interface PagerHandler {
    /**
     * Get sql execution parameters
     * @param boundSql
     * @return
     */
    public Pager getPager(BoundSql boundSql);

    /**
     * Execution paging
     *
     * @param pager
     * @param boundSql
     * @param connection
     * @param metaObject
     * @return
     * @throws SQLException
     */
    public Pager executer(Pager pager, BoundSql boundSql, Connection connection, MetaObject metaObject) throws SQLException;
}

Create MyBats interceptor to implement PagerMyBatisInterceptor, which implements the interface org.apache.ibatis.plugin.Interceptor and PagerHandler defined by ourselves, and rewrites the methods in the interface.
We also need to tell MyBatis where to intercept. Use @ Intercepts to mark:

@Intercepts(value = {
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})

It mainly intercepts prepare compilation parameter method in StatementHandler. The method needs to pass in parameter types of Connection.class and integer.class. In mybatis version 3.4.1, there is only one Connection.
Define some constants in the class:

private final Logger log = LoggerFactory.getLogger(PagerMyBatisInterceptor.class);

private final int CONNECTION_INDEX = 0; //Connection parameter index

private final DefaultReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory();//Default reflection factory

private final String DELEGATE_MAPPED_STATEMENT = "delegate.mappedStatement";//Reflection value acquisition path

private final String DELEGATE_PARAMETER_HANDLER = "delegate.parameterHandler";//Reflection value acquisition path

Specific interceptor content:

@Component
@Intercepts(value = {
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class PagerMyBatisInterceptor implements PagerHandler, Interceptor {

    private final Logger log = LoggerFactory.getLogger(PagerMyBatisInterceptor.class);

    private final int CONNECTION_INDEX = 0;

    private final DefaultReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory();

    private final String DELEGATE_MAPPED_STATEMENT = "delegate.mappedStatement";

    private final String DELEGATE_PARAMETER_HANDLER = "delegate.parameterHandler";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        //Database connection
        Connection connection = (Connection) args[CONNECTION_INDEX];
        //Responsible for handling the Statement interaction between Mybatis and JDBC
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //MetaObject is an object provided by Mybatis for convenient and elegant access to object properties. It can simplify the code without any try/catch exceptions. At the same time, it supports the operation of three types of objects: JavaBean, Collection and Map.
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY);
        //MappedStatement represents an SQL in XML
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue(DELEGATE_MAPPED_STATEMENT);
        //SqlCommandType represents SQL type
        SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
        //BoundSql is the object in which the Sql statement is saved
        BoundSql boundSql = statementHandler.getBoundSql();
        //Paging object
        Pager pager = getPager(boundSql);
        if (sqlCommandType.compareTo(SqlCommandType.SELECT) == 0 && pager != null) {
            executer(pager, boundSql, connection, metaObject);
            //Execution query
            int left = (pager.getPage() - 1) * pager.getSize();
            int right = pager.getSize();
            String rewriteSql = boundSql.getSql() + " LIMIT " + left + "," + right;
            metaObject.setValue("boundSql.sql", rewriteSql);
        }
        long startTime = System.currentTimeMillis();
        Object proceed = invocation.proceed();
        long endTime = System.currentTimeMillis();
        log.info("SQL TYPE [{}] , SQL EXECUTE TIME [{}] SQL:\n{}", sqlCommandType, startTime - endTime, boundSql.getSql().toUpperCase());
        return proceed;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        //TODO set mybatis parameter
    }

    @Override
    public Pager getPager(BoundSql boundSql) {
        Object parameterObject = boundSql.getParameterObject();
        if (parameterObject instanceof Pager) {
            return (Pager) parameterObject;
        } else if (parameterObject instanceof Map) {
            Map<String, Object> paramMap = (Map<String, Object>) parameterObject;
            Iterator<String> keys = paramMap.keySet().iterator();
            while (keys.hasNext()) {
                String key = keys.next();
                Object obj = paramMap.get(key);
                if (obj instanceof Pager) {
                    return (Pager) obj;
                }
            }

        }
        return null;
    }

    @Override
    public Pager executer(Pager pager, BoundSql boundSql, Connection connection, MetaObject metaObject) throws SQLException {
        if (pager.getPage() == 0) {
            pager.setPage(0);
        }
        if (pager.getSize() == 0) {
            pager.setSize(0);
        }
        if (pager.getCustomSQL() == null) {
            //If you do not define paging SQL, the default brute paging is used
            pager.setCustomSQL("SELECT COUNT(1) FROM (" + boundSql.getSql() + " ) tmp_table");
        }
        // precompile
        PreparedStatement prepareStatement = connection.prepareStatement(pager.getCustomSQL());
        // Precompile execution
        ParameterHandler parameterHandler = (ParameterHandler) metaObject.getValue(DELEGATE_PARAMETER_HANDLER);
        parameterHandler.setParameters(prepareStatement); // Setting parameters for sql statements
        long startTime = System.currentTimeMillis();
        ResultSet resultSet = prepareStatement.executeQuery();
        long endTime = System.currentTimeMillis();
        log.info("sql execute time {} sql:\n{}", startTime - endTime, pager.getCustomSQL().toUpperCase());
        if (resultSet.next()) {
            long total = (long) resultSet.getObject(1);// Total number of records
            int totalPageNum = (int) ((total + pager.getSize() - 1) / pager.getSize());
            pager.setTotal(total);
            pager.setTotalPage(totalPageNum);
            pager.setExecuteTime(startTime - endTime);
        }
        return pager;
    }
}

Get the pager object through the getPager method. If it is a Map parameter, the first one will be obtained in the BoundSql object through reference, and then the value in the pager will be obtained for calculation. The total number of records and pages will be returned through the reference object.
In spring boot, if you need to make the interceptor effective, you only need to use @ Component in the type to give the class to Spring IOC for management. The order of interceptors is as follows:
There are interceptors pagermybatisinterpector and OneInterceptor. To be the second interceptor, you only need to mark @ ConditionalOnBean(OneInterceptor) on the class, and then instantiate the second interceptor after the first interceptor is instantiated.

How to use Pager

 List<Map<String, Object>> selectUser(Pager pager);
 List<Map<String, Object>> selectUser(Map<String,Object> paramMap);

Create a Pager object to pass in.
Source address of this article: https://github.com/45025266/open-double/tree/master/spring-boot/custom-mybatis-pager