Iterating over ResultSet and adding its value in an ArrayList

Question

I am iterating over an ResultSet and trying to copy its values in an ArrayList. The problem is that its traversing only once. But using resultset.getString("Col 1") to resultset.getString('Col n") is showing all entries of all columns. Below is the code snippet -

ResultSet resultset = null; ArrayList<String> arrayList = new ArrayList<String>();  int i = 1; while (resultset.next()) {                   arrayList.add(resultset.getString(i++));     System.out.println(resultset.getString("Col 1"));     System.out.println(resultset.getString("Col 2"));     System.out.println(resultset.getString("Col n")); } 

The only value of ResultSet getting copied into ArrayList is for column 1. And then while exits. But I can see the value of all columns. Why?

Answer 1

If I've understood your problem correctly, there are two possible problems here:

• resultset is null - I assume that this can't be the case as if it was you'd get an exception in your while loop and nothing would be output.
• The second problem is that resultset.getString(i++) will get columns 1,2,3 and so on from each subsequent row.

I think that the second point is probably your problem here.

Lets say you only had 1 row returned, as follows:

Col 1, Col 2, Col 3  A    ,     B,     C 

Your code as it stands would only get A - it wouldn't get the rest of the columns.

I suggest you change your code as follows:

ResultSet resultset = ...; ArrayList<String> arrayList = new ArrayList<String>();  while (resultset.next()) {                           int i = 1;     while(i <= numberOfColumns) {         arrayList.add(resultset.getString(i++));     }     System.out.println(resultset.getString("Col 1"));     System.out.println(resultset.getString("Col 2"));     System.out.println(resultset.getString("Col 3"));                         System.out.println(resultset.getString("Col n")); } 

Edit:

To get the number of columns:

ResultSetMetaData metadata = resultset.getMetaData(); int numberOfColumns = metadata.getColumnCount(); 

Answer 2

Just for the fun, I'm offering an alternative solution using jOOQ and Java 8. Instead of using jOOQ, you could be using any other API that maps JDBC ResultSet to List, such as Spring JDBC or Apache DbUtils, or write your own ResultSetIterator:

jOOQ 3.8 or less

List<Object> list = DSL.using(connection)    .fetch("SELECT col1, col2, col3, ...")    .stream()    .flatMap(r -> Arrays.stream(r.intoArray()))    .collect(Collectors.toList()); 

jOOQ 3.9

List<Object> list = DSL.using(connection)    .fetch("SELECT col1, col2, col3, ...")    .stream()    .flatMap(Record::intoStream)    .collect(Collectors.toList()); 

(Disclaimer, I work for the company behind jOOQ)