17 Jan 09 MySQL streaming setting fetchSize to Integer.MIN_VALUE

Have you ever experienced a java.lang.OutOfMemoryError: Java heap space
due to too big selects?
We can avoid that thanks to the fetchSize parameter initialized at statement creation time. FetchSize decides how many rows of the select MySQL must store in the buffer before dealing with the data. Setting that parameter to it’s minimum value we will be able to do streaming with all rows.
The minimum value is still pretty odd for me as I thought it would be 1 (thinking 1 was the minum numer of rows to be stored in the buffer) but it is -2^31, the minimum value java can store in an Integer!

Another solution would be to paginate the select setting a limit value. The problem with this is that the more selects we do, the slower MySQL will deal with rows. Doing streamaing with the fetchSize parameter we just need one select and the “rows deal with speed” does not decrease. MySQL will deal with the rows buffering by itself.

The statement creation should look like this:

try {
Connection c = getConnection();
stmt = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
…

Tags: Java, MySQL