关闭流结果集(使用mysql jdbc)需要很长时间 [英] Closing a streaming result set (using mysql jdbc) takes a long time

问题描述

我正在使用以下代码创建准备好的语句和流式结果集.事实证明，在某些情况下，结果集相当大，但在消耗了几千行之后，我想关闭它.但是，关闭似乎遍历了所有其余结果-请参见以下链接，了解关闭的定义: http://love.isti.com/libs/MySQL/com/mysql/jdbc/RowDataDynamic.java

I am using the following code to create a prepared statement and a streaming result set. Turns out that in some cases the result set is fairly large but I would like to close it after consuming a couple of thousand rows. However, close seems to run through all remaining results -- see this link for the definition of close: http://love.isti.com/libs/MySQL/com/mysql/jdbc/RowDataDynamic.java

statement = db.conn().prepareStatement(query, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); db.setFetchSize(statement, kFetchSize);

statement = db.conn().prepareStatement(query, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); db.setFetchSize(statement, kFetchSize);

我想立即关闭结果集(和/或语句)，而无需遍历所有剩余结果.有可能吗?

I would like to close the result set (and/or statement) immediately without iterating through all the remaining results. Is that possible?

我还尝试将光标移动到结果集的末尾(afterLast)，但是流结果集不支持此操作.而且，不使用流式结果集不是一种选择，因为结果集有时可能非常大，而且我不想将所有结果都提供给客户端.

I also tried to move cursor to the end of the result set (afterLast) but that is not supported for streaming result sets. And, not using streaming result sets is not an option since result sets can sometimes be very large, and I don't want to get all of the results to the client.

谢谢

推荐答案

因此，正如我在对该问题的评论中所述，MySQL连接器的官方答案是，您必须流式传输所有结果集才能对其进行处理.关闭( http://dev.mysql .com/doc/refman/5.5/en/connector-j-reference-implementation-notes.html ).此外，流式传输结果发生时，您将无法再执行任何查询.

So as posted in my comment to the question, the official answer from the MySQL connector is that you have to stream all of the result set in order for it to close (http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-implementation-notes.html). Additionally, you can't perform any more queries while a streaming result is taking place.

作为一个完全令人作呕的黑客，我使用了反射技术来深入研究 RowDataDynamic (版本5.1.24)并伪造一个中断的异常，如下所示:

As a completely disgusting hack, I used reflection to go down into RowDataDynamic (ver. 5.1.24) and fake an interrupted exception, like so:

    final Class<?> rdClass = rd.getClass();
    final Field isInterruptedField = rdClass.getDeclaredField("isInterrupted");
    isInterruptedField.setAccessible(true);  // override 'protected' visibility
    isInterruptedField.set(rd, true);

请注意，您必须走下要处理的任何对象才能到达ResultSet.对我来说，我正在使用Hibernate的ScrollableResults类.这意味着从中获取ResultSet引用(实际上是其超类)，然后从那里获取RowData.

Note, you'll have to walk down whatever object you have a handle on to get to the ResultSet. For me, I was using Hibernate's ScrollableResults class. This meant getting ResultSet reference from it (its super class, actually), then RowData from there.

这将允许进行关闭操作，而无需传输其余结果.但是当我尝试回滚事务时，由于数据包大小不匹配，我得到了一个异常(我只是捕获并忽略了) ).使用Atomikos作为连接池，当事情清理后，我将看到有关接下来的几个连接的警告，但是一切仍然正常.

This will allow the close operation to take place without streaming the rest of the results HOWEVER I get an exception due to mismatched packet size when I try to rollback the transaction (which I just catch and ignore). Using Atomikos as the connection pool, I will see warnings about the next few connections as things get cleaned up, but everything still works ok.

显然，这种方法可能并不适用于所有人，但是至少在通过数据库查询进行处理或编写更复杂的逻辑来批量检索结果时，这是一种解决方法.

Clearly this approach may not work for everyone, but at least it's a workaround when doing the processing via the database query or writing more complicated logic to retrieve results in batches just won't work.

这篇关于关闭流结果集(使用mysql jdbc)需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！