与Statement对象的setFetchSize方法混淆 [英] Confusion with setFetchSize method of Statement Object

问题描述

最初，我问过这个问题

我解决了这通过将 fetchSize 设置为 Integer.MIN_VALUE ，但我对此有一些疑问

I solve this by setting fetchSize to Integer.MIN_VALUE, but I have some questions about this

1. 当我将fetchSize设置为10或另一个正整数时，在将其设置为 Integer.MIN_VALUE 之后它不起作用它有效，为什么会这样？


2. 如果我们设置负值然后它会给出非法值错误，但是 Integer.MIN_VALUE 是 -2147483648 那么为什么不给出错误？


3. 此表包含600万条记录，我已关闭结果集获取100或200条记录后，需要30-35秒的时间。


4. 减少关闭结果集。



1. When I set fetchSize to 10 or another positive integer then it does not work, after setting it to Integer.MIN_VALUE it works, why is this?
2. If we set negative value then it gives illegal value error but Integer.MIN_VALUE is -2147483648 so why is it not giving errors?
3. This table contains 6 million records and I closed resultset after fetching 100 or 200 records then it takes 30-35 seconds of time.
4. Solution to decrease time to close that resultset.

我想在这里添加更多内容
我用MySQL驱动程序对它进行了测试，它接受 Integer.MIN_VALUE
但是当我测试相同的代码时在SQL服务器中，它提供错误获取大小不能为负。如果我将其设置为10然后它可以工作，它也适用于Oracle。

I want to add something more here I have tested this with MySQL driver and it accept Integer.MIN_VALUE but when I test same code in SQL server then it gives error The fetch size cannot be negative. and if I set it to 10 then it works, it also works for Oracle.

推荐答案

MySQL驱动程序使用Integer.MIN_VALUE作为切换到流结果集模式的信号。它不用作值。请参阅文档，在结果集下。总结：

The Integer.MIN_VALUE is used by the MySQL driver as a signal to switch to streaming result set mode. It is not used as a value. See the documentation, under "Resultset". In summary:

默认情况下，ResultSets被完全检索并存储在内存中。您可以通过设置 stmt.setFetchSize（Integer.MIN_VALUE）; （与仅向前结合，读取 - 告知驱动程序一次一行地将结果流回一行只有结果集）。

By default, ResultSets are completely retrieved and stored in memory. You can tell the driver to stream the results back one row at a time by setting stmt.setFetchSize(Integer.MIN_VALUE); (in combination with a forward-only, read-only result set).

所以这非常特定于MySQL Connector / J驱动程序。

So this is very specific to the MySQL Connector/J driver.

至于为什么关闭结果 - set需要很长时间，这也是同一文档所暗示的：您必须先读取结果集中的所有行（或关闭它），然后才能对连接发出任何其他查询，否则将抛出异常。

即关闭结果集将首先读取所有剩余行，然后关闭结果集。由于读取行现在是逐行完成的，因此可能需要很长时间。 此问题中还介绍了此问题和解决方法/黑客攻击。

As for why closing the result-set takes a long time, that is also implied by the same documentation: "You must 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. "
I.e. closing the result-set will first read all remaining rows and then close the result-set. And since reading rows is now done row-by-row, it can take a long time. This problem and a workaround/hack is also described in this question.

看来（我还没有测试过）有一个替代流式结果集可以做你想要的（不使用MySQL限制条款），它涉及配置属性 useCursorFetch = true 并解释用法此处。

It appears (I have not tested it) there is an alternative to the streaming result set that might do what you want (without using the MySQL limit clause), it involves the configuration property useCursorFetch=true and usage is explained here.

这篇关于与Statement对象的setFetchSize方法混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！