About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

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.

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.

Next week, we’re going to see a couple of things that will greatly improve when using the java.util.Map API.

 

Reference: Java 8 Friday Goodies: Lambdas and SQL from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog.
Related Whitepaper:

Functional Programming in Java: Harnessing the Power of Java 8 Lambda Expressions

Get ready to program in a whole new way!

Functional Programming in Java will help you quickly get on top of the new, essential Java 8 language features and the functional style that will change and improve your code. This short, targeted book will help you make the paradigm shift from the old imperative way to a less error-prone, more elegant, and concise coding style that’s also a breeze to parallelize. You'll explore the syntax and semantics of lambda expressions, method and constructor references, and functional interfaces. You'll design and write applications better using the new standards in Java 8 and the JDK.

Get it Now!  

Leave a Reply


6 + four =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books