Don’t Miss out on Writing Java 8 SQL One-Liners with jOOλ or jOOQ
More and more people are catching up with the latest update to our platform by adopting functional programming also for their businesses.
At Data Geekery, we’re using Java 8 for our jOOQ integration tests, as using the new Streams API with lambda expressions makes generating ad-hoc test data so much easier.
However, we don’t feel that the JDK offers as much as it could, which is why we have also implemented and open-sourced jOOλ, a small utility library that patches those short-comings.
Note, we don’t aim to replace more sophisticated libraries like functionaljava. jOOλ is really just patching short-comings.
Putting lambdas to work with jOOλ or jOOQ
I’ve recently encountered this Stack Overflow question, which asked for streaming a result set with all columns into a single list. For example:
Input
+----+------------+------------+ | ID | FIRST_NAME | LAST_NAME | +----+------------+------------+ | 1 | Joslyn | Vanderford | | 2 | Rudolf | Hux | +----+------------+------------+
Output
1 Joslyn Vanderford 2 Rudolf Hux
This is a typical school-book example for using functional programming rather than an iterative solution:
Iterative solution
ResultSet rs = ...; ResultSetMetaData meta = rs.getMetaData(); List<Object> list = new ArrayList<>(); while (rs.next()) { for (int i = 0; i < meta.getColumnCount(); i++) { list.add(rs.getObject(i + 1)); } }
Truth is, the iterative solution isn’t all that bad, but let’s learn how this could be done with functional programming.
Using jOOλ
We’re using jOOλ for this example for a couple of reasons:
- JDBC didn’t really adopt the new features. There is no simple
ResultSet
toStream
conversion, even if there should be. - Unfortunately, the new functional interfaces do not allow for throwing checked exceptions. The
try .. catch
blocks inside lambdas don’t exactly look nice - Interestingly, there is no way of generating a finite stream without also implementing an
Iterator
orSpliterator
So, here’s the plain code:
ResultSet rs = ...; ResultSetMetaData meta = rs.getMetaData(); List<Object> list = Seq.generate() .limitWhile(Unchecked.predicate(v -> rs.next())) .flatMap(Unchecked.function(v -> IntStream .range(0, meta.getColumnCount()) .mapToObj(Unchecked.intFunction(i -> rs.getObject(i + 1) )) )) .toList()
So far, this looks about as verbose (or a bit more) than the iterative solution. As you can see, a couple of jOOλ extensions were needed here:
// This generate is a shortcut to generate an // infinite stream with unspecified content Seq.generate() // This predicate-based stream termination // unfortunately doesn't exist in the JDK // Besides, the checked exception is wrapped in a // RuntimeException by calling Unchecked.wrapper(...) .limitWhile(Unchecked.predicate(v -> rs.next())) // Standard JDK flatmapping, producing a "nested" // stream of column values for the "outer" stream // of database rows .flatMap(Unchecked.function(v -> IntStream .range(0, meta.getColumnCount()) .mapToObj(Unchecked.intFunction(i -> rs.getObject(i + 1) )) )) // This is another convenience method that is more // verbose to write with standard JDK code .toList()
Using jOOQ
jOOQ has even more convenience API to operate on result records of your SQL statement. Consider the following piece of logic:
ResultSet rs = ...; List<Object> list = DSL.using(connection) .fetch(rs) .stream() .flatMap(r -> Arrays.stream(r.intoArray())) .collect(Collectors.toList());
Note that the above example is using standard JDK API, without resorting to jOOλ for convenience. If you want to use jOOλ with jOOQ, you could even write:
ResultSet rs = ...; List<Object> list = Seq.seq(DSL.using(connection).fetch(rs)) .flatMap(r -> Arrays.stream(r.intoArray())) .toList();
Easy? I would say so! Let’s remember that this example:
- Fetches a JDBC ResultSet into a Java Collection
- Transforms each record in the result set into an array of column values
- Transforms each array into a stream
- Flattens that stream into a stream of streams
- Collects all values into a single list
Whew!
Conclusion
We’re heading towards exciting times! It will take a while until all Java 8 idioms and functional thinking will feel “natural” to Java developers, also in the enterprise.
The idea of having a sort of data source that can be configured with pipelined data transformations expressed as lambda expressions to be evaluated lazily is very compelling, though. jOOQ is an API that encapsulates SQL data sources in a very fluent and intuitive way, but it doesn’t stop there. jOOQ produces regular JDK collections of records, which can be transformed out-of-the-box via the new streams API.
We believe that this will drastically change the way the Java ecosystem will think about data transformation. Stay tuned for more examples on this blog!
Reference: | Don’t Miss out on Writing Java 8 SQL One-Liners with jOOλ or jOOQ from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |