Confusion with setFetchSize method of Statement Ob

Question

Initially, I asked this question

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

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.

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.

Answer 1

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:

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).

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

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.

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.