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, jdk8

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

Links

Thank You!

Martin Snyder / @MartinSnyder / Wingspan Technology, Inc.