Out of memory when doing a big query?

Question

I received this error when trying to do big query.

java.lang.OutOfMemoryError: Java heap space

I've searched and found that applying setAutoCommit(false) and setFetchSize methods to my prepared statement might help handling big query. However, when I used it, I received this error.

java.sql.SQLException: Illegal value for setFetchDirection().

What is the proper and easy way to handle large query?

What is the proper way to use setFetchSize?

Answer 1

Assuming you are using the MySQL Connector/J driver provided by MySQL, I believe the solution is found in this manual page (notice parameter 1 of Connection::createStatement()):

If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

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