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，你需要具体到方法上，可以选择填写queryString 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.Driverusername: rootpassword: rooturl: 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 = ";";@Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler target = (StatementHandler) invocation.getTarget();// 借助metaObject来修改执行的sqlMetaObject 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();}@Overridepublic Object plugin(Object o) {return Plugin.wrap(o, this);}@Overridepublic 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()";@Overridepublic Object intercept(Invocation invocation) throws Throwable {Object proceed = invocation.proceed();DefaultResultSetHandler target = (DefaultResultSetHandler) invocation.getTarget();// 借助metaObject来获取执行的sqlMetaObject 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;}}
}