mybatis分页实现总结

1.mybatis拦截器相关知识

1.作用

mybatis的拦截器是mybatis提供的一个拓展机制，允许用户在使用时根据各自的需求对sql执行的各个阶段进行干预。比较常见的如对执行的sql进行监控，排查sql的执行时间，对sql进行拦截拼接需要的场景，对最终结果进行如加密等处理。

2.拦截器分类

mybatis提供了四种拦截器供使用,按拦截的执行顺序展示
Executor->StatementHandler->ParameterHandler->ResultHandler

/*
用途：拦截执行器的方法，
可以用来修改执行sql的操作（query，update）
对事务进行操作(commit,rollback，getTransaction),
开启缓存(createCacaheKey,isCached)，看方法上注释
*/
public interface Executor {
    ResultHandler NO_RESULT_HANDLER = null;
    
	// 执行insert update delete操作
    int update(MappedStatement var1, Object var2) throws SQLException;
	
	// 执行query操作
    <E> List<E> query(MappedStatement var1, Object var2, RowBounds var3, ResultHandler var4, CacheKey var5, BoundSql var6) throws SQLException;

    <E> List<E> query(MappedStatement var1, Object var2, RowBounds var3, ResultHandler var4) throws SQLException;

    <E> Cursor<E> queryCursor(MappedStatement var1, Object var2, RowBounds var3) throws SQLException;

    List<BatchResult> flushStatements() throws SQLException;

	// 提交事务
    void commit(boolean var1) throws SQLException;
	
	// 回滚事务
    void rollback(boolean var1) throws SQLException;

    CacheKey createCacheKey(MappedStatement var1, Object var2, RowBounds var3, BoundSql var4);

    boolean isCached(MappedStatement var1, CacheKey var2);

    void clearLocalCache();

    void deferLoad(MappedStatement var1, MetaObject var2, String var3, CacheKey var4, Class<?> var5);
	
	// 获取事务
    Transaction getTransaction();

	// 关闭事务
    void close(boolean var1);
	
	// 判断事务是否关闭
    boolean isClosed();

    void setExecutorWrapper(Executor var1);
}

/**
用途: 拦截sql执行的语句
*/
public interface StatementHandler {
	// sql预编译拦截，在这个阶段可以修改sql执行的语句
    Statement prepare(Connection var1, Integer var2) throws SQLException;
	
	// 设置参数时拦截
    void parameterize(Statement var1) throws SQLException;

    void batch(Statement var1) throws SQLException;
	
	// 进行insert，update,delete的sql执行前进行拦截
    int update(Statement var1) throws SQLException;
    
	// 进行query的sql执行前进行拦截
    <E> List<E> query(Statement var1, ResultHandler var2) throws SQLException;

    <E> Cursor<E> queryCursor(Statement var1) throws SQLException;

    BoundSql getBoundSql();

    ParameterHandler getParameterHandler();
}

/**
用途： 拦截sql语句的参数设置
*/
public interface ParameterHandler {
	// 获取设置的参数(注意使用这个的时候最好和@param注解合用指定具体名，不然只能按顺序去获取，没有具体名)
    Object getParameterObject();
	
	// 设置sql的参数
    void setParameters(PreparedStatement var1) throws SQLException;
}

/**
用途：对执行完后返回的结果进行拦截
*/
public interface ResultSetHandler {
	// 最常用的返回拦截
    <E> List<E> handleResultSets(Statement var1) throws SQLException;

    <E> Cursor<E> handleCursorResultSets(Statement var1) throws SQLException;

    void handleOutputParameters(CallableStatement var1) throws SQLException;
}

2.定义拦截器的相关注解@Intercepts与@Signature

// 用于指定该拦截器是拦截哪个环节的注解，可以配置多个
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
public @interface Intercepts {
    Signature[] value();
}

// 指定具体的拦截环节
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({})
public @interface Signature {
	// 即上面的四种拦截器：Executor,StatementHandler,ParameterHandler,ResultHandler 
    Class<?> type();
	
	// 上面四种拦截器中的方法名，如指定的是StatementHandler，你需要具体到方法上，可以选择填写query
    String method();

	// method指定方法上的形参，如按query将两个参数的类对象写好即可
    Class<?>[] args();
}

2.使用SQL_CALC_FOUND_ROWS和found_rows()进行分页

1.直接按要求实现

a.在配置mysql的url处开启返回多个结果集：allowMultiQueries=true

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root
    url: jdbc:mysql://192.168.175.155:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowMultiQueries=true

b.编写mapper

