OutOfMemoryError when reading large volumes of records from MySQL

When loading a large number of records from MySQL, an out of memory error (exception) may be thrown by the MyQSL connector. The connector reads all the data in memory (buffering) before it returns the result set. This may not be the case for other JDBC providers.

The follow code fragment will fail on the third line (shown in red) when invoking the executeQuery() method if the memory required to load the whole result is larger than the allocated heap size.

Statement statement = connection.createStatement();
ResultSet resultSet = 
    statement.executeQuery("SELECT * FROM `large_table`");
while(resultSet.next()){
}

One possible solution is to increase the heap size but this may not be required, especially if you are accessing small parts from the result set at one time. Increasing the heap size will only allow the program to consumes more resources that it actually requires.

The MYSQL statement interface (com.mysql.jdbc.Statement) provides a method called enableStreamingResults() which enables streaming over buffering, (as shown in red in the following code fragment).

Statement statement = connection.createStatement();
// This prevents the MYSQL connector from loading all the 
// results into memory
((com.mysql.jdbc.Statement) statement).enableStreamingResults();
ResultSet resultSet = 
    statement.executeQuery("SELECT * FROM `large_table`");
while(resultSet.next()){
}

This allows the code to read from a large table without consuming a large amount of memory.