[JAVA](框架) Mybatis-plus Oracle 数据库排序后分页BUG

最新推荐文章于 2025-06-11 11:02:23 发布

mia010

最新推荐文章于 2025-06-11 11:02:23 发布

阅读量1.6k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： Java 文章标签： mybatis mybatis-plus oracle 排序后分页

本文链接：https://blog.youkuaiyun.com/muraty/article/details/117698633

Java 专栏收录该内容

9 篇文章

订阅专栏

该博客主要讨论了在Oracle数据库中遇到的分页查询问题，原查询导致排序后的分页数据不正确。通过重写OracleDialect、DialectFactory和PaginationInterceptor，实现了新的分页查询逻辑，确保了排序的准确性。新查询方式在内部添加了一个额外的查询层，避免了原有的问题，并在MybatisPlus配置中进行了相应的调整。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

重写OracleDialect

此处目的是再套一层查询

-- 原查询 (会导致排序后分页数据不对)
SELECT * FROM 
  (SELECT TMP.*, ROWNUM ROW_ID FROM 
    (SELECT * FROM ${table}) TMP 
  WHERE ROWNUM <= 10) 
WHERE ROW_ID > 0

-- 新查询
SELECT * FROM
  (SELECT TMP2.* FROM
    (SELECT TMP.*, ROWNUM ROW_ID FROM
	  (SELECT * FROM ${table}) TMP 
  ) TMP2	
  WHERE ROWNUM <= 20) 
WHERE ROW_ID > 10

import com.baomidou.mybatisplus.plugins.pagination.IDialect;

public class SpecialOracleDialect implements IDialect {
    public static final SpecialOracleDialect INSTANCE = new SpecialOracleDialect();

    public SpecialOracleDialect() {
    }

    @Override
    public String buildPaginationSql(String originalSql, int offset, int limit) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ( SELECT TMP2.* FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( ");
        sql.append(originalSql).append(" ) TMP ) TMP2 WHERE ROWNUM <=").append(offset >= 1 ? offset + limit : limit);
        sql.append(") WHERE ROW_ID > ").append(offset);
        return sql.toString();
    }
}

重写DialectFactory

此处是 dialect = SpecialOracleDialect.INSTANCE; 替换原来的OracleDialect


import com.baomidou.mybatisplus.enums.DBType;
import com.baomidou.mybatisplus.exceptions.MybatisPlusException;
import com.baomidou.mybatisplus.plugins.pagination.IDialect;
import com.baomidou.mybatisplus.plugins.pagination.PageHelper;
import com.baomidou.mybatisplus.plugins.pagination.Pagination;
import com.baomidou.mybatisplus.plugins.pagination.dialects.*;
import com.baomidou.mybatisplus.toolkit.StringUtils;
import org.apache.ibatis.session.RowBounds;

public class SpecialDialectFactory {
    public SpecialDialectFactory() {
    }

    public static String buildPaginationSql(Pagination page, String buildSql, DBType dbType, String dialectClazz) throws Exception {
        return getDialect(dbType, dialectClazz).buildPaginationSql(buildSql, PageHelper.offsetCurrent(page), page.getSize());
    }

    public static String buildPaginationSql(RowBounds rowBounds, String buildSql, DBType dbType, String dialectClazz) throws Exception {
        return getDialect(dbType, dialectClazz).buildPaginationSql(buildSql, rowBounds.getOffset(), rowBounds.getLimit());
    }

    private static IDialect getDialect(DBType dbType, String dialectClazz) throws Exception {
        IDialect dialect = null;
        if (StringUtils.isNotEmpty(dialectClazz)) {
            try {
                Class<?> clazz = Class.forName(dialectClazz);
                if (IDialect.class.isAssignableFrom(clazz)) {
                    dialect = (IDialect) clazz.newInstance();
                }
            } catch (ClassNotFoundException var4) {
                throw new MybatisPlusException("Class :" + dialectClazz + " is not found");
            }
        } else if (null != dbType) {
            dialect = getDialectByDbtype(dbType);
        }

        if (dialect == null) {
            throw new MybatisPlusException("The value of the dialect property in mybatis configuration.xml is not defined.");
        } else {
            return dialect;
        }
    }

