My [cref why-i-love-closures post about closures] last week generated quite a bit of traffic and comments, both positive and negative. I decided to followup on that post with a few examples of how to add a method that I believe is missing from Groovy’s Sql class that will execute a closure, and will guarantee that the connection gets closed, no matter the outcome of the closure’s contents.

I’m going to discuss three ways to add a method that does what we want:

1. Wrap the existing class
2. Modify the MetaClass of the existing class
3. Use a Category

All three of these are extremely simple. I think it mostly comes down to preference as to which one you might want to use. Before we get started, let me say that Groovy is optionally-typed. What this means is that you don’t have to declare variable types if you don’t want to. I like not having to declare variable types, so I have not done so in any of these example. You might hate that, and think it sloppy/heretical/evil. If so, and you choose to use any of the code I present, feel free to declare your variable types. So, with that out of the way, let’s start with the wrapping approach.

Wrap the existing class

First, we’ll create a class called ISql that wraps an instance of Sql.

import groovy.sql.Sql

public class ISql
{
   public static newInstance(url, user, pass, driver, closure)
   {
     def con

     try
     {
       con = Sql.newInstance(url, user, pass, driver)

       if (closure)
       {
         closure.call(con)
       }
     }
     finally
     {
       con.close()
     }
   }
}

You can see that we’ve got a class with a single static method called newInstance, to mimic the standard way of creating a Sql instance. It takes the same four arguments that the Sql class does, but it takes one extra argument: a Closure. All this method does is create an instance of Sql, executes the closure, passing in the Sql instance, and then ensures that the connection gets closed, through the call to con.close() in the finally block. To use this class, you can do this

ISql.newInstance(url, user, pass, driver) {con ->
  con.eachRow("select * from Foo") {row ->
    println "ID: $row.id"
  }
}

In this test, we call our special newInstance method, passing in the connection parameters, and then tacking on a closure at the end. Inside the closure, we get access to the connection through the con variable, and then we can do anything we would normally do with a Sql connection. In this case, we execute a query and print the value of each row’s Id column. Nothing too exciting, but it works. No matter what happens inside those closures, the connection is guaranteed to be closed at the end. 

Modify the MetaClass of the existing class

The second way to do this is to add a method to Groovy’s built-in Sql class by modifying its MetaClass. Here’s the code to do that:

Sql.metaClass.static.newInstance << {url, user, pass, driver, closure ->
  def con

  try
  {
    con = Sql.newInstance(url, user, pass, driver)

    if (closure)
    {
      closure.call(con)
    }
  }
  finally
  {
    con.close()
  }
}

Everything after line three is exactly like what we did in the wrapping approach. The magic occurs in line one. In that line, we get the Sql class’ MetaClass, and then grab the static property of the MetaClass. We then add another method called newInstance by using the << operator to append a closure taking the right number of arguments. To call it, we have code that looks almost identical to our last example, but instead of using the ISql class, we’re using Groovy’s built-in Sql class with our special method included.

Sql.newInstance(url, user, pass, driver) {con ->
  con.eachRow("select * from Foo") {row ->
    println "ID: $row.id"
  }
}

You can see that with the exception of the missing ‘I’, the code is exactly the same.

Use a Category

Groovy also provides something called Categories that allow you to add methods to existing classes, but they are only usable while the Category is in use. It’s somewhat confusing, and is my least favorite approach, but here’s how it works. You create a class with a static method taking the arguments you want to pass, plus an extra argument, usually called “self”, that will get passed the thing on which you’re calling this method. This special required argument must be the first in the list. Here’s our category called SqlHelper

import groovy.sql.Sql

public class SqlHelper
{
  def static newInstance(self, url, user, pass, driver, closure)
  {
    def con

    try
    {
      con = Sql.newInstance(url, user, pass, driver)

      if (closure)
      {
        closure.call(con)
      }
    }
    finally
    {
      con.close()
    }     
  }
}

Notice that the first parameter to newInstance is that special “self” variable. If you don’t include that argument in the method declaration, calling the method won’t work. I should add that you don’t actually pass any argument for “self” yourself when calling the method. This is handled by Groovy, in much the same way Python programs stuff values into a method’s “self” argument. So, to use the category, you have to wrap your operation in a “use” block

use(SqlHelper)
{
  Sql.newInstance(url, user, pass, driver) {con ->
    con.eachRow("select * from Foo") {row ->
      println "ID: $row.id"
    }
  }
}

Here, we declare that we want to use SqlHelper by using a “use” block that contains the category in parentheses. Within the curly brackets of the use (also a closure), Groovy will see our call to newInstance on the Sql class, and will figure out that we want to use the one in the category. It will then call that method, passing the Sql class as the self parameter, and all our other arguments as you would expect. With the exception of having to use the “use” block, this code looks just like the second example.

When Something Goes Wrong

I said that in each case, no matter what happens in the closure, the connection was guaranteed to get closed. Someone commented on the last post that he was concerned that the use of closures would somehow obscure where the problem occurred. It doesn’t. You still get the line number of where things went pear-shaped. For example,

try
{
  ISql.newInstance(url, user, pass, driver) {con ->
    con.eachRow("select * from boingo") {row ->
      println "ID: $row.id"
    }
  }
}
catch (Exception e)
{
  e.printStackTrace()
}

In this case, there is no table called “boingo,” and so when I execute this code I get an exception thrown, and from the stack trace, I can see where the problem occurred:

java.sql.SQLException: Invalid object name 'boingo'.
  ...
  at groovy.sql.Sql.eachRow(Sql.java:559)
  at groovy.sql.Sql.eachRow(Sql.java:541)
  ...
  at ISqlTest$_testBadness_closure2.doCall(ISqlTest.groovy:38)

I cut some of the stack dump out for brevity, but you can see that it was a java.sql.SQLException that was thrown, and it references line 38 in the code. That just as much information as you’d get from straight Java in this case, so you should be able to diagnose the problem.

Other Approaches

I should add that you should be able to subclass the Sql class, adding a static method called newInstance that accepts a closure in addition to the four connection arguments. I tried that, but it didn’t work. Actually, it partially worked. The newInstance method I added worked like a champ, but the original newInstance method was no longer visible. I don’t know why, but that’s the behavior I was seeing. It might just be that I’m not familiar enough with Groovy, but I couldn’t get it to work. If anyone knows why, let me know.

To Sum Up

So, those are three ways to add a closure-with-guaranteed-connection-closing method to Groovy. Which of these approaches should you use? Personally, I prefer the second way, adding a method to Sql through its metaclass, but it really comes down to preference. Whichever way you choose should be documented so your teammates understand what’s going on.

I hope that someone on the Groovy team will realize that they left this functionality out of the previous versions and decide to add it for a future version. It really strikes me as odd that this is not in there already, since it seems to fit so well with the language. The fact that various methods inside the Sql class take closures, but not the construction method, makes me think it was just an oversight.