Java 8 Friday Goodies: SQL ResultSet Streams

Posted on by lukaseder
At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem. We have blogged a couple of times about some nice Java 8 goodies, and now we feel it’s time to start a new blog series, the…

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

Java 8 Goodie: SQL ResultSet Streams

Yes, the SQL subject must be dealt with again. Even if last week, we promised an article on concurrency, there is one very important aspect of Java 8 lambdas and interoperability with “legacy” APIs that we need to talk about, first. Checked Exceptions Yes. Unfortunately, those beasts from the past still haunt us, more than ever when we’re using Java 8’s lambda expressions. Already before Java 8’s release, there are a couple of Stack Overflow questions related to the subject. Let’s remember how the IOExceptions caused issues when traversing the file system. Unless you write your own utility, you’ll have to resort to this beauty: 

Arrays.stream(dir.listFiles()).forEach(file -> {
    try {
        System.out.println(file.getCanonicalPath());
    }
    catch (IOException e) {
        throw new RuntimeException(e);
    }

    // Ouch, my fingers hurt! All this typing!
});
We think it is safe to say:
Java 8 and checked exceptions don’t match.tweet this
A workaround is to write your own CheckedConsumer that wraps the checked exception. Such a consumer will be highly reusable, but… Did you think of all the other FunctionalInterfaces? There are quite a few of them in the java.util.function package:
Some of the many types in java.util.function
Some of the many types in java.util.function

jOOλ – Fixing lambda in Java 8

jool-logo-blackWhile writing this Java 8 blog series, we’ve constantly run into the need to wrap checked exceptions inside lambda expressions. And what do we geeks do when we frequently run into a problem? We fix it! And we have created jOOλ (also jOOL, jOO-Lambda), ASL 2.0 licensed, where we have duplicated pretty much every FunctionalInterface that is available from the JDK to support checked exceptions. Here’s how you would use jOOλ in the above example: 

Arrays.stream(dir.listFiles()).forEach(
    Unchecked.consumer(file -> {
        // Throw all sorts of checked exceptions
        // here, we don't care...
        System.out.println(file.getCanonicalPath());
    })
);
The above example shows how you can simply ignore and pass checked exceptions as RuntimeExceptions. If you actually want to handle them, you can pass an exception handler lambda: 

