广东中山网站建设,网站开发合同适用印花税,海珠区住房和水务建设局网站,电子商务网站建设域名前言 分页查询在日常开发中无法避免,但每次sql编写时,mybatis 中使用limit,oracle中使用rownum分页,业务场景少的情况下,可以接受.但是随着业务增加每次相同的功能做重复开发又不是那么方便,那么我们有什么方案去解决开发中出现的分页问题呢?
一、PageHelper 框架分页 首先我…前言 分页查询在日常开发中无法避免,但每次sql编写时,mybatis 中使用limit,oracle中使用rownum分页,业务场景少的情况下,可以接受.但是随着业务增加每次相同的功能做重复开发又不是那么方便,那么我们有什么方案去解决开发中出现的分页问题呢?
一、PageHelper 框架分页 首先我们看下pageHelper框架的分页核心类PageInterceptor 同样是实现了mybatis的Interceptor 二、自定义实现mybatis 插件 Interceptor (与pageHelper类似) 当涉及系统中的查询语句,参数为RowBounds时,查询语句会默认根据RowBounds分页 package com.cloud.common;import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.builder.StaticSqlSource;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Properties;/*** author haizhuangbu* date 2022/6/5 18:25* mark MybatisIntercepter mybatis 拦截器*/
Intercepts(Signature(type Executor.class, method query, args {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}))
Slf4j
public class MybatisInterceptor implements Interceptor {private Logger logger LoggerFactory.getLogger(MybatisInterceptor.class);private final Integer MAPPED_STATEMENT_INDEX 0;private final Integer PARAM 1;// 默认分页private final Integer ROW_BOUNDS_INDEX 2;Overridepublic Object intercept(Invocation invocation) throws Throwable {logger.info(分页拦截器);// 1. 方法获取参数Object[] args invocation.getArgs();// 分页器RowBounds rowBounds (RowBounds) args[ROW_BOUNDS_INDEX];// 检查是否为默认分页if (rowBounds RowBounds.DEFAULT) {return invocation.proceed();}// 设置为默认args[ROW_BOUNDS_INDEX] RowBounds.DEFAULT;// 执行器MappedStatement mappedStatements (MappedStatement) args[MAPPED_STATEMENT_INDEX];// 获取执行sqlBoundSql boundSql mappedStatements.getBoundSql(PARAM);String sql boundSql.getSql();// 获取数据库类型String databaseProductName mappedStatements.getConfiguration().getEnvironment().getDataSource().getConnection().getMetaData().getDatabaseProductName();// mysql 的 分页处理if (MYSQL.equalsIgnoreCase(databaseProductName)) {sql mysql(sql, rowBounds);}// oracle 的分页处理if (ORACLE.equalsIgnoreCase(databaseProductName)) {sql oracle(sql, rowBounds);}logger.info(sql:{}, sql);StaticSqlSource staticSqlSource new StaticSqlSource(mappedStatements.getConfiguration(), sql, boundSql.getParameterMappings());// 通过反射设置MapperStatment 的sqlSource字段Field field MappedStatement.class.getDeclaredField(sqlSource);field.setAccessible(true);field.set(mappedStatements, staticSqlSource);return invocation.proceed();}private String oracle(String sql, RowBounds rowBounds) {// 为查询语句给到别名sql select * from (select t1.*, rownum rn from ( sql ) t1 ) t2 where rn (rowBounds.getOffset() - 1) * rowBounds.getLimit() and rn rowBounds.getOffset() * rowBounds.getLimit();return sql;}private String mysql(String sql, RowBounds rowBounds) {// 页数int limit rowBounds.getLimit();// 页码int offset rowBounds.getOffset();String format String.format(LIMIT %d,%d, offset, limit);if (sql.contains(format)) {
// sql sql.replaceAll(select, SELECT COUNT(1) total,);sql sql.replaceAll(format, );}sql format;return sql;}Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}Overridepublic void setProperties(Properties properties) {Interceptor.super.setProperties(properties);}}将自定义插件交给spring
Beanpublic ConfigurationCustomizer configurationCustomizer() {return new ConfigurationCustomizer() {Overridepublic void customize(Configuration configuration) {configuration.addInterceptor(new MybatisInterceptor());}};}
