I’ve been trying to write a bunch of scripts that perform some database administration tasks in Groovy. Groovy offers a several advantages over most of my other options. It runs practically everywhere. There are JDBC drivers available for every database that I have to work with. Most importantly to me though, SQL programming in Groovy is really simple.

At first I thought it was a Groovy limitation that was causing my scripts to run out of heap space when I ran them against large tables. It turned out to be the default behavior of the MySQL JDBC driver though.

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not 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.

The solution is to override three default settings. The first two override the default settings of the ResultSets that will be returned by the statement.
sql.setResultSetType( java.sql.ResultSet.TYPE_FORWARD_ONLY )
sql.setResultSetConcurrency( java.sql.ResultSet.CONCUR_READ_ONLY )

This one adds a closure that gets called every time a statement gets created.
sql.withStatement{ stmt -> stmt.setFetchSize( Integer.MIN_VALUE ) }