Arrays.stream(dir.listFiles())
      .forEach(Unchecked.consumer(

    file -> {
        System.out.println(file.getCanonicalPath());
    },
    e -> {
        log.info("Log stuff here", e);
        throw new MyRuntimeException(e);
    }
);
The second example now seems equally verbose, but don’t worry. You will probably reuse that exception handler and fall back to this: 

Arrays.stream(dir.listFiles())
      .forEach(Unchecked.consumer(
    file -> {
        System.out.println(file.getCanonicalPath());
    },
    myExceptionHandler
);

jOOλ – Providing JDBC ResultSet Streams

Unfortunately, most efforts in the Java 8 Streams API were made in the area of correctly implementing parallelisable streams. While this is very useful for those of us actually doing parallel computing, for most others better integration with legacy APIs would have been better. One API that seriously deserves some lifting is JDBC, and we’ve blogged about this before. With jOOλ, you can now generate Streams directly from ResultSets or even from PreparedStatements. Here’s how you prepare: 

Class.forName("org.h2.Driver");
try (Connection c = getConnection()) {
    String sql = "select schema_name, is_default " +
                 "from information_schema.schemata " +
                 "order by schema_name";

    try (PreparedStatement stmt = c.prepareStatement(sql)) {
        // code here
    }
}
Now, all you have to do when using jOOλ is stream your PreparedStatements as such: 

SQL.stream(stmt, Unchecked.function(rs ->
    new SQLGoodies.Schema(
        rs.getString("SCHEMA_NAME"),
        rs.getBoolean("IS_DEFAULT")
    )
))
.forEach(System.out::println);
Where SQLGoodies.Schema is just an ordinary POJO. Some of the stream() method’s signatures are these ones: 

public static <T> Stream<T> stream(
    PreparedStatement stmt,
    Function<ResultSet, T> rowFunction
);

public static <T> Stream<T> stream(
    PreparedStatement stmt,
    Function<ResultSet, T> rowFunction,
    Consumer<? super SQLException> exceptionHandler
);
Others are available as well. That is awesome, isn’t it?
JDBC ResultSets should be Java 8 Streams.tweet this
Too bad, the above code didn’t make it into the JDK 8, as this would have been a chance to finally greatly improve on the JDBC API. Another, similar attempt at improving things has been done here by Julian Exenberger.

Java 8 alternatives of writing SQL

We’ve also published a couple of alternatives to jOOλ, using Java 8 with SQL here: https://www.jooq.org/java-8-and-sql

Conclusion

While Java 8’s lambda expressions are awesome, the new Streams API is pretty incomplete. When implementing the above, we had to implement our own ResultSetIterator, and write all this mess to wrap the iterator in a Stream: 

StreamSupport.stream(
    Spliterators.spliteratorUnknownSize(
        new ResultSetIterator<>(
            supplier, 
            rowFunction, 
            exceptionTranslator
        ), 0
    ), false
);
And it shouldn’t be necessary to write an Iterator in the first place, if only we were able to generate finite streams: 

// Unfortunately, this method doesn't exist
Stream.generate(
    // Supplier, generating new POJOs
    () -> { 
        rs.next(); 
        return new SQLGoodies.Schema(
            rs.getString("SCHEMA_NAME"),
            rs.getBoolean("IS_DEFAULT")
        );
    },

    // Predicate, terminating the Stream
    () -> { !rs.isLast(); }
);
While jOOλ is an acceptable intermediate solution, and the Guava guys are probably already working out how to fix their library, it is really too bad, that Java 8 is lacking such utility functionality. But we’re complaining on a high level. Next week, as promised, we’ll see a couple of examples related to concurrency, so stay tuned!

More on Java 8

In the mean time, have a look at Eugen Paraschiv’s awesome Java 8 resources page

Published by lukaseder

I made jOOQ

8 thoughts on “Java 8 Friday Goodies: SQL ResultSet Streams”

  1. “JDBC ResultSets should be Java 8 Streams”
    I don’t think so. This is a misconception about Stream in general. As http://download.java.net/jdk8/docs/api/java/util/stream/package-summary.html makes it clear:

    No storage. A stream is not a data structure that stores elements; instead, it conveys elements from a source such as a data structure, an array, a generator function, or an I/O channel, through a pipeline of computational operations.

    A ResultSet IS the storage, its eventually modifiable and you can eventually navigate back and forth.

    Reply

    1. Hi Christian, long time no see! :-)

      Thanks for joining the discussion. You are right, of course. ResultSets aren’t Streams, they’re more. But 90% of ResultSet usage matches the use-case of a Stream. It is a forward-only cursor (TYPE_FORWARD_ONLY) that may or may not be entirely consumed:

      A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row.

      The fact that JDBC ResultSets are so stateful and have so many operation modes probably originates from the fact that OO design might not have been well-established yet when JDBC was first designed. If JDBC was redesigned from scratch, we would certainly have distinct types for:

      • Read-only, forward-only
      • Read-only, scrollable
      • Read-write, forward-only (does that even make sense?)
      • Read-write, scrollable

      What do you think?

      Reply

      1. Well now it’s too late anyway, and a modifiable ResultSet is not the core of the problem. Any ResultSet is stateful by definition because it’s an iterator and represents a database cursor (it has a next() method). Because of that it’s not a function and thus it has side effects. Imagine

        Stream stream = … // Some provider
stream.parallel().peek( rs -> {
                          try { if (Math.random() > 0.5) rs.next(); } catch (SQLException e) {} }
                       )
      .forEach( rs -> {
                  try { System.out.println( rs.getRow() ); } catch (SQLException e) {} }
              );

        The result is pure chaos.
        If ResultSet would be really stateless and would just represent a row of data and not a cursor it could be used in a Stream. But because of the cursor state (R/W) is does not act like a pure function and it’s impossible to get rid of the state in ResultSet in the API anymore.

        Reply

        1. What the article was trying to point out is that there should be a way to interact with databases through Streams. The semantics of transactions and ACID should enable a database to provide an immutable view of the data at the time the query was executed. I think that this would match the philosophy behind Streams.

          But you’re right of course. It is too late to get rid of this statefulness in ResultSet. So the suggested implementation is misleading.

          Reply

  2. When you implement a ResultSetIterator just for the (Java 8) Stream support it looks more natural to me to implement Spliterator directly instead. Is there a reason why you didn’t?

    Reply

    1. You’re right, that would’ve been easier in hindsight. There are a couple of reasons why I haven’t done it:

      • I actually didn’t check if this was possible
      • I already had a ResultSetIterator (in jOOQ)
      Reply

  3. Re: Stream.generate

    I’m not saying it’s pretty, but I’m messing with Stream.iterate, it should work?

    Stream.iterate(
    null,
    it -> rs.next(),
    it -> {
        return new SQLGoodies.Schema(
            rs.getString("SCHEMA_NAME"),
            rs.getBoolean("IS_DEFAULT")
        );
    }).skip(1); // Skip the seed null, lol
    Reply

Leave a Reply