解决场景一:oracle中使用rownum分页随着数据的增多、查询页数的累加,查询时长剧增的问题:
rownum分页图示:
(使用语句select * from ( select tmp_page.*, rownum row_id from (select * from dual union all......) tmp_page where rownum <= ? ) where row_id > ? )
row_number() 分页图示:
(使用语句select * from (select tmp_page.*,row_number() over(order by null) row_id from ( select * from dual union all......) tmp_page ) tmp_page where row_id <= ? and row_id > ? )
图中只演示了20万数据在查询第10页(每页20条)时候遇到的问题,往后第一种会直接查询不出来。
解决场景二:
解决替换PageHelper自定义的分页模板的问题,原分页模板(jar中OracleParser类)如下图所示:
使用PageHelper官方文档中提供的拦截器使用方法,参考链接如下:
https://pagehelper.github.io/docs/interceptor/#5-如何配置不同的-executor-插件
配置文件代码(注意拦截器顺序):
<plugins>
<!-- 自定义拦截器类所在包名 -->
<plugin interceptor="com.navchina.utils.pageInterceptor.PageInterceptor">
</plugin>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 4.0.0以后版本可以不设置该参数 -->
<!-- <property name="dialect" value="oracle"/> -->
<!-- 该参数默认为false -->
<!-- 设置为true时,会将RowBounds第一个参数offset当成pageNum页码使用 -->
<!-- 和startPage中的pageNum效果一样-->
<property name="offsetAsPageNum" value="true"/>
<!-- 该参数默认为false -->
<!-- 设置为true时,使用RowBounds分页会进行count查询 -->
<property name="rowBoundsWithCount" value="true"/>
<!-- 设置为true时,如果pageSize=0或者RowBounds.limit = 0就会查询出全部的结果 -->
<!-- (相当于没有执行分页查询,但是返回结果仍然是Page类型)-->
<property name="pageSizeZero" value="true"/>
<!-- 3.3.0版本可用 - 分页参数合理化,默认false禁用 -->
<!-- 启用合理化时,如果pageNum<1会查询第一页,如果pageNum>pages会查询最后一页 -->
<!-- 禁用合理化时,如果pageNum<1或pageNum>pages会返回空数据 -->
<property name="reasonable" value="true"/>
<!-- 3.5.0版本可用 - 为了支持startPage(Object params)方法 -->
<!-- 增加了一个`params`参数来配置参数映射,用于从Map或ServletRequest中取值 -->
<!-- 可以配置pageNum,pageSize,count,pageSizeZero,reasonable,orderBy,不配置映射的用默认值 -->
<!-- 不理解该含义的前提下,不要随便复制该配置 -->
<property name="params" value="pageNum=start;pageSize=limit;"/>
<!-- 支持通过Mapper接口参数来传递分页参数 -->
<property name="supportMethodsArguments" value="true"/>
<!-- always总是返回PageInfo类型,check检查返回类型是否为PageInfo,none返回Page -->
<property name="returnPageInfo" value="check"/>
</plugin>
</plugins>
拦截器代码:
package com.navchina.utils.pageInterceptor;
import java.util.Properties;
import org.apache.ibatis.builder.SqlSourceBuilder;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.scripting.xmltags.DynamicContext;
import org.apache.ibatis.scripting.xmltags.SqlNode;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
@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 {
@SuppressWarnings("rawtypes")
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
MappedStatement mappedStatement = (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;
//由于逻辑关系,只会进入一次
String id = mappedStatement.getId();
if(args.length == 4){
//4 个参数时
boundSql = mappedStatement.getBoundSql(parameter);
cacheKey = executor.createCacheKey(mappedStatement, parameter, rowBounds, boundSql);
} else {
//6 个参数时
cacheKey = (CacheKey) args[4];
boundSql = (BoundSql) args[5];
}
//根据ID筛选要处理的查询语句,对应XML中的id属性;匹配queryBDList_COUNT可处理分页中的总数查询。
if (id.matches("^.+queryBDList$")) {
Object parameterObject = boundSql.getParameterObject();
Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();
DynamicContext context = new DynamicContext(mappedStatement.getConfiguration(), parameterObject);
MetaObject metaObject = MetaObject.forObject(mappedStatement, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
//获取PageHelper加工前原SQL
SqlNode sqlNode = (SqlNode) metaObject.getValue("sqlSource.rootSqlNode");
sqlNode.apply(context);
String countextSql = context.getSql();
//使用自定义分页方式
StringBuilder stringBuilder = new StringBuilder("select * from (select tmp_page.*,row_number() over(order by null) row_id from ( ");
stringBuilder.append(countextSql);
stringBuilder.append(" ) tmp_page ) tmp_page where row_id <= #{First_PageHelper,jdbcType=VARCHAR} and row_id > #{Second_PageHelper,jdbcType=VARCHAR} ");
//创建新分页方式的boundSql
SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(mappedStatement.getConfiguration());
SqlSource sqlSource = sqlSourceParser.parse(stringBuilder.toString(), parameterType, context.getBindings());
boundSql = sqlSource.getBoundSql(parameterObject);
//此处可处理新加的参数,因新加参数和原PageHelper分页参数相同,这里不再处理
// boundSql.setAdditionalParameter("First_PageHelper", "20");
// boundSql.setAdditionalParameter("Second_PageHelper", "10");
}
//注:下面的方法可以根据自己的逻辑调用多次,在分页插件中,count 和 page 各调用了一次
return executor.query(mappedStatement, parameter, rowBounds, resultHandler, cacheKey, boundSql);
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
id过滤可根据自己需要做处理(如写在配置文件中,通过serProperties读取,也可全局替换,去掉id过滤)。