在项目中使用mybatis-plus分页,数据库使用oracle11g,查询语句中有order by且order by的字段不是主键,会造成数据查询重复!
修改方案1:order by字段后面,再使用主键进行,order by 非主键字段,主键字段
修改方案2:修改mybatis-plus中mybatis-plus-extension-3.3.2.jar中OracleDialect.java中的buildPaginationSql方法,修改如下:
原文件代码:
public class OracleDialect
implements IDialect
{
public DialectModel buildPaginationSql(String originalSql, long offset, long limit)
{
limit = offset >= 1L ? offset + limit : limit;
String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( " + originalSql + " ) TMP WHERE ROWNUM <=" + "?" + ") WHERE ROW_ID > " + "?";
return new DialectModel(sql, limit, offset).setConsumerChain();
}
}
修改后代码:
@Override
public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
limit = (offset >= 1) ? (offset + limit) : limit;
offset=(offset>=1) ? (offset+1):offset;
String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM RN FROM ( " +
originalSql + " ) TMP ) WHERE RN BETWEEN "+ SECOND_MARK +" AND "+ FIRST_MARK;
return new DialectModel(sql, offset,limit).setConsumerChain();
}
修改后的jar包和编译后的class文件在此:百度网盘地址,提取码:1234
ps:出差路上所写,比较潦草,欢迎吐槽批评,后期再整理,谢谢