Can I load mysql data trunk by trunk in java?

Question

Hi all, Currently I am using statement.executeQuery(qStr) in java to select a large amount of data from mysql. Unfortunatly, java keeps running out of memory at the statement.executeQuery(qStr) statement with exception java.lang.OutOfMemoryError: Java heap space. I am wondering if there is a method to stream load data from mysql. So that I can handle the selected data trunk by trunk to avoid running out of memory?

Note that: I am using eclipse and this post shows me how to increase the heap memory for java to use inside eclipse. But after I followed it's method, i am still running into the same problem.

Thanks in advance.

Answer 1

Yes, you can set the FetchSize on a Statement to Integer.MIN_VALUE , the ResultSet should be TYPE_FORWARD_ONLY as well, though I believe that's the default.. MySQL treats that specially and enables streaming of the ResultSet instead of reading it all into memory, it's documented here

PreparedStatement stmt =  
 conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Answer 2

By default, ResultSets are completely retrieved and stored in memory. If you are dealing with huge result sets, your heap will quickly be exhausted. You can change this behavior by creating the Statement instance like this:

 stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
          java.sql.ResultSet.CONCUR_READ_ONLY);
 stmt.setFetchSize(Integer.MIN_VALUE);

This creates a

forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

See the MySQL documentation for more details and the caveats: http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-implementation-notes.html