// 注意要接收多个结果，所以需要List不指定泛型
List select2(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

c.编写xml的sql

	<!-- 实体和字段的映射配置 -->
    <resultMap id="BaseResultMap" type="com.example.demo.pagehelper.User">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="age" jdbcType="BIGINT" property="age"/>
    </resultMap>
	<!-- 总行数的配置 -->
    <resultMap id="recordCounts" type="java.lang.Integer">
        <result column="recordCounts" jdbcType="BIGINT"/>
    </resultMap>

    <select id="select2" resultMap="BaseResultMap, recordCounts">
        select SQL_CALC_FOUND_ROWS * from user limit #{pageNum}, #{pageSize};
        SELECT found_rows() AS recordCounts;
    </select>

d.返回结果的处理

  List<Object> lists = userMapper.select2(1,3);
  // 获取分页的数据
  List<User> users = (List<User>) lists.get(0);
  // 获取查询的总数
  List<Integer> nums = (List<Integer>) lists.get(1);
  Integer count = nums.get(0);

总结：
1.sql在写的时候都需要拼接SQL_CALC_FOUND_ROWS和SELECT found_rows() AS recordCounts;
2.处理结果时很麻烦一直强转再分别获取

2.使用自定义拦截器对上述实现进行一定程度优化(思路仅供参考)

a.定义两个拦截器：一个拦截sql执行,一个拦截结果处理

/**
 * @description: 在准备阶段对sql进行拦截，并处理结果
 * @author: zengwenbo
 * @date: 2024/4/11 18:22
 */
@Intercepts(value = {
        @Signature(type = StatementHandler.class, method="prepare", args = {Connection.class, Integer.class} )
})
@Component
public class PagingInterceptor implements Interceptor {
    private static final String PAGE_NUM = "pageNum";
    private static final String PAGE_SIZE = "pageSize";
    private static final String SQL_CALC_FOUND_ROWS = "sql_calc_found_rows";
    private static final String FOUND_ROWS = "found_rows()";
    private static final String SEMICOLON = ";";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler target = (StatementHandler) invocation.getTarget();
        // 借助metaObject来修改执行的sql
        MetaObject metaObject= SystemMetaObject.forObject(target);
        BoundSql boundSql = target.getBoundSql();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        String sql = boundSql.getSql();
        // 只处理查询语句
        if (sql != null && sql.toLowerCase().trim().startsWith("select ")) {
            // 获取传递的参数所有名称
            List<String> paramsName = parameterMappings.stream()
                    .filter(item -> ParameterMode.IN.equals(item.getMode()))
                    .map(ParameterMapping::getProperty)
                    .collect(Collectors.toList());

            // 根据参数名称里面是否有指定两个参数来判断是否需要分页
            if (paramsName.contains(PAGE_NUM) && paramsName.contains(PAGE_SIZE)) {
                // 需要分页则对sql进行处理
                metaObject.setValue("delegate.boundSql.sql", dealSql(sql));
            } else {
                metaObject.setValue("delegate.boundSql.sql", sql);
            }
        }
        return invocation.proceed();
    }

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

    @Override
    public void setProperties(Properties properties) {

    }

    /**
     * 拼接需要的分页sql语句
     * @param oldSql
     * @return
     */
    private String dealSql(String oldSql) {
        if (oldSql.toLowerCase().contains(SQL_CALC_FOUND_ROWS) || oldSql.toLowerCase().contains(FOUND_ROWS)) {
            return oldSql;
        }
        oldSql = oldSql.toLowerCase().replace("select ", "select SQL_CALC_FOUND_ROWS ");
        if(!oldSql.trim().endsWith(SEMICOLON)) {
            oldSql = oldSql.concat(SEMICOLON);
        }
        return oldSql.concat("select found_rows() AS recordCounts;");
    }
}

/**
 * @description: 对分页结果数据进行处理
 * @author: zengwenbo
 * @date: 2024/4/12 18:37
 */
