A Little Java 8 Goodness In JDBC

Preface

Early on, in the ’80s, I was deep into Objective-C and read Brad Cox’s book Object-Oriented Programming: An Evolutionary Approach. He spoke about encapsulation in terms of keeping “Object Surface” small, clean and understandable. That concept has never left me, so while I often utilize others code and frameworks, I’ve never been one to pull in a complex tractor trailer of code just to get the one tire I need.

So, while I am aware there are a lot of rich frameworks out there adding Java 8’s functional features to database access, this post is about some very simple code to just add a little lambda and stream goodness to JDBC.

Goals

For me, when I want to add lightweight data persistence to a Java project there pain points center around the boilerplate code you end up doing to manage resources (Connections, Statements, ResultSets…) and the tuple extraction code. Java 7’s try with resources helped a great deal on the resource management. I wanted to see it Java 8’s lambdas, default methods, and streams could also help cut down code.

The code below is available in complete form at github

Lambdas

From some other database access packages, I’d come to see that creating light weight functors to extract a tuple from a ResultSet was a great way to reduce code. This seemed like a match for lambdas.

Consider the following:

@FunctionalInterface
public interface Extractor<T> {
    T extract(ResultSet rs) throws SQLException;
}

Okay, not exciting, but lets build on it. Here’s a generic query that uses an Extractor:

public static <T> List<T> query(Connection c,
                       String sql,
                       Extractor<T> extractor)
                       throws SQLException {
      List<T> list = new ArrayList<T>();
      try (Statement s = c.createStatement();
           ResultSet rs = s.executeQuery(sql)) {
        while (rs.next()) {
          list.add(extractor.extract(rs));
        }
      }
      return list;
    }

And then making a query is as easy as:

List<String> list = query(c,
               "SELECT title FROM books",
                rs -> rs.getString("title"));

That is pretty clean no?

Default Methods

Again, from using other tools, I was used to the idea of a base class, maybe an abstract, that was inherited from when you wanted a class to be persistent. So I looked into instead using an interface with default methods for greater flexibility.

So lets take the above static query method, and make it into a default method.

public interface DbAccessor {
  Connection getConnection() throws SQLException;

  default <T> List<T> query(String sql,
                       Extractor<T> extractor)
                       throws SQLException {
      List<T> list = new ArrayList<T>();
      try (Connection c = getConnection();
           Statement s = c.createStatement();
           ResultSet rs = s.executeQuery(sql)) {
        while (rs.next()) {
          list.add(extractor.extract(rs));
        }
      }
      return list;
    }
}

Now, any class can implement DbAccessor and provide a getConnection() and have query available.

Streams

Traversing ResultSets, and working with the resultant tuples, ends up being the meat of a lot of code for things like report generation or metrics gathering. In particular, if you’re performing some sort of aggregation operation beyond what SQL’s GROUP BY offers, you’ll need to traverse every record in your Java. Now JDBC’s ResultSet already uses a cursor to move through the data, so introducing the List is a very memory inefficient way to traverse it. So let’s get the query method to return a Stream instead and that way we’ve only a single record in memory at one time.

The most straight forward way I found to create a Stream from a ResultSet was to create a ResultSetIterator and then use Spliterators to turn the Iterator into a Stream. Here’s a simplified example:

public class ResultSetIterator<T>
            implements Iterator<T>, AutoCloseable {
    private final ResultSet resultSet;
    private final Extractor<T> extractor;

    public ResultSetIterator(final ResultSet resultSet, final Extractor<T> extractor)
        this.extractor = extractor;
        this.resultSet = resultSet;
    }

    @Override
    public boolean hasNext() {
        try {
            return resultSet.next();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public T next() {
        try {
            return extractor.extract(resultSet);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void close() throws SQLException {
        resultSet.close();
    }
}

Now that we have a ResultSetIterator creating a Stream is easy:

 static <T> Stream<T> stream(
                       final ResultSet resultSet,
                       final Extractor extractor) {
        ResultSetIterator iterator = new ResultSetIterator(resultSet, extractor);
        return StreamSupport.stream(Spliterators.spliteratorUnknownSize(iterator, 0), false)
                .onClose(() -> {
                    try {
                        iterator.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
    }

Using that we can change the query method to read:

default <T> Stream<T> query(
             String sql,
             Extractor<T> extractor)
                  throws SQLException {
       Connection c = getConnection();
       Statement s = c.createStatement();
       ResultSet rs = s.executeQuery(sql);
       return ResultSetIterator.stream(rs,extractor).onClose(() -> {
           try {
               s.close();
               c.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       });
    }

With that in place you can do things like:

    query("SELECT title FROM books",
       rs -> rs.getString("title"))
        .limit(10)
          .forEach(s -> System.out.println(s));

Summary

With a bare minimum of code you can get a pretty useful bridge from Java 8’s functional features to JDBC. As mentioned, I maintain a Kata project for these ideas, that I keep at github.

Footnote on Dog Fooding

When I applied my own JDK Contracts tests to this code the Iterator failed. It worked fine in the Spliterator but it wasn’t to contract. I’ve updated the implementation now.

Follow Up

I’ve written a follow up to piece  Java 8 Functional JDBC Revisited that speaks to taking a functional approach to your mapper objects.

Advertisements

2 thoughts on “A Little Java 8 Goodness In JDBC

  1. Pingback: Functional JDBC Part 2 | My name is Ozymandias

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s