Functional Wrappers for legacy APIs

Martin Snyder / @MartinSnyder / Wingspan Technology, Inc.

Goals for technology introduction

  • Lower adoption cost
  • Showcase the strengths of the technology
  • Give an immediate, concrete benefit

Talking points for Functional Programming

  • Immutable data, elimination of side effects
  • Higher order functions
  • Robust collection APIs
  • What to do, not how to do it
  • Lower risk profile

Scala wrapper for JDBC

This example was selected for the following reasons:

  • JDBC is a highly accessible technology
  • Scala is a JVM language with great Java interopability
  • The combination lends itself to self-contained examples
  • NOTE: the value is in the process, not the product. This is representative of more interesting problems

Example Scenario

Convert relational data ...

CREATE TABLE EXAMPLE(ID INT PRIMARY KEY, DESCRIPTION VARCHAR);
INSERT INTO EXAMPLE(ID, DESCRIPTION) VALUES(0, 'Zero');
INSERT INTO EXAMPLE(ID, DESCRIPTION) VALUES(1, 'One');
INSERT INTO EXAMPLE(ID, DESCRIPTION) VALUES(2, 'Two');
INSERT INTO EXAMPLE(ID, DESCRIPTION) VALUES(3, 'Three');
INSERT INTO EXAMPLE(ID, DESCRIPTION) VALUES(4, 'Four');

... to JSON

[
    {"DESCRIPTION":"Zero","ID":0},
    {"DESCRIPTION":"One","ID":1},
    {"DESCRIPTION":"Two","ID":2},
    {"DESCRIPTION":"Three","ID":3},
    {"DESCRIPTION":"Four","ID":4}
]

Target Source Code

val connectionInfo = new Jdbc.ConnectionInfo("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1")

val mapper = new ObjectMapper().registerModule(DefaultScalaModule)

def queryToJSON(conn: Jdbc.ConnectionInfo, sql: String) =
  Jdbc.withResultsIterator(conn, sql, it => mapper.writeValueAsString(it))

def main(args: Array[String]) {
  queryToJSON(connectionInfo, "SELECT * FROM EXAMPLE") match {
    case Success(json) => println(json)
    case Failure(e) => println(e.getMessage)
  }
}

Target with Partial result sets

def queryToJSONPartial(conn: Jdbc.ConnectionInfo, sql: String, start: Int, rows: Int) =
  Jdbc.withResultsIterator(
    conn,
    sql,
    it => mapper.writeValueAsString(it.drop(start).take(rows))
  )

queryToJSONPartial(connectionInfo, "SELECT * FROM EXAMPLE", 2, 2)

Produces

[
    {"DESCRIPTION":"Two","ID":2},
    {"DESCRIPTION":"Three","ID":3},
]

The Approach

  • Hide state from our callers
  • Surface within the collections API

The alternative

Connection conn =  DriverManager.getConnection("url", "user", "pass");
try {
  Statement stmt = conn.createStatement();
  try {
    ResultSet rs = stmt.executeQuery("SELECT * FROM EX");
    try {
      while (rs.next()) { ... }
    } finally {
      rs.close();
    }
  } finally {
    stmt.close();
  }
} finally {
  conn.close();
}

Step 1 - Hiding Connection State

  • Connection, Statement, and ResultSet all have an internal state that must be managed
  • Requires developer knowledge for correct use
  • Potential source of programmer errors

Create Data Payload

case class ConnectionInfo(url: String, username: String = "", password: String = "")

Introduce Higher Order Function

def withConnection (connInfo: ConnectionInfo, f: Connection => Unit) {
    ...
}

Control State Management

def withConnection (connInfo: ConnectionInfo, f: Connection => Unit) {
    val conn = DriverManager.getConnection(connInfo.url,
                                           connInfo.username,
                                           connInfo.password)
    try {
      f(conn)
    }
    finally {
      conn.close()
    }
}

Use Generic Return Types

def withConnection [T] (connInfo: ConnectionInfo, f: Connection => T): T = {
    val conn = DriverManager.getConnection(connInfo.url,
                                           connInfo.username,
                                           connInfo.password)
    try {
      f(conn)
    }
    finally {
      conn.close()
    }
}

