Java 8 Friday Goodies: SQL ResultSet Streams

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: -> {
    try {
    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:
    Unchecked.consumer(file -> {
        // Throw all sorts of checked exceptions
        // here, we don't care...

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:

    file -> {
    e -> {"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:
    file -> {

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:

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:, Unchecked.function(rs ->
    new SQLGoodies.Schema(

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:


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:
        new ResultSetIterator<>(
        ), 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
    // Supplier, generating new POJOs
    () -> {; 
        return new SQLGoodies.Schema(

    // 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

The JDBC Boolean Compatibility List

Interestingly, boolean types have been introduced only late in the SQL standard, namely in SQL:1999. Even today, not all databases natively support BOOLEAN or BIT types. Most importantly, we can still wait for them in Oracle for a while. Here’s “Ask Tom”‘s point of view from 2002 on the subject:
Oracle. Why U No Boolean? tweet this
People have worked around this limitation by using numeric or string literals instead. For instance 1 / 0, Y / N, T / F or the SQL standard 'true' / 'false'.

Booleans in JDBC

From a JDBC API perspective, boolean values can be set as bind values through PreparedStatement.setBoolean() or fetched from result sets through ResultSet.getBoolean(), and similar methods. If your database supports booleans, the Java boolean type nicely maps to SQL BOOLEAN – even if Java’s Boolean wrapper type would have been a better fit to respect NULLs. But if you’re storing boolean values in INTEGER, CHAR(1) or VARCHAR(1) columns, things look differently in various databases. Consider the following example:

CREATE TABLE booleans (
  val char(1)

And then, run this Java program (we’re using jOOQ to keep things concise)

try {
       "insert into boolean (val) values (?)", true);
catch (Exception e) {

   .fetch("select * from booleans");

Not all databases / JDBC drivers support the above. These databases will run the above program:
  • Firebird (inserts ‘Y’ or ‘N’)
  • HSQLDB (inserts ‘1’ or ‘0’)
  • IBM DB2 (inserts ‘1’ or ‘0’)
  • MariaDB (inserts ‘1’ or ‘0’)
  • Microsoft Access (inserts ‘1’ or ‘0’)
  • MySQL (inserts ‘1’ or ‘0’)
  • Oracle (inserts ‘1’ or ‘0’)
  • SQL Server (inserts ‘1’ or ‘0’)
  • Sybase (inserts ‘1’ or ‘0’)
… whereas these databases will throw an exception:
  • Derby
  • H2
  • Ingres
  • PostgreSQL
  • SQLite

Booleans in the SQL standard

It is worth mentioning, that the SQL standard specifies how to deal with boolean to string conversion in the specification of the CAST() function:
6.13 <cast specification>
10) If TD is fixed-length character string, 
   then let LTD be the length in characters
   of TD.
e) If SD is boolean, then
i) If SV is True and LTD is not less than 4, 
   then TV is 'TRUE' extended on the right by
ii) If SV is False and LTD is not less than 5,
   then TV is 'FALSE' extended on the right by
   LTD–5 <space>s.
iii) Otherwise, an exception condition is 
   raised: data exception — invalid character
   value for cast.
So, most Open Source databases show what could be interpreted as the “correct” behaviour, even if from a historic perspective, 1/0 should be accepted behaviours. Beware of this limitation when using an Open Source test database! For more information about this and the H2 database, please refer to this thread on the H2 user group.

Java 8 Friday Goodies: Lambdas and SQL

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. tweet this

Java 8 Goodie: Lambdas and SQL

If you’re used to writing Groovy, this may appear “so 2003” to you. We know. Groovy has known a very useful way to write string-based SQL since its early days. Here’s an example written in Groovy (see the official docs here):

import groovy.sql.Sql
sql = Sql.newInstance( 
    'jdbc:h2:~/test', 'sa', '', 
    'org.h2.Driver' )
    'select * from information_schema.schemata' 
) { 
    println "$it.SCHEMA_NAME -- $it.IS_DEFAULT" 

Note also Groovy’s built-in String interpolation, where you can put expressions into strings. But we’re in Java land, and with Java 8, things get better in the Java / SQL integration as well, if we’re using third-party libraries, instead of JDBC directly. In the following examples, we’re looking at how to fetch data from an H2 database and map records into custom POJOs / DTOs using these three popular libraries: As always, the sources are also available from GitHub. For these tests, we’re creating a little POJO / DTO to wrap schema meta-information:

class Schema {
    final String schemaName;
    final boolean isDefault;

    Schema(String schemaName, boolean isDefault) {
        this.schemaName = schemaName;
        this.isDefault = isDefault;

    public String toString() {
        return "Schema{" +
               "schemaName='" + schemaName + '\'' +
               ", isDefault=" + isDefault +

Our main method will get an H2 connection through DriverManager:

try (Connection c = getConnection(
        "jdbc:h2:~/test", "sa", "")) {

  String sql = "select schema_name, is_default "+
               "from information_schema.schemata "+
               "order by schema_name";
  // Library code here...

Now, how does Java 8 improve upon the jOOQ API, when using String-based SQL? Greatly! Check out the following little query:

   .map(r -> new Schema(
       r.getValue("SCHEMA_NAME", String.class),
       r.getValue("IS_DEFAULT", boolean.class)

This is how things should be, right? Note that jOOQ’s native APIs are also capable of mapping the database Record onto your POJO directly, as such:


Things look just as nice when doing the same with Spring JDBC and RowMapper (note, the following still throws checked SQLExceptions):

new JdbcTemplate(
        new SingleConnectionDataSource(c, true))
    .query(sql, (rs, rowNum) -> 
        new Schema(

… and if you’re using Apache DbUtils, you can do almost the same:

new QueryRunner()
    .query(c, sql, new ArrayListHandler())
    .map(array -> new Schema(
        (String) array[0],
        (Boolean) array[1]


All three solutions are more or less equivalent and quite lean. The point here, again, is that Java 8 will improve all existing APIs. The more unambiguous (few overloads!) methods accepting SAM arguments (single abstract method types), the better for a Java 8 integration.
Java 8 and SQL look very lean tweet this
Next week, we’re going to see a couple of things that will greatly improve when using the java.util.Map API

More on Java 8

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

JDBC 4.0’s Lesser-known and Methods

When I talk about jOOQ at conferences, I always show this slide containing a bunch of very common JDBC mistakes that people often commit:

Six common JDBC bugs in this image
Six common JDBC bugs in this image

Can you find the bugs? Some of them are obvious, such as:

  • Line 4: Syntax errors resulting from bad concatenation on line 3
  • Line 7: Syntax errors and SQL injection risk due to variable inlining
  • Line 8: Wrong bind index resulting from a potential mismatch on line 3
  • Line 14: Wrong column name due to sloppy rename
  • Line 18: Bad resource management

But then, there’s another very subtle bug that most people are unaware of because the fix was only possible since the upgrade in Java 6 / JDBC 4.0. See the solution, below:

Solution to the previous six bugs
Solution to the previous six bugs

With JDBC 4.0, the and the methods were introduced. While calling them is optional, it may be a very bad idea not to call them as early as possible, as you should not rely on the garbage collector to kick in early enough to free these resources. In fact, in certain databases / JDBC drivers, LOBs can outlive individual statements and/or transactions. They’re beasts of their own. If you’re reading through the JDBC tutorial (and also in the JDBC specification), it says:

Blob, Clob, and NClob Java objects remain valid for at least the duration of the transaction in which they are created. This could potentially result in an application running out of resources during a long running transaction.

The same is true for arrays, which also have an method since Java 6 / JDBC 4.0.

So if your application has long-running transactions, do call these free() methods, or make it a habit to always call them. We’ll file an issue to FindBugs to make this a potential bug pattern.

jOOQ Newsletter: January 22, 2014

Subscribe to the newsletter here

Tweet of the Day

We are contributing this new section of the newsletter to our followers, users, and customers. Here are:

Jose M. Arranz who has had plans to build jOOQ, when he happily discovered that jOOQ already exists

Majid Azimi who wishes for jOOQ to become the new de facto standard in all languages. (we wish for the same, shocker, I know)

jOOQ 3.3 Preview

We’re closing in on releasing jOOQ 3.3 towards the beginning of February 2014, which is an exciting release for both existing and new jOOQ users. Apart from many defects fixed, there are now also

… and much more. jOOQ Open Source Edition users can download a preview from GitHub, commercial users can request a pre-built download directly from us.

The Data Geekery Business Case at RedHat’s

At Data Geekery, we’re in close touch with various communities, among which those by Oracle or RedHat. RedHat has been selling Open Source software as a business model for a long time. In the enterprise, apart from the flagship RHEL, RedHat is also providing support for a variety of other stacks, such as the JBoss platform, or cloud computing solutions

We’re thrilled to present to you our feature article on RedHat’s platform, where we have published an article on our own commercial Open Source business case:

This is the first part of a series of blog posts. In the next part, we’re going to talk about the five lessons learned when making a business of Open Source, so stay tuned!

Community Work

In the last few weeks, there had been a couple of excellent blog posts by jOOQ community members, which we do not want to keep from you. In December, Gregor Riegler has written a witty rant about the Annotation Nightmare, which we’re increasingly suffering from in the Java ecosystem. Declarative programming at its best.

In January 2014, Petri Kainulainen has started writing a series of excellent blog posts and tutorials for new jOOQ users. The first two posts he has written can be seen here:

Petri is a very active blogger, whom we have been following for a while now. We’re eagerly looking forward to future posts about jOOQ from him.

There are more goodies. Johannes Bühler has contributed new functionality around the loader API and JSON, whereas Darren Shepherd who is a very active contributor to Apache CloudStack has been very active on the jOOQ User Group as well, discovering many issues. Thank you very much for all your help, guys!

Upcoming Events

In January, we have been visiting the JUG-HH in Hamburg for our talk about jOOQ. This was a very exciting and welcoming event with a heavily “JPA / JEE – biased” audience. Thrilling to see how jOOQ (and SQL!) finds high resonance in this kind of industry!

Here is an overview of our upcoming events. We’re very happy to talk at geecon this year!

Stay informed about 2014 events on

SQL Zone – DEFAULT values

We’re always surprised ourselves at the depth and breadth of the SQL language, and we love share our discoveries with you. Few people know that in SQL (in the standard and in many SQL dialects), you can use the DEFAULT keyword in INSERT and UPDATE statements. Read more about DEFAULT VALUES on our blog.

SQL Zone – JDBC Batch

In heavy-throughput environments, SQL users rely on vendor-specific tools, such as Oracle’s SQL*Loader. In slightly less performance-critical environments, JDBC batch operations are still good enough. But how good are they compared to standalone statements? The answer is: very good. But not in all databases / JDBC drivers. We have found a very interesting benchmark by James Sutherland, which we want to share with you.

What you Didn’t Know About JDBC Batch

In our previous blog post “10 Common Mistakes Java Developers Make When Writing SQL“, we have made a point about batching being important when inserting large data sets. In most databases and with most JDBC drivers, you can get a significant performance improvement when running a single prepared statement in batch mode as such:

PreparedStatement s = connection.prepareStatement(
    "INSERT INTO author(id, first_name, last_name)"
  + "  VALUES (?, ?, ?)");

s.setInt(1, 1);
s.setString(2, "Erich");
s.setString(3, "Gamma");

s.setInt(1, 2);
s.setString(2, "Richard");
s.setString(3, "Helm");

s.setInt(1, 3);
s.setString(2, "Ralph");
s.setString(3, "Johnson");

s.setInt(1, 4);
s.setString(2, "John");
s.setString(3, "Vlissides");

int[] result = s.executeBatch();

Or with jOOQ:

        insertInto(AUTHOR, ID, FIRST_NAME, LAST_NAME)
       .values((Integer) null, null, null))
      .bind(1, "Erich", "Gamma")
      .bind(2, "Richard", "Helm")
      .bind(3, "Ralph", "Johnson")
      .bind(4, "John", "Vlissides")

What you probably didn’t know, however, is how dramatic the improvement really is and that JDBC drivers like that of MySQL don’t really support batching, whereas Derby, H2, and HSQLDB don’t really seem to benefit from batching. James Sutherland has assembled this very interesting benchmark on his Java Persistence Performance blog, which can be summarised as such:
Database Performance gain when batched
DB2 503%
Derby 7%
H2 20%
MySQL 5%
MySQL 332% (with rewriteBatchedStatements=true)
Oracle 503%
PostgreSQL 325%
SQL Server 325%
The above table shows the improvement when comparing each database against itself for INSERT, not databases against each other. Regardless of the actual results, it can be said that batching is never worse than not batching for the data set sizes used in the benchmark. See the full article here to see a more detailed interpretation of the above benchmark results, as well as results for UPDATE statements:

Detect JDBC API Misusage with JDBCLint

I’ve recently seen an advertisement for JDBCLint on the H2 User Group. JDBCLint is an Apache licensed JDBC proxy implementation that does some plausibility checks on the lifecycles of your JDBC objects. For instance, it
  • Checks if a ResultSet is closed twice
  • Checks if a ResultSet is not closed at all (in the finalizer)
  • Checks if a ResultSet yields unread columns
All of these checks can be disabled by specifying relevant properties on the proxy. And the best thing is that this proxy is so easy to integrate:

import com.maginatics.jdbclint.ConnectionProxy;
Connection connection =
connection = ConnectionProxy.newInstance(
    connection, new Properties());
// reports error and optionally throws exception

In addition to static code analysis tools like FindBugs or Alvor, this tool can help you find very subtle memory leaks in your large legacy application. Certainly a tool to have on your tool chain!

The Code That Made me Cry

A friend of mine recently told me about the kind of problems he’s currently struggling with in the legacy application he’s maintaining. Here’s a sample piece of code to illustrate what I’m talking about:

String q = "select replace('" +
            accountNo +
           "%','- ','-') from dual";
rs = stmt.executeQuery(q);
if ( {
    accountNoFormatted = rs.getString(1);

It instantly made me cry. As in Code That Made Me Cry, or #CTMMC. If this is just a sample, I can imagine what the rest of the application looks like. As a matter of fact, these problems were the very reason why he thought he needed to sort out a couple of things first, before he could even think about introducing jOOQ or any other new technology in that application. Yes, there’s some serious teaching to be done (or slapping?) If you’ve read through this article thus far without knowing what I’m talking about, then let me give you some advice. Please follow this advice to keep my friend from jumping out the window:

NEVER send such trivial logic to the database for execution!

I’ve recently blogged about various reasons why you should calculate / execute some stuff in the database. A simple string replacement is not one of those things! Heck, why risk the database round trip / network latency, connection and/or data transfer timeouts, and all sorts of other stuff for something that could be written as such in Java?

accountNo.replace("- ", "-");

The method even has the same name as the SQL function. Heck, why even go through the hassle of using the horrible JDBC API for this? Please, dear developer. Take 1h and study the entire list of methods available to java.lang.String. It’s such an awesome and completely underestimated class!

NEVER reformat previously formatted data

This is the rule of thumb: Once data is formatted, it is eternally lost and unavailable to computing / data processing. There is only one simple reason why anyone would ever format any data. It is for displaying data to human beings. Humans are not good at deciphering or memorizing things like
Humans are good at reading and memorizing things like:
My wife's bank account
So repeat after me. Once data is formatted, it is eternally lost and unavailable to computing / data processing. If the formatting was wrong in any way, then fix the formatting where it is wrong. NEVER re-format the previously formatted data. NEVER.

NEVER format data in the data access layer

Just as humans are incredibly bad at operating on long technical IDs, machines are incredibly bad at operating on formatted data. In fact, there are so few reasons to ever format data in the data access layer that it should probably not even occur to you. One acceptable reason is when you have a very very sophisticated, highly tuned report which runs in the DB. But you don’t have that, because you considered using the SQL replace() function to remove a whitespace from a Java string. That isn’t exactly sophisticated reporting. So read after me. NEVER format data in the data access layer, unless you have a compelling technical reason for it. Your accountNo should remain as untouched and technical and ID-style as long as possible throughout your application. There is absolutely no need to format it for human consumption before the accountNo hits the UI. OK, to be fair, there’s another exception to this rule. When you choose to sort data in the UI, then you might want to sort the data by the formatted version of the accountNo, as the sorting result will be consumed by a human:

FROM accounts a
ORDER BY a.account_no_formatted

Be lazy

There is one very simple way to become a better programmer: Be lazy. Be too lazy to write 10 lines of code for a simple replacement of "- " by "-". By being so incredibly lazy, you will always think:
There HAS to be a better way to write this
There’s nothing wrong with not knowing. But there’s everything wrong with using the path of least resistance and writing 10 lines of code for something as trivial as this. Believe me. Your life will be so much better, once trivial stuff can be written in one-liners. You can focus again on your business logic.

Apache Derby About to Adopt the Awesome SQL:2003 MERGE Statement

Apache Derby is one out of three popular Java embeddable databases (apart from H2 and HSQLDB). It is very SQL and JDBC standards-compliant, but maybe a bit behind on developments of more advanced SQL features. Around 6 years after its first submission, there has now been some action on the Apache Derby DERBY-3155 ticket, recently. Rick Hillegas has attached a first, promising draft for the MERGE statement specification, which can be seen here:

Among all 14 of the SQL databases supported by jOOQ, Derby would thus be the 8th to support the SQL:2003 MERGE statement, lining up with:

Other databases support proprietary versions of MERGE:

… or other forms of UPSERT:

Visit DERBY-3155 and show the maintainers some love for implementing this awesome and powerful SQL statement!

The Lame Side of Java’s Backwards-Compatibility

Java is a very backwards-compatible language. Very as in very very very. It is so backwards compatible, we still have tons of deprecated code that was deprecated in the JDK 1.1. For example, most of the java.util.Date and java.util.Calendar API. Some may argue that it would’ve been easier to deprecate the classes altogether…

But things don’t get better as we’re approaching Java 8. Please, observe with me with a mixture of intrigue and disgust what is going to be added to the JDBC 4.2 specs:

“large”. As in “We should’ve made that a long instead of an int from the very beginning”. Luckily, Java 8 also introduces defender methods, such that the additions were done backwards-compatibly.

I wonder how many other places in the JDK should now have duplicate methods using the “large” term, because in the beginning, people chose int over long, when most processors were still 32bit, and it really did make a difference.

Also, I wonder what’ll happen when we run out of 64bit space in the year 2139, as mankind will reach the outer skirts of milky way. In order to be able to write the occasional planet-migration script, we’ll have to add things like executeHugeUpdate() to the JDBC specs in Java 11 – if we’re optimistic that Java 11 will be shipped by then ;-)

For more info, you can see the up-to-date OpenJDK source code here: