How can I retrieve a JDBC ResultSet as an ArrayList?

Question

I'm doing a query to retrieve a large amount of IDs (integers). Instead of iterating millions of times through the ResultSet and copying everything one-by-one to an ArrayList, is there some way to simply retrieve everything as an ArrayList?

I understand that ResultSet is supposed to be iterated because the underlying implementation may be caching stuff, but in my situation I just need all the IDs straight away. I know I can set the FetchSize to a large number, but then I still have to retrieve the IDs one-by-one.

Clarification: the reason I want to do this is performance. Profiling shows me that doing ResultSet.next(), ResultSet.getInt() and ArrayList.add() millions of times takes quite some time. I figure that the database (I'm using H2, which is written in Java) probably has the array or list somewhere in memory, so I'm looking for a way to have it copied to me directly instead of through the ResultSet iterating interface.

Solution 1

Using the Apache DbUtils library you can easily return a ResultSet as a List of Maps.

public List query(String query) {
    List result = null;
    try {
        QueryRunner qrun = new QueryRunner();
        result = (List) qrun.query(connection, query, new MapListHandler());
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return result;
}

Solution 2

Put the code in a method. It's very simple to call methods...

Off the top of my head:

public static List<Integer> readInts(
     PreparedStatement statement
) throws SQLException {
     ResultSet results = statement.executeQuery();
     try {
         assert results.getMetaData().getColumnCount() == 1;

         List<Integer> ints = new ArrayList<Integer>();
         while (results.next()) {
             ints.add(Integer.valueOf(results.getInt(1)));
         }
         return ints;
     } finally {
         results.close();
     }
}

Then just call it as:

List<Integer> ids = readInts(myStatemnet);

Done.

Solution 3

If your problem is poor performance, tune the statement before executing it with

java.sql.Statement.setFetchSize(int)

Experiment with 100, 1000, 10000,.. This will avoid unnecessary roundtrips, which may be the cause of the slowness you mentioned.

Also, ArrayList.add() may be slow if it must resize the internal array many times, as it creates a new array and copies all data to there. Try LinkedList instead.