Apply the Try Monad

def withConnection [T] (connInfo: ConnectionInfo, f: Connection => T): Try[T] = {
    val conn = DriverManager.getConnection(connInfo.url,
                                           connInfo.username,
                                           connInfo.password)
    val result = Try(f(conn))
    conn.close()
    result
}

Step 1a - Hide more state

Now we finish the job for Statement and ResultSet

Statement Management

def withStatement [T] (connInfo: ConnectionInfo, f: Statement => T): Try[T] = {
    def privFun(conn: Connection) = {
        val stmt = conn.createStatement()
        try {
            f(stmt)
        }
        finally {
            stmt.close()
        }
    }

    withConnection(connInfo, privFun)
}

ResultSet Management

def withResultSet [T] (connInfo: ConnectionInfo, sql: String, f: ResultSet => T):Try[T]={
    def privFun(stmt: Statement) = {
        val resultSet = stmt.executeQuery(sql)
        try {
            f(resultSet)
        }
        finally {
            resultSet.close()
        }
    }

    withStatement(connInfo, privFun)
}

Progress check

Our risk profile is about the same as before because of the complexity of the ResultSet interface which combines:

  • State regarding the result set
  • Iteration
  • Data structure of the results
  • Read access to the “current” row
  • Write access to the “current” row

Step 2 - Plug into the collections API

There are three appropriate "iterable" collection objects in Scala:

  • List
  • Iterator
  • Stream

A Naive Iterator

class ResultsIterator(resultSet: ResultSet) extends Iterator[ResultSet] {
    def hasNext = resultSet.next()
    def next() = resultSet
}

Extract our column names

private val columnNames: Seq[String] = {
  val rsmd: ResultSetMetaData = resultSet.getMetaData

  for (i <- 1 to rsmd.getColumnCount) yield rsmd.getColumnName(i)
}

Extract One Row

private def buildRowMap(resultSet: ResultSet): Map[String, AnyRef] = {
  (
    for (c <- columnNames) yield c -> resultSet.getObject(c)
  ).toMap
}

An Improved Iterator

class ResultsIterator (resultSet: ResultSet) extends Iterator[Map[String, AnyRef]] {
    val columnNames = { … }
    private def buildRowMap(resultSet: ResultSet) = { ... }

    def hasNext = resultSet.next()
    def next() = buildRowMap(resultSet)
}

Controlling Row Retrieval

private def getNextRow(resultSet: ResultSet): Option[Map[String, AnyRef]] = {
    if (resultSet.next())
        Some(buildRowMap(resultSet))
    else
        None
}

The Final Iterator

class ResultsIterator (resultSet: ResultSet) extends Iterator[Map[String, AnyRef]]
{
    val columnNames = { … }
    private def buildRowMap(resultSet: ResultSet) = { … }
    private def getNextRow(resultSet: ResultSet) = { … }

    var nextRow = getNextRow(resultSet)

    def hasNext = nextRow.isDefined

    def next() = {
        val rowData = nextRow.get
        nextRow = getNextRow(resultSet)
        rowData
    }
}

Provide our iterator to our callers

def withResultsIterator [T] (connInfo: ConnectionInfo,
                             sql: String,
                             itFun: ResultsIterator => T): Try[T] =
    withResultSet(connInfo, sql, resultSet => itFun(new ResultsIterator(resultSet)))

Revisiting the destination

val connectionInfo = new Jdbc.ConnectionInfo("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1")

val mapper = new ObjectMapper()registerModule(DefaultScalaModule)

def queryToJSON(conn: Jdbc.ConnectionInfo, sql: String) =
    Jdbc.withResultsIterator(conn, sql, it => mapper.writeValueAsString(it))

def main(args: Array[String]) {
    queryToJSON(connectionInfo, "SELECT * FROM EXAMPLE") match {
        case Success(json) => println(json)
        case Failure(e) => println(e.getMessage)
    }
}

Source Project

https://github.com/MartinSnyder/scala-jdbc

Requires git, jdk7

git clone https://github.com/MartinSnyder/scala-jdbc.git
gradlew test run

Links

Thank You!

Martin Snyder / @MartinSnyder / Wingspan Technology, Inc.