    private static IDialect getDialectByDbtype(DBType dbType) {
        Object dialect;
        switch (dbType) {
            case MYSQL:
                dialect = MySqlDialect.INSTANCE;
                break;
            case ORACLE:
                dialect = SpecialOracleDialect.INSTANCE;
                break;
            case DB2:
                dialect = DB2Dialect.INSTANCE;
                break;
            case H2:
                dialect = H2Dialect.INSTANCE;
                break;
            case SQLSERVER:
                dialect = SQLServerDialect.INSTANCE;
                break;
            case SQLSERVER2005:
                dialect = SQLServer2005Dialect.INSTANCE;
                break;
            case POSTGRE:
                dialect = PostgreDialect.INSTANCE;
                break;
            case HSQL:
                dialect = HSQLDialect.INSTANCE;
                break;
            case SQLITE:
                dialect = SQLiteDialect.INSTANCE;
                break;
            default:
                throw new MybatisPlusException("The Database's Not Supported! DBType:" + dbType);
        }

        return (IDialect) dialect;
    }
}

重写PaginationInterceptor

此处是 originalSql = SpecialDialectFactory.buildPaginationSql 替换原来的工厂 DialectFactory

import com.baomidou.mybatisplus.MybatisDefaultParameterHandler;
import com.baomidou.mybatisplus.enums.DBType;
import com.baomidou.mybatisplus.plugins.SqlParserHandler;
import com.baomidou.mybatisplus.plugins.pagination.PageHelper;
import com.baomidou.mybatisplus.plugins.pagination.Pagination;
import com.baomidou.mybatisplus.plugins.parser.ISqlParser;
import com.baomidou.mybatisplus.plugins.parser.SqlInfo;
import com.baomidou.mybatisplus.toolkit.JdbcUtils;
import com.baomidou.mybatisplus.toolkit.PluginUtils;
import com.baomidou.mybatisplus.toolkit.SqlUtils;
import com.baomidou.mybatisplus.toolkit.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
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.RowBounds;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

@Intercepts({@Signature(
        type = StatementHandler.class,
        method = "prepare",
        args = {Connection.class, Integer.class}
)})
public class SpecialPaginationInterceptor extends SqlParserHandler implements Interceptor {
    private static final Log logger = LogFactory.getLog(SpecialPaginationInterceptor.class);
    private ISqlParser sqlParser;
    private boolean overflowCurrent = false;
    private String dialectType;
    private String dialectClazz;
    private boolean localPage = false;

    public SpecialPaginationInterceptor() {
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) PluginUtils.realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        this.sqlParser(metaObject);
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {
            return invocation.proceed();
        } else {
            RowBounds rowBounds = (RowBounds) metaObject.getValue("delegate.rowBounds");
            if (rowBounds == null || rowBounds == RowBounds.DEFAULT) {
                if (!this.localPage) {
                    return invocation.proceed();
                }

                rowBounds = PageHelper.getPagination();
                if (rowBounds == null) {
                    return invocation.proceed();
                }
            }

            BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
            String originalSql = boundSql.getSql();
            Connection connection = (Connection) invocation.getArgs()[0];
            DBType dbType = StringUtils.isNotEmpty(this.dialectType) ? DBType.getDBType(this.dialectType) : JdbcUtils.getDbType(connection.getMetaData().getURL());
            if (rowBounds instanceof Pagination) {
                Pagination page = (Pagination) rowBounds;
                boolean orderBy = true;
                if (page.isSearchCount()) {
                    SqlInfo sqlInfo = SqlUtils.getOptimizeCountSql(page.isOptimizeCountSql(), this.sqlParser, originalSql);
                    orderBy = sqlInfo.isOrderBy();
                    this.queryTotal(this.overflowCurrent, sqlInfo.getSql(), mappedStatement, boundSql, page, connection);
                    if (page.getTotal() <= 0) {
                        return invocation.proceed();
                    }
                }

                String buildSql = SqlUtils.concatOrderBy(originalSql, page, orderBy);
                originalSql = SpecialDialectFactory.buildPaginationSql(page, buildSql, dbType, this.dialectClazz);
            } else {
                originalSql = SpecialDialectFactory.buildPaginationSql((RowBounds) rowBounds, originalSql, dbType, this.dialectClazz);
            }

            metaObject.setValue("delegate.boundSql.sql", originalSql);
            metaObject.setValue("delegate.rowBounds.offset", 0);
            metaObject.setValue("delegate.rowBounds.limit", 2147483647);
            return invocation.proceed();
        }
    }

