1:When we use hibernate’s NativeQuery for paging, the underlying will use limit or rownum, and which paging method is determined by the dialect of different databases, the following will explain the h2 oracle pattern using NativeQuery for paging when the problem is solved org. InvalidDataAccessResourceUsageException: could not prepare statement; SQL [SELECT * limit ?] SQLGrammarException: could not prepare statement
We will find that h2’s oracle schema uses the limit method for paging, but using limit for paging will report an error
2:h2 paging method
Open h2’s dialect class H2Dialect, we can find that h2’s paging method is using limit

3: Oracle paging mode
open the dialect class of Oracle according to different Oracle versions

we will find that the bottom layer of Oracle is rownum for paging

II. Problem-solving
1: since we only solve the paging problem now, here we create a custom dialect class TestH2Dialect, Inherited from H2Dialect

2: because our custom dialect class inherits from H2Dialect, we don’t need to pay attention to other dialect problems. We just need to rewrite the paging method to solve the above problems. Here we have taken oracle12 as an example
Create TestH2Dialect to customize dialect

public class TestH2Dialect extends H2Dialect {

    private static final TestOracle12LimitHandler LIMIT_HANDLER = new TestOracle12LimitHandler() ;

    @Override
    public LimitHandler getLimitHandler() {
        return LIMIT_HANDLER;
    }

}

Create Oracle paging processing class

public class TestOracle12LimitHandler extends AbstractLimitHandler {
    public boolean bindLimitParametersInReverseOrder;
    public boolean useMaxForLimit;
    public static final TestOracle12LimitHandler INSTANCE = new TestOracle12LimitHandler();

    TestOracle12LimitHandler() {
    }

    @Override
    public String processSql(String sql, RowSelection selection) {
        boolean hasFirstRow = LimitHelper.hasFirstRow(selection);
        boolean hasMaxRows = LimitHelper.hasMaxRows(selection);
        return !hasMaxRows ?sql : this.processSql(sql, this.getForUpdateIndex(sql), hasFirstRow);
    }
    @Override
    public String processSql(String sql, QueryParameters queryParameters) {
        RowSelection selection = queryParameters.getRowSelection();
        boolean hasFirstRow = LimitHelper.hasFirstRow(selection);
        boolean hasMaxRows = LimitHelper.hasMaxRows(selection);
        if (!hasMaxRows) {
            return sql;
        } else {
            sql = sql.trim();
            LockOptions lockOptions = queryParameters.getLockOptions();
            if (lockOptions != null) {
                LockMode lockMode = lockOptions.getLockMode();
                switch(lockMode) {
                    case UPGRADE:
                    case PESSIMISTIC_READ:
                    case PESSIMISTIC_WRITE:
                    case UPGRADE_NOWAIT:
                    case FORCE:
                    case PESSIMISTIC_FORCE_INCREMENT:
                    case UPGRADE_SKIPLOCKED:
                        return this.processSql(sql, selection);
                    default:
                        return this.processSqlOffsetFetch(sql, hasFirstRow);
                }
            } else {
                return this.processSqlOffsetFetch(sql, hasFirstRow);
            }
        }
    }

    private String processSqlOffsetFetch(String sql, boolean hasFirstRow) {
        int forUpdateLastIndex = this.getForUpdateIndex(sql);
        if (forUpdateLastIndex > -1) {
            return this.processSql(sql, forUpdateLastIndex, hasFirstRow);
        } else {
            this.bindLimitParametersInReverseOrder = false;
            this.useMaxForLimit = false;
            String offsetFetchString;
            if (hasFirstRow) {
                offsetFetchString = " offset ?rows fetch next ?rows only";
            } else {
                offsetFetchString = " fetch first ?rows only";
            }

            int offsetFetchLength = sql.length() + offsetFetchString.length();
            return (new StringBuilder(offsetFetchLength)).append(sql).append(offsetFetchString).toString();
        }
    }

    private String processSql(String sql, int forUpdateIndex, boolean hasFirstRow) {
        this.bindLimitParametersInReverseOrder = true;
        this.useMaxForLimit = true;
        String forUpdateClause = null;
        boolean isForUpdate = false;
        if (forUpdateIndex > -1) {
            forUpdateClause = sql.substring(forUpdateIndex);
            sql = sql.substring(0, forUpdateIndex - 1);
            isForUpdate = true;
        }

        int forUpdateClauseLength;
        if (forUpdateClause == null) {
            forUpdateClauseLength = 0;
        } else {
            forUpdateClauseLength = forUpdateClause.length() + 1;
        }

        StringBuilder pagingSelect;
        if (hasFirstRow) {
            pagingSelect = new StringBuilder(sql.length() + forUpdateClauseLength + 98);
            pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
            pagingSelect.append(sql);
            pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
        } else {
            pagingSelect = new StringBuilder(sql.length() + forUpdateClauseLength + 37);
            pagingSelect.append("select * from ( ");
            pagingSelect.append(sql);
            pagingSelect.append(" ) where rownum <= ?");
        }

        if (isForUpdate) {
            pagingSelect.append(" ");
            pagingSelect.append(forUpdateClause);
        }

        return pagingSelect.toString();
    }

    private int getForUpdateIndex(String sql) {
        int forUpdateLastIndex = sql.toLowerCase(Locale.ROOT).lastIndexOf("for update");
        int lastIndexOfQuote = sql.lastIndexOf("'");
        if (forUpdateLastIndex > -1) {
            if (lastIndexOfQuote == -1) {
                return forUpdateLastIndex;
            } else {
                return lastIndexOfQuote > forUpdateLastIndex ?-1 : forUpdateLastIndex;
            }
        } else {
            return forUpdateLastIndex;
        }
    }
    @Override
    public final boolean supportsLimit() {
        return true;
    }
    @Override
    public boolean bindLimitParametersInReverseOrder() {
        return this.bindLimitParametersInReverseOrder;
    }
    @Override
    public boolean useMaxForLimit() {
        return this.useMaxForLimit;
    }
}

3. Modify the dialect class used in the configuration file

to

III. summary
if you encounter other dialect problems later, you can use the same method to solve them