Always Crispy

Every now and then, you need to pull a massive amount of data from a database, more than can fit into memory reasonably. To this end, you can set the fetch size for your statement so that the database driver will pull back more manageable chunks of data. For example, if you set the fetch size to 100, the driver should pull back 100 row chunks. A new chunk is pulled when needed as you iterator over the corresponding result set.

Now setting the fetch size for most databases should be sufficient however, not for Postgresql. If you want to enable fetching, you also have to turn auto commit off as well. The reason has to deal with how Postgresql fetches chunks of data; it requires a transaction block and if auto commit is on, it cannot get one. You would think that the Postgresql JDBC driver would be smart enough to turn auto commit off if the fetch size is set to anything other than 0. Anyhow, as per usual, some example code is below:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class BigQueryExample {
   public static void main (String args[]) {
      Properties connectionProps = new Properties();
      connectionProps.put("user", "enter username");
      connectionProps.put("password", "enter password");
      Connection conn = null;
      Statement statement = null;
      ResultSet rs = null;
      try {
         conn = DriverManager.getConnection("enter url", connectionProps);
         conn.setAutoCommit(false);
         statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
         statement.setFetchSize(100);
         rs = statement.executeQuery("SELECT name FROM giant_table_of_users");
         while (rs.next()) {
            System.out.println("Hi " + rs.getString("name"));
         }
      }
      catch (SQLException se) {
         System.err.println("some sort of jdbc error encountered");
         throw new RuntimeException(("some sort of jdbc error encountered", se);
      }
      finally {
         rs.close();
         statement.close();
         conn.close();
      }
   }
}