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:
- jOOQ. (Shocker, I know)
- Spring Data / JDBC
- Apache Commons DbUtils
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.
