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?

Answer 1

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

Answer 2

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.