In the previous section, we saw how to create self-closing connections with the loan pattern. This allows us to open connections to the database without having to remember to close them. However, we still have to remember to close any ResultSet and PreparedStatement that we open:

// WARNING: Poor Scala code
SqlUtils.usingConnection("test") { connection =>
  val statement = connection.prepareStatement(
    "SELECT * FROM physicists")
  val results = statement.executeQuery
  // do something useful with the results
  results.close
  statement.close
}

Having to open and close the statement is somewhat ugly and error prone. This is another natural use case for the loan pattern. Ideally, we would like to write the following:

usingConnection("test") { connection =>
  connection.withQuery("SELECT * FROM physicists") {
    resultSet => // process results
  }
}

How can we define a .withQuery method on the Connection class? We do not control the Connection class definition as it is part of the JDBC API. We would like to be able to somehow reopen the Connection class definition to add the withQuery method.

Scala does not let us reopen classes to add new methods (a practice known as monkey-patching). We can still, however, enrich existing libraries with implicit conversions using the pimp my library pattern (http://www.artima.com/weblogs/viewpost.jsp?thread=179766). We first define a RichConnection class that contains the withQuery method. This RichConnection class is created from an existing Connection instance.

// RichConnection.scala

import java.sql.{Connection, ResultSet}

class RichConnection(val underlying:Connection) {

  /** Execute a SQL query and process the ResultSet */
  def withQuery[T](query:String)(f:ResultSet => T):T = {
    val statement = underlying.prepareStatement(query)
    val results = statement.executeQuery
    try {
      f(results) // loan the ResultSet to the client
    }
    finally {
      // Ensure all the resources get freed.
      results.close
      statement.close
    }
  }
}

We could use this class by just wrapping every Connection instance in a RichConnection instance:

// Warning: poor Scala code
SqlUtils.usingConnection("test") { connection =>
  val richConnection = new RichConnection(connection)
  richConnection.withQuery("SELECT * FROM physicists") {
    resultSet => // process resultSet
  }
}

This adds unnecessary boilerplate: we have to remember to convert every connection instance to RichConnection to use withQuery. Fortunately, Scala provides an easier way with implicit conversions: we tell Scala how to convert from Connection to RichConnection and vice versa, and tell it to perform this conversion automatically (implicitly), if necessary:

// Implicits.scala
import java.sql.Connection

// Implicit conversion methods are often put in 
// an object called Implicits.
object Implicits {
  implicit def pimpConnection(conn:Connection) = 
    new RichConnection(conn)
  implicit def depimpConnection(conn:RichConnection) =  
    conn.underlying
}

Now, whenever pimpConnection and depimpConnection are in the current scope, Scala will automatically use them to convert from Connection instances to RichConnection and back as needed.

We can now write the following (I have added type information for emphasis):

// Bring the conversion functions into the current scope
import Implicits._ 

SqlUtils.usingConnection("test") { (connection:Connection) =>
  connection.withQuery("SELECT * FROM physicists") {
    // Wow! It's like we have just added 
    // .withQuery to the JDBC Connection class!
    resultSet => // process results
  }
}

This might look like magic, so let's step back and look at what happens when we call withQuery on a Connection instance. The Scala compiler will first look to see if the class definition of Connection defines a withQuery method. When it finds that it does not, it will look for implicit methods that convert a Connection instance to a class that defines withQuery. It will find that the pimpConnection method allows conversion from Connection to RichConnection, which defines withQuery. The Scala compiler automatically uses pimpConnection to transform the Connection instance to RichConnection.

Note that we used the names pimpConnection and depimpConnection for the conversion functions, but they could have been anything. We never call these methods explicitly.

Let's summarize how to use the pimp my library pattern to add methods to an existing class: