Functional JDBC with the Reader monad

James Earl Douglas

October 11, 2017

Monads

In functional programming, a monad is a representation of a computation.

Monads give us useful functions such as map and flatMap:

Reader monad

A Reader monad represents a computation that involves functions that share a common dependency.

JDBC via Reader

Let's abstract a bunch of JDBC code as functions that depend on a common Connection instance.

We'll use a Reader to compose them in different ways.

Let's code

We will work with Java 8's function API and a bit of JDBC.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.function.Consumer;
import java.util.function.Function;

Exception-throwing functions

Lambda expressions in Java 8 can't throw checked exceptions.

We have to write our own exception-throwing function interface.

interface FunctionE<A,B,E extends Exception> {
  B apply(A a) throws E;
}

JDBC functions

A JdbcFunction<A> takes a Connection, returns an A, and can throw an SQLException.

class JdbcFunction<A> implements FunctionE<Connection, A, SQLException> {

  private final FunctionE<Connection, A, SQLException> f;

  JdbcFunction(FunctionE<Connection, A, SQLException> f) {
    this.f = f;
  }

  public A apply(Connection c) throws SQLException {
    return f.apply(c);
  }

JDBC function as a Reader

Lift a pure function into the JDBC function with map.

Bind an effectful function with flatMap.

  <B> JdbcFunction<B> map(Function<A, B> g) {
    return new JdbcFunction<B>(c -> g.apply(f.apply(c)));
  }

  <B> JdbcFunction<B> flatMap(Function<A, JdbcFunction<B>> g) {
    return new JdbcFunction<B>(c -> g.apply(f.apply(c)).apply(c));
  }

forEach: a lossy map

Operate on the return value of a JDBC function without returning anything.

  JdbcFunction<Void> forEach(Consumer<A> g) {
    return new JdbcFunction<Void>(c -> {
      g.accept(f.apply(c));
      return null;
    });
  }

andThen: a lossy flatMap

Run one JDBC function, throw out its result, then run another JDBC function.

  <B> JdbcFunction<B> andThen(JdbcFunction<B> g) {
    return new JdbcFunction<B>(c -> {
      f.apply(c);
      return g.apply(c);
    });
  }

}

In-memory JDBC database

Let's write a Database class that abstracts an in-memory H2 database.

class Database {

  private final Connection c;

  Database() throws Exception {
    Class.forName("org.h2.Driver");
    this.c = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
  }

Transactional application

Our Database class defines how to run a JdbcFunction<A> within a transaction.

  <A> A apply(JdbcFunction<A> f) throws SQLException {
    c.setAutoCommit(false);
    try {
      A a = f.apply(c);
      c.commit();
      return a;
    } catch (SQLException e) {
      c.rollback();
      throw e;
    } finally {
      c.setAutoCommit(true);
    }
  }

}

Database schema

We'll use a single table to track hits on URLs.

.-------------------.
| HITS              |
|-------------------|
| URL: VARCHAR(256) |
| HITS: INT         |
'-------------------'

JDBC function: initDb

public class FunctionalJDBC {

  static JdbcFunction<Void> initDb() {
    return new JdbcFunction<Void>(c -> {
      Statement s = c.createStatement();
      s.executeUpdate("CREATE TABLE HITS(URL VARCHAR(256), HITS INT)");
      s.close();
      return null;
    });
  }

JDBC function: getHits

  static JdbcFunction<Integer> getHits(String url) {
    return new JdbcFunction<Integer>(c -> {
      String q = "SELECT HITS FROM HITS WHERE URL = ?";
      PreparedStatement s = c.prepareStatement(q);
      s.setString(1, url);
      ResultSet rs = s.executeQuery();
      int hits = 0;
      if (rs.next()) {
        hits = rs.getInt("HITS");
      }
      s.close();
      return hits;
    });
  }

JDBC function: deleteHits

  static JdbcFunction<Void> deleteHits(String url) {
    return new JdbcFunction<Void>(c -> {
      String q = "DELETE FROM HITS WHERE URL = ?";
      PreparedStatement s = c.prepareStatement(q);
      s.setString(1, url);
      s.executeUpdate();
      s.close();
      return null;
    });
  }

JDBC function: insertHits

  static JdbcFunction<Void> insertHits(String url, int hits) {
    return new JdbcFunction<Void>(c -> {
      String q = "INSERT INTO HITS (URL, HITS) VALUES (?, ?)";
      PreparedStatement s = c.prepareStatement(q);
      s.setString(1, url);
      s.setInt(2, hits);
      s.executeUpdate();
      s.close();
      return null;
    });
  }

JDBC function: incrementHits

  static JdbcFunction<Void> incrementHits(String url) {
    return getHits(url).flatMap(hits ->
             deleteHits(url).andThen(
               insertHits(url, hits + 1)
             )
           );
  }

Usage: initialization

  public static void main(String[] args) throws Exception {

    Database db = new Database();

    db.apply(
      initDb() // JdbcFunction<Void>
    );

Usage: insertion and selection

    db.apply(
      incrementHits("/foo")                // JdbcFunction<Void>
        .andThen(incrementHits("/foo"))    // JdbcFunction<Void>
        .andThen(getHits("/foo"))          // JdbcFunction<Integer>
        .map(hits -> "/foo hits: " + hits) // JdbcFunction<String>
        .forEach(System.out::println)      // JdbcFunction<Void>
    );

Usage: selection

    db.apply(
      getHits("/bar")
        .map(hits -> "/bar hits: " + hits) // JdbcFunction<String>
        .forEach(System.out::println)      // JdbcFunction<Void>
    );

    db.apply(
      getHits("/foo")
        .map(hits -> "/foo hits: " + hits) // JdbcFunction<String>
        .forEach(System.out::println)      // JdbcFunction<Void>
    );

  }

}

Demo

These slides are literate Java. Grab a recent H2 .jar file and try it out:

$ curl -s https://earldouglas.com/talks/functional-jdbc/slides.md | codedown java > FunctionalJDBC.java
$ wget https://repo1.maven.org/maven2/com/h2database/h2/1.4.196/h2-1.4.196.jar
$ javac -cp h2-1.4.196.jar FunctionalJDBC.java
$ java -cp h2-1.4.196.jar:. FunctionalJDBC
/foo hits: 2
/bar hits: 0
/foo hits: 2

Further reading