Yet Another Fluent JDBC Wrapper in 200 Lines of Code

Introduction

Those who are not convinced to use Hibernate to manage persistence are forced to use plain old JDBC API. Though powerful, it requires a lot of typing to get it right. For example, retrieving user data from a database often requires such code snippet:

final List<User> result = new ArrayList<>();
final PreparedStatement s = 
        conn.prepareStatement("select name, password from user where name = ?");
try{
   s.setString(1, "adam");
   final ResultSet rs = s.executeQuery();
   try {
      result.add(new User(rs.getString(1), rs.getString(2)));
   } finally {
      rs.close();
   }
} finally {
   s.close();
}

With Java 7’s try-with-resources, this code is a little shorter:

final List<User> list = new ArrayList<>();
try (final PreparedStatement s = c.prepareStatement
("select name, password from users where name = ?")) {
    s.setString(1, "adam");
    try (final ResultSet rs = s.executeQuery()) {
        list.add(new User(rs.getString(1), rs.getString(2)));
    }
}

Even though it is progress, it is not what most would describe as "elegant". With Java 8’s lambda expressions and fluent API pattern, it can be minimized to so-called "on-liner" that looks something like this:

final List<User> list2 = using(c).prepare("select name, password from users where name = ?").
              set("Adam").andMap(rs -> new User(rs.getString(1), rs.getString(2)));

Goal

There are numerous approaches to this (jcabi-jdbc, fluent-jdbc, fluent-jdbc, JdbcTemplate, …), but all of them seem a little "big". The goal of this version was a simple one.

Create a fluent JDBC wrapper, that streamlines 80% of JDBC use cases, in just a single source file.

The result is 200 lines of code (after stripping comments and blank lines) that allow you to express the most common JDBC use case in a compact way.

Usage

The wrapper uses static imports (in the form of import static fluentJDBC.FluentConnection.*;), fluent method chaining, with implicit parameter index incrementation and functional interfaces which allow lambda expression as parameters to andMap, andReduse and forEach methods. The examples of FluentConnection usage are listed below:

Connection c = null; // we surely should have initializeded this :)

//FluentJDBC - single value retrieval
Optional<User> user = using(c).prepare("select name, password from users where name = ?").
	set("Adam").andMapOne(rs -> new User(rs.getString(1), rs.getString(2)));

//FluentJDBC - result printing
using(c).prepare("select name, password from users where name = ?").
	set("Adam").andForEach(rs -> System.out.println(rs.getString(1)));

// FluentJDBC - aggregation in application - inefficient example
String str = using(c).prepare("select name, password from users where name = ?").
	set("Adam").andReduce("", (acc, rs) -> acc + "name: " + rs.getString(1));

//FluentJDBC - insertion (works for modification and deletion as well)
using(c).prepare("insert into users values (?,?,?)").set(1).set("Adam").
	set("password").andUpdate();

//FluentJDBC - insertion returning autogenerated primary key
Integer id = (Integer) using(c).
	prepareWithKey("insert into users(name, password) values (?,?)").
	set("Adam").set("secret").andUpdateReturningKey();

//FluentJDBC - custom parameter setting code
using(c).prepare("insert into users values (?,?,?)").set(1).set("Adam").
	set((s, index) -> s.setString(index, "secret")).andUpdate();

//FluentJDBC - custom PreparedStatement modification
using(c).prepare("insert into users values (?,?,?)").set(1).
	set("Adam").set("password").
	apply((s) -> s.setCursorName("c1")).andUpdate();

This project is a BSD licensed, single file (FluentConnection.java), lightweight wrapper that you can include in your project and use. It is by no means a complete JDBC wrapper, but I encourage you to extend it to meet your own needs.

NOTE: You might be interested in another concept of mine related to relational databases: Relational Database Schema Versioning in 55 Lines of Code.