@Intercepts(value = {
        @Signature(type = ResultSetHandler.class, method="handleResultSets", args = {Statement.class} )
})
@Component
public class PagingResultInterceptor implements Interceptor {
    private static final String SQL_CALC_FOUND_ROWS = "sql_calc_found_rows";
    private static final String FOUND_ROWS = "found_rows()";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object proceed = invocation.proceed();
        DefaultResultSetHandler target = (DefaultResultSetHandler) invocation.getTarget();
        // 借助metaObject来获取执行的sql
        MetaObject metaObject= SystemMetaObject.forObject(target);
        BoundSql boundSql = (BoundSql) metaObject.getValue("boundSql");
        String sql = boundSql.getSql();
        // 执行的是分页，则需要封装返回需要的对象
        if (null != sql && sql.toLowerCase().contains(SQL_CALC_FOUND_ROWS) && sql.toLowerCase().contains(FOUND_ROWS)) {
            if (proceed instanceof List) {
                List<Object> result = (List) proceed;
                if (result.size() == 2) {
                    Object res1 = result.get(0);
                    Object res2 = result.get(1);
                    if (res1 instanceof List && res2 instanceof List) {
                        List<Object> result1 = (List) res1;
                        List<Object> result2 = (List) res2;
                        MyPage<Object> page = new MyPage<>();
                        page.setTotal((Integer) result2.get(0));
                        page.addAll(result1);
                        return page;
                    }
                }
            }
        }
        return proceed;
    }
}

/**
 * @description: 封装分页数据对象
 * @author: zengwenbo
 * @date: 2024/4/11 20:14
 */
@Data
public class MyPage<E> extends ArrayList<E> {
    private int total; // 总行数
}

b.编写mapper

// 注意上面对sql拦截需要用到pageNum和pageSize，所以这两个参数不能丢，必须用@Param注解绑定名
// MyPage来接收上面的分页返回结果
MyPage<User> select3(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

c.编写xml的sql

    <resultMap id="BaseResultMap" type="com.example.demo.pagehelper.User">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="age" jdbcType="BIGINT" property="age"/>
    </resultMap>

    <resultMap id="recordCounts" type="java.lang.Integer">
        <result column="recordCounts" jdbcType="BIGINT"/>
    </resultMap>
    
    <!-- 正常的写分页sql即可 -->
    <select id="select3" resultMap="BaseResultMap, recordCounts">
        select * from user limit #{pageNum}, #{pageSize};
    </select>

d.返回结果的处理

// 直接获取结果数据
List<User> lists = userMapper.select3(1,3);
// 总数需要转换下
int total = ((MyPage<User>) lists).getTotal();

3.使用PageHelper第三方插件来进行分页

1.实现过程

a.导包

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.1</version>
        </dependency>

b.mapper和xmlsql的编写

    @Select("select * from user")
    List<User> select1();

c.调用处和结果的处理

// 调用前设置当前页和当前页数据大小
 PageHelper.startPage(1,3);
 List<User> lists = userMapper.select1();
 // 借助PageInfo来获取总数
 PageInfo<User> userPageInfo = new PageInfo<>(lists);
 long total = userPageInfo.getTotal();

2.实现原理

1.PageHelper.startPage(1,3):将pagNum和pageSize通过ThreadLocal存储起来;

    public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
        Page<E> page = new Page(pageNum, pageSize, count);
        page.setReasonable(reasonable);
        page.setPageSizeZero(pageSizeZero);
        Page<E> oldPage = getLocalPage();
        if (oldPage != null && oldPage.isOrderByOnly()) {
            page.setOrderBy(oldPage.getOrderBy());
        }
		// 核心代码：将page对象存储到ThreadLocal中进行线程隔离
        setLocalPage(page);
        return page;
    }

2.自定义一个拦截器对相关实现进行拦截PageInterceptor,该拦截器实现分页的方式是进行了两次sql的执行，第一次组sql查询出总数，再按参数修改原sql进行分页，然后将数据封装成Page对象，最后将TreadLocal里面的参数进行移除。代码中有标出,追代码可以看如何操作的。

@Intercepts({@Signature(
    type = Executor.class,
    method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
), @Signature(
    type = Executor.class,
    method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}
)})
public class PageInterceptor implements Interceptor {
    private static final Log log = LogFactory.getLog(PageInterceptor.class);
    private volatile Dialect dialect;
    private String countSuffix = "_COUNT";
    protected Cache<String, MappedStatement> msCountMap = null;
    private String default_dialect_class = "com.github.pagehelper.PageHelper";

    public PageInterceptor() {
        String bannerEnabled = System.getProperty("pagehelper.banner");
        if (StringUtil.isEmpty(bannerEnabled)) {
            bannerEnabled = System.getenv("PAGEHELPER_BANNER");
        }

        if (StringUtil.isEmpty(bannerEnabled) || Boolean.parseBoolean(bannerEnabled)) {
            log.debug("\n\n,------.                           ,--.  ,--.         ,--.                         \n|  .--. '  ,--,--.  ,---.   ,---.  |  '--'  |  ,---.  |  |  ,---.   ,---.  ,--.--. \n|  '--' | ' ,-.  | | .-. | | .-. : |  .--.  | | .-. : |  | | .-. | | .-. : |  .--' \n|  | --'  \\ '-'  | ' '-' ' \\   --. |  |  |  | \\   --. |  | | '-' ' \\   --. |  |    \n`--'       `--`--' .`-  /   `----' `--'  `--'  `----' `--' |  |-'   `----' `--'    \n                   `---'                                   `--'                        is intercepting.\n");
        }

    }

