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

Sometimes a tattletale is enough

You ever see one of the old spy movies where the agent puts a tattletale, like a hair, across a door frame to know if the room is entered while they are out?

Recently I had to write a tactical piece of code that was to run for about a month. It was designed to spin off a separate thread and periodically create a heartbeat message to keep a device alive until the next version of the device no longer required that heartbeat. It was a couple of pages of code, written to be rolled out ASAP. It was kept as simple as possible to avoid the risks introduced by complexity. That said, it wasn’t done in a haphazard way. The development was test driven and there was thorough test coverage. Testing the heartbeat code was being invoked off in it’s own thread did prove a challenge with the complexity adverse implementation.

Had the code been a full on strategic solution with a normal life span I know exactly what approach I’d have taken. The separate thread would have depended on a delegate to send the heartbeats and in testing I’d set the delegate to a Spy instance that I could work with in the tests. The introduction of delegation and dependency injection, was just too much of an over design, particularly when driven solely by testing needs.

So, instead of a full on spy, I went with just a little tattletale. I passed an optional tattletale reference into the thread calling the heartbeat. If the tattletale was present, the heartbeat code changed it’s value as a signal (it was an Optional<Semaphore> to be exact). In testing the tattletale was passed in and monitored for change. Under normal operation no tattletale was passed in. Really it was still the spy pattern, just done in a very low tech way, with minimal design impact.