    protected void queryTotal(boolean overflowCurrent, String sql, MappedStatement mappedStatement, BoundSql boundSql, Pagination page, Connection connection) {
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            Throwable var8 = null;

            try {
                DefaultParameterHandler parameterHandler = new MybatisDefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), boundSql);
                parameterHandler.setParameters(statement);
                int total = 0;
                ResultSet resultSet = statement.executeQuery();
                Throwable var12 = null;

                try {
                    if (resultSet.next()) {
                        total = resultSet.getInt(1);
                    }
                } catch (Throwable var37) {
                    var12 = var37;
                    throw var37;
                } finally {
                    if (resultSet != null) {
                        if (var12 != null) {
                            try {
                                resultSet.close();
                            } catch (Throwable var36) {
                                var12.addSuppressed(var36);
                            }
                        } else {
                            resultSet.close();
                        }
                    }

                }

                page.setTotal(total);
                int pages = page.getPages();
                if (overflowCurrent && page.getCurrent() > pages) {
                    page.setCurrent(1);
                }
            } catch (Throwable var39) {
                var8 = var39;
                throw var39;
            } finally {
                if (statement != null) {
                    if (var8 != null) {
                        try {
                            statement.close();
                        } catch (Throwable var35) {
                            var8.addSuppressed(var35);
                        }
                    } else {
                        statement.close();
                    }
                }

            }
        } catch (Exception var41) {
            logger.error("Error: Method queryTotal execution error !", var41);
        }

    }

    @Override
    public Object plugin(Object target) {
        return target instanceof StatementHandler ? Plugin.wrap(target, this) : target;
    }

    @Override
    public void setProperties(Properties prop) {
        String dialectType = prop.getProperty("dialectType");
        String dialectClazz = prop.getProperty("dialectClazz");
        String localPage = prop.getProperty("localPage");
        if (StringUtils.isNotEmpty(dialectType)) {
            this.dialectType = dialectType;
        }

        if (StringUtils.isNotEmpty(dialectClazz)) {
            this.dialectClazz = dialectClazz;
        }

        if (StringUtils.isNotEmpty(localPage)) {
            this.localPage = Boolean.valueOf(localPage);
        }

    }

    public SpecialPaginationInterceptor setDialectType(String dialectType) {
        this.dialectType = dialectType;
        return this;
    }

    public SpecialPaginationInterceptor setDialectClazz(String dialectClazz) {
        this.dialectClazz = dialectClazz;
        return this;
    }

    public SpecialPaginationInterceptor setOverflowCurrent(boolean overflowCurrent) {
        this.overflowCurrent = overflowCurrent;
        return this;
    }

    public SpecialPaginationInterceptor setSqlParser(ISqlParser sqlParser) {
        this.sqlParser = sqlParser;
        return this;
    }

    public SpecialPaginationInterceptor setLocalPage(boolean localPage) {
        this.localPage = localPage;
        return this;
    }
}

最后别忘了MybatisPlusConfig

    @Bean
    public SpecialPaginationInterceptor paginationInterceptor() {
        return new SpecialPaginationInterceptor();
    }