    public Object intercept(Invocation invocation) throws Throwable {
        try {
            Object[] args = invocation.getArgs();
            MappedStatement ms = (MappedStatement)args[0];
            Object parameter = args[1];
            RowBounds rowBounds = (RowBounds)args[2];
            ResultHandler resultHandler = (ResultHandler)args[3];
            Executor executor = (Executor)invocation.getTarget();
            CacheKey cacheKey;
            BoundSql boundSql;
            if (args.length == 4) {
                boundSql = ms.getBoundSql(parameter);
                cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
            } else {
                cacheKey = (CacheKey)args[4];
                boundSql = (BoundSql)args[5];
            }

            this.checkDialectExists();
            if (this.dialect instanceof Chain) {
                boundSql = ((Chain)this.dialect).doBoundSql(Type.ORIGINAL, boundSql, cacheKey);
            }

            List resultList;
            if (!this.dialect.skip(ms, parameter, rowBounds)) {
                if (this.dialect.beforeCount(ms, parameter, rowBounds)) {
                	// 1.组sql获取总数的地方
                    Long count = this.count(executor, ms, parameter, rowBounds, (ResultHandler)null, boundSql);
                    if (!this.dialect.afterCount(count, parameter, rowBounds)) {
                        Object var12 = this.dialect.afterPage(new ArrayList(), parameter, rowBounds);
                        return var12;
                    }
                }
				// 2.修改原sql进行分页查询并返回分页结果
                resultList = ExecutorUtil.pageQuery(this.dialect, executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
            } else {
                resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
            }
			// 3.组装Page对象进行返回
            Object var16 = this.dialect.afterPage(resultList, parameter, rowBounds);
            return var16;
        } finally {
            if (this.dialect != null) {
            	// 4.核心是移除最开始设置到Threadlocal里面的参数
                this.dialect.afterAll();
            }

        }
    }

    private void checkDialectExists() {
        if (this.dialect == null) {
            String var1 = this.default_dialect_class;
            synchronized(this.default_dialect_class) {
                if (this.dialect == null) {
                    this.setProperties(new Properties());
                }
            }
        }

    }

    private Long count(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        String countMsId = ms.getId() + this.countSuffix;
        MappedStatement countMs = ExecutorUtil.getExistedMappedStatement(ms.getConfiguration(), countMsId);
        Long count;
        if (countMs != null) {
            count = ExecutorUtil.executeManualCount(executor, countMs, parameter, boundSql, resultHandler);
        } else {
            if (this.msCountMap != null) {
                countMs = (MappedStatement)this.msCountMap.get(countMsId);
            }

            if (countMs == null) {
                countMs = MSUtils.newCountMappedStatement(ms, countMsId);
                if (this.msCountMap != null) {
                    this.msCountMap.put(countMsId, countMs);
                }
            }

            count = ExecutorUtil.executeAutoCount(this.dialect, executor, countMs, parameter, boundSql, rowBounds, resultHandler);
        }

        return count;
    }

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

    public void setProperties(Properties properties) {
        this.msCountMap = CacheFactory.createCache(properties.getProperty("msCountCache"), "ms", properties);
        String dialectClass = properties.getProperty("dialect");
        if (StringUtil.isEmpty(dialectClass)) {
            dialectClass = this.default_dialect_class;
        }

        try {
            Class<?> aClass = Class.forName(dialectClass);
            this.dialect = (Dialect)aClass.newInstance();
        } catch (Exception var4) {
            throw new PageException(var4);
        }

        this.dialect.setProperties(properties);
        String countSuffix = properties.getProperty("countSuffix");
        if (StringUtil.isNotEmpty(countSuffix)) {
            this.countSuffix = countSuffix;
        }

    }
}

因此可以看出使用PageHelper时，在用PageHelper调用startPage设置参数后，中间必须要紧跟当前的查询语句，如果两者之间存在其他的查询语句就会出问题，这点在使用时需要注意。

4.使用Mybatis-plus封装来进行分页

参考链接：mybatis-plus实现分页
其实现过程和pageHelper差不多，也是先算出总数在执行原sql