Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I retrieve a JDBC ResultSet as an ArrayList?

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.

like image 375
Deckard Avatar asked Mar 13 '09 11:03

Deckard


People also ask

How do I convert a ResultSet to a list?

You need to use ResultSet#getString to fetch the name . Now, each time you fetch one record, get the name field and add it to your list. Now, since you haven't given enough information about your DTO , so that part you need to find out, how to add this ArrayList to your DTO .

Which method is used to retrieve the ResultSet created in JDBC?

Invoke the Statement. executeQuery method to obtain the result table from the SELECT statement in a ResultSet object. In a loop, position the cursor using the next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods.

How do I get the ResultSet column?

You can get the name of a particular column using the getColumnName() method of the ResultSetMetadata interface. This method accepts an integer value representing the index of a column and returns a String value representing the name of the specified column.


1 Answers

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;
}
like image 197
Mark Avatar answered Oct 19 '22 10:10

Mark