Safer JDBC connections with the loan pattern

We have already seen how to connect to a JDBC database and send statements to the database for execution. This technique, however, is somewhat error prone: you have to remember to close statements; otherwise, you will quickly run out of memory. In more traditional imperative style, we write the following try-finally block around every connection:

// WARNING: poor Scala code
val connection = DriverManager.getConnection(url, user, password)
try {
  // do something with connection
}
finally {
  connection.close()
}

Scala, with first-class functions, provides us with an alternative: the loan pattern. We write a function that is responsible for opening the connection, loaning it to the client code to do something interesting with it, and then closing it when the client code is done. Thus, the client code is not responsible for closing the connection any more.

Let's create a new SqlUtils object with a usingConnection method that leverages the loan pattern:

// SqlUtils.scala

import java.sql._

object SqlUtils {

  /** Create an auto-closing connection using 
    * the loan pattern */
  def usingConnection[T](
    db:String,
    host:String="127.0.0.1",
    user:String="root",
    password:String="",
    port:Int=3306
  )(f:Connection => T):T = {
    
    // Create the connection
    val Url = s"jdbc:mysql://$host:$port/$db"
    Class.forName("com.mysql.jdbc.Driver")
    val connection = DriverManager.getConnection(
      Url, user, password)

    // give the connection to the client, through the callable 
    // `f` passed in as argument
    try {
      f(connection)
    }
    finally {
      // When client is done, close the connection
      connection.close()
    }
  }
}

Let's see this function in action:

scala> SqlUtils.usingConnection("test") {
 connection => println(connection)
}
com.mysql.jdbc.JDBC4Connection@46fd3d66

Thus, the client doesn't have to remember to close the connection, and the resultant code (for the client) feels much more like Scala.

How does our usingConnection function work? The function definition is def usingConnection( ... )(f : Connection => T ):T. It takes, as its second set of arguments, a function that acts on a Connection object. The body of usingConnection creates the connection, then passes it to f, and finally closes the connection. This syntax is somewhat similar to code blocks in Ruby or the with statement in Python.

Tip

Be careful when mixing the loan pattern with lazy operations. This applies particularly to returning iterators, streams, and futures from f. As soon as the thread of execution leaves f, the connection will be closed. Any data structure that is not materialized at this point will not be able to carry on accessing the connection.

The loan pattern is, of course, not exclusive to database connections. It is useful whenever you have the following pattern, in pseudocode:

open resource (eg. database connection, file ...)
use resource somehow // loan resource to client for this part.
close resource