1

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.

Community
  • 1
  • 1
Darshan Patel
  • 3,176
  • 6
  • 26
  • 49
  • `Integer.MIN_VALUE` can be tricky. Just check out the results of the following method calls. `System.out.println(Math.abs(-1));`, `System.out.println(Math.abs(Integer.MIN_VALUE + 1));` and `System.out.println(Math.abs(Integer.MIN_VALUE));` I am not saying this is the reason for the behaviour you are experiencing but it might be. One could only tell by investigating the code that lies underneath the database abstraction you are using. – Jagger Oct 07 '14 at 09:34
  • Yes thats correct but that is because of integer overflows – Darshan Patel Oct 07 '14 at 09:55
  • And how can you guarantee that the code underneath the DB access abstraction is not doing anything that has to do with overflows? – Jagger Oct 07 '14 at 10:41

1 Answers1

8

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.

Community
  • 1
  • 1
vanOekel
  • 6,358
  • 1
  • 21
  • 56
  • thanks for help according to me that the perfect answer with all useful references. – Darshan Patel Oct 08 '14 at 07:31
  • You write that `Integer.MIN_VALUE` is used as a signal to switch to streaming mode. And then you write that `stmt.setFetchSize(Integer.MIN_VALUE);` is used to get one row at a time. So we have one same thing used for two different purposes ? How can I switch to streaming mode and have multiple rows at a time ? – Stephane Mar 26 '17 at 09:13
  • 1
    @Stephane 1) using streaming mode results in fetching rows one by one. 2) you can't, use the alternative (follow the link after `useCursorFetch`). – vanOekel Mar 29 '17 at 09:36
  • I did follow the link and implemented the solution which solved my issue. I configured the connection with streaming with a fetching amount of 50. – Stephane Mar 31 '17 at 11:43