Java Streams to iterate over a ResultSet object

Question

I have the following code snippet

ResultSet rs = stmt.executeQuery(); 
List<String> userIdList = new ArrayList<String>(); 
while(rs.next()){
    userIdList.add(rs.getString(1));
}

Can I make use of Java streams/Lambda expressions to perform this iteration instead of a while loop to populate the List?

If you want to use one of `Stream`’s factory methods, you’re out of luck as the “iterate until `next()` returns `false`” logic cannot be expressed with them in Java 8. There will be support in Java 9, however, the checked `SQLException` prevents you from writing concise lambda expressions. You’ll most likely end up with a custom `Spliterator`, like in [this answer](http://stackoverflow.com/a/32232173/2711488), which covers a bit more than just iterating over the `ResultSet`. That’s useful as a factory when you will use it often, but for converting a single loop, it would be overkill. — Holger, Mar 16 '17 at 17:46

Gerald Mücke · Answer 1 · 2019-02-16T17:26:54.370

You may create a wrapper for the ResultSet making it an Iterable. From there you can iterate as well as create a stream. Of course you have to define a mapper function to get the iterated values from the result set.

The ResultSetIterable may look like this

public class ResultSetIterable<T> implements Iterable<T> {

  private final ResultSet rs;
  private final Function<ResultSet, T> onNext;

  public ResultSetIterable(ResultSet rs, CheckedFunction<ResultSet, T> onNext){
    this.rs = rs;
    //onNext is the mapper function to get the values from the resultSet
    this.onNext = onNext;
  }

  private boolean resultSetHasNext(){
     try {
       hasNext = rs.next();
     } catch (SQLException e) {
       //you should add proper exception handling here
       throw new RuntimeException(e);
     }
  }


  @Override
  public Iterator<T> iterator() {

    try {
        return new Iterator<T>() {

            //the iterator state is initialized by calling next() to 
            //know whether there are elements to iterate
            boolean hasNext = resultSetHasNext();


            @Override
            public boolean hasNext() {
                return hasNext;
            }

            @Override
            public T next() {

                T result = onNext.apply(rs);
                //after each get, we need to update the hasNext info
                hasNext = resultSetHasNext();
                return result;
            }
        };
    } catch (Exception e) {
        //you should add proper exception handling here
        throw new RuntimeException(e);
    }
  }

  //adding stream support based on an iteratable is easy
  public Stream<T> stream() {
    return StreamSupport.stream(this.spliterator(), false);
  }
}

Now that we have our wrapper, you could stream over the results:

ResultSet rs = stmt.executeQuery(); 
List<String> userIdList = new ResultSetIterable(rs, rs -> rs.getString(1)).stream()
                                                                          .collect(Collectors.toList())

}

EDIT

As Lukas pointed out, the rs.getString(1) may throw a checked SQLException, therefor we need to use a CheckedFunction instead of a java Function that would be capable of wrapping any checked Exception in an unchecked one. A very simple implementation could be

public interface CheckedFunction<T,R> extends Function<T,R> {

  @Override
  default R apply(T t) {

    try {
        return applyAndThrow(t);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
  }

  R applyAndThrow(T t) throws Exception;
}

Alternatively you could use a library with such a function, i.e. jooλ or vavr

Or you can use `org.apache.commons.dbutils.ResultSetIterator.iterable(rowSet)` — Florian F, Sep 19 '17 at 19:09
This doesn't work. `Function` doesn't allow for throwing `SQLException`, so your lambda `rs -> rs.getString(1)` doesn't compile. — Lukas Eder, Feb 15 '19 at 09:25
Thank you for pointing this out. Could be solved by using a "CheckedFunction" instead, that would allow for code that throws checked Exception — Gerald Mücke, Feb 15 '19 at 10:41
... Speaking of which, your `boolean hasNext = rs.next();` also throws an uncaught checked exception in the iterator constructor. The wrapping try catch is not applicable here. — Lukas Eder, Feb 16 '19 at 12:38
fixing that would make "copy and pasting from stackoverflow" way too easy :) — Gerald Mücke, Feb 16 '19 at 17:16

score 3 · Answer 2 · answered Feb 15 '19 at 09:24

If using a third party library is an option, you could use jOOQ, which supports wrapping JDBC ResultSet in jOOQ Cursor types, and then stream them. For example, using DSLContext.fetchStream()

Essentially, you could write:

try (ResultSet rs = stmt.executeQuery()) {
    DSL.using(con)                       // DSLContext
       .fetchStream(rs)                  // Stream<Record>
       .map(r -> r.get(0, String.class)) // Stream<String>
       .collect(toList());
}

Disclaimer: I work for the vendor.

score 0 · Answer 3 · answered Dec 19 '19 at 03:48

Try library: abacus-jdbc

List<String> userIdList = StreamEx.<String> rows(resultSet, 1).toList(); // Don't forget to close ResultSet

Or: If you want to close the ResultSet after toList.

StreamEx.<String> rows(resultSet, 1).onClose(() -> JdbcUtil.closeQuitely(resultSet)).toList();

Or: If you use the utility classes provided in abacus-jdbc:

String sql = "select user_id from user";
// No need to worry about closing Connection/Statement/ResultSet manually. It will be took care by the framework.
JdbcUtil.prepareQuery(dataSource, sql).stream(String.class).toList();
// Or:
JdbcUtil.prepareQuery(dataSource, sql).toList(String.class);

Disclaimer：I'm the developer of abacus-jdbc.

Java Streams to iterate over a ResultSet object

3 Answers3

EDIT