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?

@AbdullahShaikh No. it just ends in one iteration inside the while loop. the value of i on exiting is 2. — R11G
– R11G, Commented Mar 20, 2013 at 8:43

bad_coder · Accepted Answer · 2020-03-26 12:51:30Z

33

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();

edited Mar 26, 2020 at 12:51

bad_coder

13.2k20 gold badges59 silver badges95 bronze badges

answered Mar 20, 2013 at 9:00

Sean Landsman

7,1972 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R11G Over a year ago

Yes. resultset is NOT null. the second point is the issue. Question - Do I need to know numberOfColumns before hand? And does that mean hard-code it?

MarkSchoonover Over a year ago

The code in the accepted answer won't get to the last column. ArrayLists index start at 0 but result sets start at 1. If metadata returns 11 for the number of columns, (i < numberOfColumns) will go from 1 to 10. It should be (i <= numberOfColumns) to get to the last column of the resultset.

Lukas Eder · Accepted Answer · 2016-08-23 08:03:15Z

12

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)

edited Aug 23, 2016 at 8:03

answered Sep 23, 2014 at 13:43

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

3 Comments

Adam Siemion Over a year ago

This looks really nice! convinced me to take a clooser look at JOOQ.

thg Over a year ago

nice but unfortunately jooQ is rather pricey for commercial databases

Lukas Eder Over a year ago

@thg: You don't need jOOQ for such a simple query. You can easily use any of the other mentioned APIs like Spring JDBC, Apache DbUtils, or roll your own. The idea stays the same.

Collectives™ on Stack Overflow

Iterating over ResultSet and adding its value in an ArrayList

2 Answers 2

2 Comments

jOOQ 3.8 or less

jOOQ 3.9

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

jOOQ 3.8 or less

jOOQ 3.9

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related