使用MySQL将大型结果集串流。-StreaminglargeresultsetswithMySQL

52  

Only setting the fetch size is not the correct approach. The javadoc of Statement#setFetchSize() already states the following:

只设置fetch大小不是正确的方法。语句#setFetchSize()的javadoc已经声明了以下内容:

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database

给JDBC驱动程序一个提示，说明应该从数据库中获取的行数。

The driver is actually free to apply or ignore the hint. Some drivers ignore it, some drivers apply it directly, some drivers need more parameters. The MySQL JDBC driver falls in the last category. If you check the MySQL JDBC driver documentation, you'll see the following information (scroll about 2/3 down until header ResultSet):

实际上，驱动程序可以应用或忽略这个提示。一些驱动程序忽略它，一些驱动程序直接应用它，一些驱动程序需要更多的参数。JDBC驱动程序属于最后一类。如果您检查了MySQL JDBC驱动程序文档，您将看到以下信息(向下滚动大约2/3直到头结果集):

To enable this functionality, you need to create a Statement instance in the following manner:

要启用此功能，您需要以以下方式创建语句实例:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Please read the entire section of the document, it describes the caveats of this approach as well. Here's a relevant cite:

请阅读文档的整个部分，它还描述了这种方法的注意事项。这里有一个相关的引用:

There are some caveats with this approach. You will have to read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

这种方法有一些值得注意的地方。您必须读取结果集中的所有行(或关闭它)，才能对连接发出任何其他查询，否则将抛出异常。

(...)

(…)

If the statement is within scope of a transaction, then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases, statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.

如果语句位于事务的范围内，则在事务完成时释放锁(这意味着语句需要首先完成)。与大多数其他数据库一样，在读取语句上的所有挂起的结果或关闭语句的活动结果集之前，语句是不完整的。

If that doesn't fix the OutOfMemoryError (not Exception), then the problem is likely that you're storing all the data in Java's memory instead of processing it immediately as soon as the data comes in. This would require more changes in your code, maybe a complete rewrite. I've answered similar question before here.

如果这不能修复OutOfMemoryError(不是Exception)，那么问题很可能是您将所有数据存储在Java内存中，而不是在数据一进入就立即处理它。这将需要对代码进行更多的修改，可能还需要进行一次完整的重写。我之前也回答过类似的问题。

12  

Don't close your ResultSets twice.

不要关闭结果集两次。

Apparently, when closing a Statement it attempts to close the corresponding ResultSet, as you can see in these two lines from the stack trace:

显然，当关闭一个语句时，它试图关闭相应的ResultSet，您可以从堆栈跟踪中看到这两行:

DelegatingResultSet.close() line: 152
DelegatingPreparedStatement(DelegatingStatement).close() line: 163

()，()，()，()，()，()，()，()，()()，()()()()()

I had thought the hang was in ResultSet.close() but it was actually in Statement.close() which calls ResultSet.close(). Since the ResultSet was already closed, it just hung.

我原以为挂起是在ResultSet.close()，但实际上是在Statement.close()中调用ResultSet.close()。由于结果集已经关闭，所以它只是挂起了。

We've replaced all ResultSet.close() with results.getStatement().close() and removed all Statement.close()s, and the problem is now solved.

我们已经用results.getStatement().close()替换了所有ResultSet.close()，并删除了所有的语句。

4  

In case someone has the same problem, I resolved it by using the LIMIT clause in my query.

如果有人遇到同样的问题，我在查询中使用LIMIT子句来解决它。

This issue was reported to MySql as a bug (find it here http://bugs.mysql.com/bug.php?id=42929) which now has a status of "not a bug". The most pertinent part is:

这个问题被报告给MySql是一个bug(在这里可以找到http://bugs.mysql.com/bug.php?id=42929)，它现在的状态是“不是bug”。最相关的部分是:

There's no way currently to close a result set "midstream"

目前没有办法关闭结果集“中流”

Since you have to read ALL rows, you will have to limit your query results using a clause like WHERE or LIMIT. Alternatively, try the following:

由于必须读取所有行，因此必须使用WHERE或limit这样的子句限制查询结果。另外,试试以下:

ResultSet rs = ...
while(rs.next()) {
   ...
   if(bailOut == true) { break; }
}

while(rs.next()); // This will deplete the remaining rows on the stream

rs.close();

It may not be ideal, but at least it gets you past the hang on close.

这可能不太理想，但至少能让你通过近距离接触。

1  

If you are using spring jdbc then you need to use a preparedstatement creator in conjunction with SimpleJdbcTemplate to set the fetchSize as Integer.MIN_VALUE. Its described here http://neopatel.blogspot.com/2012/02/mysql-jdbc-driver-and-streaming-large.html

如果您正在使用spring jdbc，那么需要使用preparedstatement creator并结合SimpleJdbcTemplate将fetchSize设置为Integer.MIN_VALUE。这里描述的http://neopatel.blogspot.com/2012/02/mysql-jdbc-driver-and-streaming-large.html

0  

It hangs because even if you stop listening, the request still goes on. In order to close the ResultSet and Statement in the right order, try calling statement.cancel() first:

它挂起是因为即使您停止监听，请求仍然继续。为了以正确的顺序关闭ResultSet和语句，请尝试调用statement.cancel()首先:

public void close() {
    try {
        statement.cancel();
        if (resultSet != null)
            resultSet.close();
    } catch (SQLException e) {
        // ignore errors on closing
    } finally {
        try {
            statement.close();
        } catch (SQLException e) {
            // ignore errors on closing
        } finally {
            resultSet = null;
            statement = null;
        }
    }
}

0  

Scrollable Resultset ignore fetchSize and fetches all the rows at once causing out of meory error.

可滚动的Resultset忽略fetchSize并一次获取所有的行，导致了错误。

For me it worked properly when setting useCursors=true, otherwise The Scrollable Resultset ignores all the implementations of fetch size, in my case it was 5000 but Scrollable Resultset fetched millions of records at once causing excessive memory usage. underlying DB is MSSQLServer.

对于我来说，当设置useCursors=true时，它可以正常工作，否则，可滚动的Resultset会忽略所有的fetch大小实现，在我的情况下，它是5000，但是可滚动的Resultset立即获取了数百万条记录，导致内存占用过多。MSSQLServer底层数据库。

jdbc:jtds:sqlserver://localhost:1433/ACS;TDS=8.0;useCursors=true

jdbc:jtds::状态"置疑" / / localhost:1433 / ACS;TDS = 8.0;useCursors = true