工作发现第6-8页出现很多重复数据.
第7页
#执行sql为
SELECT * FROM ( SELECT TMP.id, ROWNUM ROW_ID FROM ( SELECT ID,app_id,apk_id,cert_identity,uesr_id,oper_time,apk_version,channel_name,packet_type,bit,cert_sn,sign_time,signer,result FROM TB_SIGN_LOG ORDER BY oper_time DESC ) TMP WHERE ROWNUM <=80) WHERE ROW_ID > 70
第8页
#执行sql为
SELECT * FROM ( SELECT TMP.id, ROWNUM ROW_ID FROM ( SELECT ID,app_id,apk_id,cert_identity,uesr_id,oper_time,apk_version,channel_name,packet_type,bit,cert_sn,sign_time,signer,result FROM TB_SIGN_LOG ORDER BY oper_time DESC ) TMP WHERE ROWNUM <=90) WHERE ROW_ID > 80
发现都是一样的数据，最终发现一个共同点，因为根据oper_time排序的，而oper_time很多时间是一致的。
首先进行mybatisplus分页进行分析
mybaplus针对oracle分页插件实现逻辑为
#页面实现sql
SELECT * FROM ( SELECT TMP.id, ROWNUM ROW_ID FROM ( 【正常sql语句】) TMP WHERE ROWNUM <=90) WHERE ROW_ID > 80
#sql前面加上如下
SELECT * FROM ( SELECT TMP.id, ROWNUM ROW_ID FROM (
#sql后面加上如下，截止到第多少个，从多少个开始
) TMP WHERE ROWNUM <=90) WHERE ROW_ID > 80
sql语句分析
首先确定了为order by 与分页结合出现问题，后续确定因为oper_time有重复值，这样的话并没有给它一个排序规则，所以就根据数据库的取出的先后顺序来给它排序了，那加上唯一键id排序。
#根据时间与id进行排序，执行sql如下
SELECT * FROM ( SELECT TMP.id, ROWNUM ROW_ID FROM ( SELECT ID,app_id,apk_id,cert_identity,uesr_id,oper_time,apk_version,channel_name,packet_type,bit,cert_sn,sign_time,signer,result FROM TB_SIGN_LOG ORDER BY oper_time,id DESC ) TMP WHERE ROWNUM <=90) WHERE ROW_ID > 80
#直接根据时间排序
SELECT * FROM ( SELECT TMP.id,oper_time, ROWNUM ROW_ID FROM ( SELECT ID,app_id,apk_id,cert_identity,uesr_id,oper_time,apk_version,channel_name,packet_type,bit,cert_sn,sign_time,signer,result FROM TB_SIGN_LOG ORDER BY oper_time DESC ) TMP WHERE ROWNUM <=10) WHERE ROW_ID > 0
最终发现根据时间与id排序的，发现order by oper_time失效，直接根据id降序排序了，等于还是有问题。
解决方案
1.不使用mybatisplus自带插件，使用手写分页。
### 测试sql
SELECT * FROM ( SELECT TMP.id,oper_time, ROWNUM ROW_ID FROM (
SELECT ID,app_id,apk_id,cert_identity,uesr_id,oper_time,apk_version,channel_name,packet_type,bit,cert_sn,sign_time,signer,result FROM TB_SIGN_LOG ORDER BY oper_time DESC
) TMP ) WHERE ROW_ID <=10 and ROW_ID > 0
### 业务xml中sql修改
SELECT * FROM ( SELECT TMP.id,oper_time, ROWNUM ROW_ID FROM (
SELECT ID,app_id,apk_id,cert_identity,uesr_id,oper_time,apk_version,channel_name,packet_type,bit,cert_sn,sign_time,signer,result FROM TB_SIGN_LOG ORDER BY oper_time DESC
) TMP ) WHERE ROW_ID <= ${page*size} and ROW_ID > ${(page - 1) * size}
2.修改mybtisplus源码
mybtisplus3.1.1 中oracle源码
/**
* ORACLE 数据库分页语句组装实现
*
* @author hubin
* @since 2016-01-23
*/
public class OracleDialect implements IDialect {
@Override
public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
limit = (offset >= 1) ? (offset + limit) : limit;
String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( " +
originalSql + " ) TMP WHERE ROWNUM <=" + FIRST_MARK + ") WHERE ROW_ID > " + SECOND_MARK;
return new DialectModel(sql, limit, offset).setConsumerChain();
}
}
修改成如下
/**
* ORACLE 数据库分页语句组装实现
*
* @author hubin
* @since 2016-01-23
*/
public class OracleDialect implements IDialect {
@Override
public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
limit = (offset >= 1) ? (offset + limit) : limit;
String sql = " SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM (" +
originalSql + " ) TMP ) WHERE ROW_ID <=" + FIRST_MARK + "and ROW_ID > " + SECOND_MARK;
return new DialectModel(sql, limit, offset).setConsumerChain();
}
}
3.最优方式，自定义分页插件，修改分页拼接语句
重写DialectFactory
package cn.byzk.appsignservice2.common.config.mybatis.page;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import com.baomidou.mybatisplus.core.toolkit.ExceptionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.*;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
/**
* @author 会飞的大象 2022-07-15
*/
public class SpecialDialectFactory {
/**
* 方言缓存
*/
private static final Map<String, IDialect> DIALECT_CACHE = new ConcurrentHashMap<>();
/**
* Physical Page Interceptor for all the queries with parameter
* {@link RowBounds}
*
* @param page 翻页对象
* @param buildSql 编译 SQL
* @param dbType 数据类型
* @param dialectClazz 数据库方言
* @return 分页模型
*/
public static DialectModel buildPaginationSql(IPage<?> page, String buildSql, DbType dbType, String dialectClazz) {
// fix #196
return getDialect(dbType, dialectClazz).buildPaginationSql(buildSql, page.offset(), page.getSize());
}
/**
* 获取数据库方言
*
* @param dbType 数据库类型
* @param dialectClazz 自定义方言实现类
* @return ignore
*/
private static IDialect getDialect(DbType dbType, String dialectClazz) {
IDialect dialect = DIALECT_CACHE.get(dbType.getDb());
if (null == dialect) {
// 自定义方言
if (StringUtils.isNotEmpty(dialectClazz)) {
dialect = DIALECT_CACHE.get(dialectClazz);
if (null != dialect) {
return dialect;
}
try {
Class<?> clazz = Class.forName(dialectClazz);
if (IDialect.class.isAssignableFrom(clazz)) {
dialect = (IDialect) clazz.newInstance();
DIALECT_CACHE.put(dialectClazz, dialect);
}
} catch (ClassNotFoundException e) {
throw ExceptionUtils.mpe("Class : %s is not found", dialectClazz);
} catch (IllegalAccessException | InstantiationException e) {
throw ExceptionUtils.mpe("Class : %s can not be instance", dialectClazz);
}
} else {
// 缓存方言
dialect = getDialectByDbType(dbType);
DIALECT_CACHE.put(dbType.getDb(), dialect);
}
/* 未配置方言则抛出异常 */
Assert.notNull(dialect, "The value of the dialect property in mybatis configuration.xml is not defined.");
}
return dialect;
}
/**
* 根据数据库类型选择不同分页方言
*
* @param dbType 数据库类型
* @return 分页语句组装类
*/
private static IDialect getDialectByDbType(DbType dbType) {
switch (dbType) {
case MYSQL:
return new MySqlDialect();
case MARIADB:
return new MariaDBDialect();
case ORACLE:
return new SpecialOracleDialect();
case DB2:
return new DB2Dialect();
case H2:
return new H2Dialect();
case SQL_SERVER:
return new SQLServerDialect();
case SQL_SERVER2005:
return new SQLServer2005Dialect();
case POSTGRE_SQL:
return new PostgreDialect();
case HSQL:
return new HSQLDialect();
case SQLITE:
return new SQLiteDialect();
case DM:
return new DmDialect();
default:
throw ExceptionUtils.mpe("The Database's IDialect Not Supported!");
}
}
}
重写OracleDialect
package cn.byzk.appsignservice2.common.config.mybatis.page;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.OracleDialect;
/**
* @author 会飞的大象 2022-07-15
*/
public class SpecialOracleDialect extends OracleDialect {
@Override
public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
limit = (offset >= 1) ? (offset + limit) : limit;
String sql = " SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM (" +
originalSql + " ) TMP ) WHERE ROW_ID <=" + FIRST_MARK + " and ROW_ID > " + SECOND_MARK;
return new DialectModel(sql, limit, offset).setConsumerChain();
}
}
重写PaginationInterceptor
package cn.byzk.appsignservice2.common.config.mybatis.page;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisDefaultParameterHandler;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.core.parser.SqlInfo;
import com.baomidou.mybatisplus.core.toolkit.*;
import com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.toolkit.JdbcUtils;
import com.baomidou.mybatisplus.extension.toolkit.SqlParserUtils;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import static java.util.stream.Collectors.joining;
/**
* @author 会飞的大象 2022-07-15
*/
@Setter
@Accessors(chain = true)
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class SpecialPaginationInterceptor extends AbstractSqlParserHandler implements Interceptor {
/**
* COUNT SQL 解析
*/
private ISqlParser countSqlParser;
/**
* 溢出总页数，设置第一页
*/
private boolean overflow = false;
/**
* 单页限制 500 条，小于 0 如 -1 不受限制
*/
private long limit = 500L;
/**
* 方言类型
*/
private String dialectType;
/**
* 方言实现类
*/
private String dialectClazz;
/**
* 查询SQL拼接Order By
*
* @param originalSql 需要拼接的SQL
* @param page page对象
* @param orderBy 是否需要拼接Order By
* @return ignore
*/
public static String concatOrderBy(String originalSql, IPage<?> page, boolean orderBy) {
if (orderBy && (ArrayUtils.isNotEmpty(page.ascs())
|| ArrayUtils.isNotEmpty(page.descs()))) {
StringBuilder buildSql = new StringBuilder(originalSql);
String ascStr = concatOrderBuilder(page.ascs(), " ASC");
String descStr = concatOrderBuilder(page.descs(), " DESC");
if (StringUtils.isNotEmpty(ascStr) && StringUtils.isNotEmpty(descStr)) {
ascStr += ", ";
}
if (StringUtils.isNotEmpty(ascStr) || StringUtils.isNotEmpty(descStr)) {
buildSql.append(" ORDER BY ").append(ascStr).append(descStr);
}
return buildSql.toString();
}
return originalSql;
}
/**
* 拼接多个排序方法
*
* @param columns ignore
* @param orderWord ignore
*/
private static String concatOrderBuilder(String[] columns, String orderWord) {
if (ArrayUtils.isNotEmpty(columns)) {
return Arrays.stream(columns).filter(StringUtils::isNotEmpty)
.map(i -> i + orderWord).collect(joining(StringPool.COMMA));
}
return StringUtils.EMPTY;
}
/**
* Physical Page Interceptor for all the queries with parameter {@link RowBounds}
*/
@SuppressWarnings("unchecked")
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
// SQL 解析
this.sqlParser(metaObject);
// 先判断是不是SELECT操作 (2019-04-10 00:37:31 跳过存储过程)
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
if (SqlCommandType.SELECT != mappedStatement.getSqlCommandType()
|| StatementType.CALLABLE == mappedStatement.getStatementType()) {
return invocation.proceed();
}
// 针对定义了rowBounds，做为mapper接口方法的参数
BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
Object paramObj = boundSql.getParameterObject();
// 判断参数里是否有page对象
IPage<?> page = null;
if (paramObj instanceof IPage) {
page = (IPage<?>) paramObj;
} else if (paramObj instanceof Map) {
for (Object arg : ((Map<?, ?>) paramObj).values()) {
if (arg instanceof IPage) {
page = (IPage<?>) arg;
break;
}
}
}
/*
* 不需要分页的场合，如果 size 小于 0 返回结果集
*/
if (null == page || page.getSize() < 0) {
return invocation.proceed();
}
/*
* 处理单页条数限制
*/
if (limit > 0 && limit <= page.getSize()) {
page.setSize(limit);
}
String originalSql = boundSql.getSql();
Connection connection = (Connection) invocation.getArgs()[0];
DbType dbType = StringUtils.isNotEmpty(dialectType) ? DbType.getDbType(dialectType)
: JdbcUtils.getDbType(connection.getMetaData().getURL());
boolean orderBy = true;
if (page.isSearchCount()) {
SqlInfo sqlInfo = SqlParserUtils.getOptimizeCountSql(page.optimizeCountSql(), countSqlParser, originalSql);
orderBy = sqlInfo.isOrderBy();
this.queryTotal(overflow, sqlInfo.getSql(), mappedStatement, boundSql, page, connection);
if (page.getTotal() <= 0) {
return null;
}
}
String buildSql = concatOrderBy(originalSql, page, orderBy);
DialectModel model = SpecialDialectFactory.buildPaginationSql(page, buildSql, dbType, dialectClazz);
Configuration configuration = mappedStatement.getConfiguration();
List<ParameterMapping> mappings = new ArrayList<>(boundSql.getParameterMappings());
Map<String, Object> additionalParameters = (Map<String, Object>) metaObject.getValue("delegate.boundSql.additionalParameters");
model.consumers(mappings, configuration, additionalParameters);
metaObject.setValue("delegate.boundSql.sql", model.getDialectSql());
metaObject.setValue("delegate.boundSql.parameterMappings", mappings);
return invocation.proceed();
}
/**
* 查询总记录条数
*
* @param sql count sql
* @param mappedStatement MappedStatement
* @param boundSql BoundSql
* @param page IPage
* @param connection Connection
*/
protected void queryTotal(boolean overflowCurrent, String sql, MappedStatement mappedStatement, BoundSql boundSql, IPage<?> page, Connection connection) {
try (PreparedStatement statement = connection.prepareStatement(sql)) {
DefaultParameterHandler parameterHandler = new MybatisDefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), boundSql);
parameterHandler.setParameters(statement);
long total = 0;
try (ResultSet resultSet = statement.executeQuery()) {
if (resultSet.next()) {
total = resultSet.getLong(1);
}
}
page.setTotal(total);
/*
* 溢出总页数，设置第一页
*/
long pages = page.getPages();
if (overflowCurrent && page.getCurrent() > pages) {
// 设置为第一条
page.setCurrent(1);
}
} catch (Exception e) {
throw ExceptionUtils.mpe("Error: Method queryTotal execution error of sql : \n %s \n", e, sql);
}
}
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
}
return target;
}
@Override
public void setProperties(Properties prop) {
String dialectType = prop.getProperty("dialectType");
String dialectClazz = prop.getProperty("dialectClazz");
if (StringUtils.isNotEmpty(dialectType)) {
this.dialectType = dialectType;
}
if (StringUtils.isNotEmpty(dialectClazz)) {
this.dialectClazz = dialectClazz;
}
}
}
修改MyBatisConfig使用自定义SpecialPaginationInterceptor
/**
* 分页插件 使用自定义sql分页.
*
* @return 分页插件
*/
@Bean
public SpecialPaginationInterceptor paginationInterceptor() {
SpecialPaginationInterceptor paginationInterceptor = new SpecialPaginationInterceptor();
// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量，默认 500 条，-1 不受限制
// paginationInterceptor.setLimit(500);
// 开启 count 的 join 优化,只针对部分 left join
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize());
return paginationInterceptor;
}
评论区