java 在PreparedStatement中设置FetchSize时,Oracle驱动程序中出现OutOfMemory错误

xuo3flqw  于 2023-01-16  发布在  Java
关注(0)|答案(1)|浏览(326)

我有这段代码,这段代码基本上在PreparedStatement中设置了限制,以避免加载整个表数据。

PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        if (getDataSheet().isPagingEnabled()) {
            pstmt.setFetchSize(getDataSheet().getPageSize() + 1);
        }
        
        if (getDataSheet().isDisableTotalRowsCount() && maxRecords >= 0) {
            if (getDataSheet().isPagingEnabled()) {
                pstmt.setMaxRows(getCurrentPageSize());
            } else {
                pstmt.setMaxRows(maxRecords);
            }
        }
        
        //the rest of code.
        
    } catch(Exception ex) {
        //handle exception
    }

这段代码对于所有数据库供应商都能正常工作,但在Oracle驱动程序中有一个例外:如果我在PreparedStatement中使用setFetchSize,并且页面大小超过20000,则驱动程序会抛出OutOfMemoryError

Caused by: java.lang.OutOfMemoryError: Java heap space
at oracle.jdbc.driver.PhysicalConnection.getCharBuffer(PhysicalConnection.java:7018)
at oracle.jdbc.driver.OracleStatement.prepareAccessors(OracleStatement.java:907)
at oracle.jdbc.driver.T4CTTIdcb.receiveCommon(T4CTTIdcb.java:261)
at oracle.jdbc.driver.T4CTTIdcb.receive(T4CTTIdcb.java:127)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:992)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203)

你知道为什么会在Oracle中发生这种情况吗?在DB级别有什么要做的吗?

a0x5cqrl

a0x5cqrl1#

与您所想的相反,setFetchSize并不限制检索的记录数量,它决定(或提示)驱动程序应该预取和缓存多少记录。例外是MySQL,它总是预取所有记录,除非您将提取大小设置为Integer.MIN_VALUE
驱动程序只是检索了太多的行,导致内存耗尽。

相关问题