Simple JDBC Queries in Scala

The Beginning Scala book has a great example of using partially applied functions to automatically close JDBC connections. Today I needed to use some complex SQL outside of our ORM and extended this code sample to make it incredibly simple & safe.

The using and bmap methods are from the book; the query and queryEach methods are my creations:

object Control {
  def using[Closeable <: {def close(): Unit}, B&#93;(closeable: Closeable)(getB: Closeable => B): B = 
    try {
      getB(closeable)
    } finally {
      closeable.close()
    }
  
  import scala.collection.mutable.ListBuffer
  
  def bmap[T](test: => Boolean)(block: => T): List[T] = {
    val ret = new ListBuffer[T]
    while(test) ret += block
    ret.toList
  }
  
  import java.sql._
  
  /** Executes the SQL and processes the result set using the specified function. */
  def query[B](connection: Connection, sql: String)(process: ResultSet => B): B =
    using (connection) { connection => 
      using (connection.createStatement) { statement => 
        using (statement.executeQuery(sql)) { results => 
          process(results)
        }
      }
    }

  /** Executes the SQL and uses the process function to convert each row into a T. */
  def queryEach[T](connection: Connection, sql: String)(process: ResultSet => T): List[T] =
    query(connection, sql) { results => 
      bmap(results.next) { 
        process(results)
      }
    }
}

The using method just ensures that something with a close() method gets closed after it’s used, while bmap collects the results of some function into a list. I found the using method resulted in a fair amount of boilerplate code, so I factored that out into the query method. You just give it a Connection object and an SQL string and it will handle creating the Statement and ResultSet, passing the ResultSet to your custom processing function, and then closing everything safely for you.

The queryEach method also eliminates some common boilerplate. It builds on the query method by using each row in the ResultSet to create some domain object and collecting all of those objects into a list. As the example usage below shows, you just give queryEach your connection and SQL, as well as some simple code to process a single row in the ResultSet:

import com.whatever.Control._
import java.sql._

val conn: Connection = ...
val people = queryEach(conn, "SELECT * FROM person") {rs => 
  new Person(rs.getString("name"), rs.getInt("age"), rs.getBoolean("valid"))
}

I think this results in some very compact and intuitive code. And reusing all of the boilerplate in the Control methods ensures that you don’t forget to close a Statement or ResultSet.