Java 8 Friday Goodies: Lambdas and SQL

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. 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' )
sql.eachRow( 
    '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;
    }

    @Override
    public String toString() {
        return "Schema{" +
               "schemaName='" + schemaName + '\'' +
               ", isDefault=" + isDefault +
               '}';
    }
}
Our main method will get an H2 connection through DriverManager: 

Class.forName("org.h2.Driver");
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: 

DSL.using(c)
   .fetch(sql)
   .map(r -> new Schema(
       r.getValue("SCHEMA_NAME", String.class),
       r.getValue("IS_DEFAULT", boolean.class)
   ))
   .forEach(System.out::println);
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: 

DSL.using(c)
   .fetch(sql)
   .into(Schema.class)
   .forEach(System.out::println);
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(
            rs.getString("SCHEMA_NAME"),
            rs.getBoolean("IS_DEFAULT")
        ))
    .forEach(System.out::println);
… and if you’re using Apache DbUtils, you can do almost the same: 

new QueryRunner()
    .query(c, sql, new ArrayListHandler())
    .stream()
    .map(array -> new Schema(
        (String) array[0],
        (Boolean) array[1]
    ))
    .forEach(System.out::println);

Conclusion

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

Published by lukaseder

I made jOOQ

Leave a Reply