Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Java 8 Friday Goodies: Lambdas and SQL

DZone's Guide to

Java 8 Friday Goodies: Lambdas and SQL

· Java Zone
Free Resource

Are you joining the containers revolution? Start leveraging container management using Platform9's ultimate guide to Kubernetes deployment.

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

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

Using Containers? Read our Kubernetes Comparison eBook to learn the positives and negatives of Kubernetes, Mesos, Docker Swarm and EC2 Container Services.

Topics:

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}