If a method arg is a closure, for crying out loud pass it a closure

This is a (mildly) embarrassing post, because it demonstrates how in my transition to functional programming I missed something important. I get it now, though, and I might as well help others avoid the same mistake I made.

I teach a lot of Grails training classes, and one question that always comes up is how to map to existing databases rather than generate the schema from the Grails application. Hibernate (and therefore Grails) would love to generate the database schema for you, for a variety of reasons, but mostly because going the other way isn’t unique. You have to make a lot of decisions about a domain model when you reverse-engineer an existing schema, ranging from multiplicities in relationships to how to map many-to-manys to directionality and more.

In my standard Grails course, rather than create a database we can map to, I go with an existing, freely available sample. For years I’ve chosen the Sakila sample database from MySQL.

(As an aside, it took me years to realize that the word Sakila is actually intended to be a subtle joke. It’s the letters SQL with some vowels thrown in to make it possible to pronounce the word, with the “Q” replaced by a “k”. Go figure. As it turns out, it’s also the name of their dolphin emblem, but that’s neither here nor there.)

Over the years I’ve tried to map the Sakila schema to a Grails domain model, with varying degrees of success. I think I have it all worked out now, but that’s the subject for another blog post. Here, instead, I want to talk about stored procedures.

Many of my clients want to use Grails with a database that has lots of stored procedures in it. Hibernate doesn’t like that. Hibernate wants to map all the domain classes directly to tables, without going through any possible intermediaries. While it is possible to invoke a stored procedure from Hibernate, it’s a bit awkward, mostly because Hibernate doesn’t want to do it.

(Length of Java Persistence Using Hibernate: about 850 pages. Number of pages that discuss stored procedures: about 5.)

That’s where Groovy (as opposed to Grails) comes in. The Groovy API includes the wonderful groovy.sql.Sql class, which is a simple, but effective, façade over JDBC. That class will open and close connections, eliminate all the annoying boilerplate code associated with JDBC, and more. If I ever have to write raw SQL code, I always switch to groovy.sql.Sql rather than use JDBC.

(That’s not quite true. The JdbcTemplate class from Spring is nearly as helpful. If I don’t have Groovy available and I have to do JDBC, I go with that.)

Let me get specific. The Sakila database represents a video store (which shows how old that is). It has tables representing stores, and inventory, and actors, and films, and more. But it also contains a handful of stored procedures, one of which is called film_in_stock. According to the documentation, the film_in_stock procedure takes two input parameters (the id of the film and the id of a store) and one output parameter (the number of copies of that film currently at that store). The example they show is:

mysql> call film_in_stock(1, 1, @count);
+--------------+
| inventory_id |
+--------------+
| 1            |
| 2            |
| 3            |
| 4            |
+--------------+
4 rows in set (0.06 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> select @count;
+--------+
| @count |
+--------+
| 4      |
+--------+
1 row in set (0.00 sec)

There are four copies of film 1 (“Academy Dinosaur”) at store 1 (a store at “47 MySakila Drive” in Alberta). This provides me with a test case:

import spock.lang.*

class StoreProcServiceIntegrationSpec extends Specification {
    def storedProcService  // inject the service

    void "call film_in_stock stored procedure"() {
        expect:
        storedProcService.callFilmInStock(1, 1) == 4
    }
}

This assumes my call to the stored procedure happens in a class called StoredProcService. I have to use an integration test here, because I want to use the real database, but that means I can rely on Spring dependency injection to get the service into the test.

To access the Sakila database from a Grails app, I configured my conf/DataSource.groovy file to include the dataSource properties:

import org.hibernate.dialect.MySQL5InnoDBDialect

dataSource {
    pooled = true
    jmxExport = true
    driverClassName = "com.mysql.jdbc.Driver"
    username = "root"
    password = ""
    dialect = MySQL5InnoDBDialect
}
// ...
environments {
    development {
        dataSource {
            dbCreate = "validate"
            url = "jdbc:mysql://localhost:3306/sakila"
        }
    }
    test {
        dataSource {
            dbCreate = "validate"
            url = "jdbc:mysql://localhost:3306/sakila"
        }
    }
// ...
}

(Yeah, I have super high security on this database. Don’t even think about breaking in. You’d never guess that the root user has no password whatsoever. I mean, who would do such a ridiculous thing?)

So the default data source in my Grails app is the Sakila database, using a MySQL driver that I have listed in the conf/BuildConfig.groovy file:

dependencies {
    runtime 'mysql:mysql-connector-java:5.1.29'
    test "org.grails:grails-datastore-test-support:1.0-grails-2.4"
}

Again, nothing terribly unusual here.

The key observation is that the dataSource reference in the config file refers to a Spring bean, which can be injected in the usual manner. So I created a Grails service called StoredProcService and specified the dataSource bean as a dependency.

@Transactional
class StoredProcService {
    def dataSource
    // ...
}

The GroovyDocs for the Groovy API show that the groovy.sql.Sql class has a constructor that takes a data source, so I’m all set. Then, to call the stored procedure, I need to invoke the call method from that class.

This is where life gets interesting, and ultimately tricky. The procedure I want to call has both input and output parameters, so my call has to take that into account. It turns out that the way to call a stored procedure with both inputs and outputs is to invoke:

void call(String sql, List<Object> params, Closure closure)

The SQL string uses the JDBC escape syntax for stored procedure calls. The params list take the input parameters and placeholders for the output parameters, and the closure is then invoked with the output parameters.

Wait, what? That’s a little fast. Here’s the actual call, or at least this is the version I had before last weekend:

import groovy.sql.Sql

@Transactional
class StoredProcService {
    def dataSource
    
    int callFilmInStock(filmId, storeId) {
        String sqlTxt = '{call film_in_stock(?,?,?)}' // JDBC escape syntax
        Sql db = new Sql(dataSource)  // use the injected datasource
        db.call(sqlTxt, [filmId, storeId, Sql.INTEGER]) { count ->
            // use the count here
        }
    }
}

The Sql class provides constants like “static OutParameter INTEGER” to represent output variable in the stored procedure call. Then the last argument is a closure that has as many arguments as there are output parameters (in this case, one, which I called count).

Now, though, I have a problem. I’d like my callFilmInStock method to return the result of invoking the film_in_stock stored procedure, i.e., the count of films at that store. The problem is, I can’t just say return count inside that closure, because returning from a closure returns only from the closure, not the calling method.

I’ve blogged about that before, and even submitted a variation to Baruch Sadogursky as a Groovy Puzzler, but this is where I first encountered that problem.

My solution was always to create a local variable outside the call, assign the count to that variable, and return the variable:

int callFilmInStock(filmId, storeId) {
    String sqlTxt = '{call film_in_stock(?,?,?)}' // JDBC escape syntax
    Sql db = new Sql(dataSource)  // use the injected datasource
    int result = 0
    db.call(sqlTxt, [filmId, storeId, Sql.INTEGER]) { count ->
        result = count
    }
    return result
}

This works, and (sigh) I’ve been teaching this approach for some time now. I’ve always been uncomfortable with it, though, and when I started digging into Java 8 lambdas I realized why. I’m using a closure here and relying on it having a side effect, which is to modify a variable defined outside the closure. In fact, in Java 8, lambdas are definitely not supposed to do that. The only variables lambdas are supposed to access from outside the lambda are those that are “effectively final”, meaning they’re never modified. Groovy closures don’t have that limitation, but I still had this nagging feeling that I was, as they say, doing it wrong.

Last weekend, the new NFJS season started for me in Minneapolis, and I got a chance to talk about this to the inimitable Venkat Subramaniam, who writes books on this stuff (among other things). After I explained the problem, he thought about it for about five whole entire minutes and said that I should be passing the method a closure, not returning an integer.

The right way to write my service function is to add a closure to the method signature and use it in the sql call:

void callFilmInStock(filmId, storeId, closure) {
    String sqlTxt = '{call film_in_stock(?,?,?)}' // JDBC escape syntax
    Sql db = new Sql(dataSource)  // use the injected datasource
    db.call(sqlTxt, [filmId, storeId, Sql.INTEGER], closure)
}

Wow, that’s so much simpler. The client now passes in a closure that uses the returned value, rather than trying to kludge returning the value itself. That means the test case becomes:

void "call film_in_stock stored procedure"() {
    expect:
    storedProcService.callFilmInStock(1, 1) { count ->
        assert count == 4
    }
}

(This uses the normal Groovy idiom where a method taking a closure as its last argument can put the closure after the parentheses.)

This test passes just as the previous one did, even though the method return type is now void.

That’s the part I never got before. The groovy.sql.Sql.call(String, List, Closure) method takes a closure as its last argument, which means I can supply the closure myself and the method will use it with the returned count.

When I learned about functional programming, I got the idea of transforming collections rather than iterating over them, and using filters to select only the values I want, and reducing the results down to single values. I got that I’m supposed to favor immutability wherever possible. What I apparently hadn’t grokked was sending closures as method arguments and how that simplified everything. I’m still not sure I’m all the way there, but I’m certainly a lot closer now.

Over the weekend I tweeted that Venkat had fundamentally changed my understanding of functional programming, which is both cool and seriously annoying. Frankly, those are also words I would use to describe Venkat. If he wasn’t such a nice guy, he would be insufferable, mostly due to sheer jealousy. But we rapidly learn on the NFJS tour never to compare ourselves to Venkat in any way or you’ll just drive yourself crazy. Just don’t tell him I said that.

Now I have to go update my Grails materials…

About Ken Kousen
I teach software development training courses. I specialize in all areas of Java and XML, from EJB3 to web services to open source projects like Spring, Hibernate, Groovy, and Grails. Find me on Google+ I am the author of "Making Java Groovy", a Java / Groovy integration book published by Manning in the Fall of 2013, and "Gradle Recipes for Android", published by O'Reilly in 2015.

7 Responses to If a method arg is a closure, for crying out loud pass it a closure

  1. Very cool stuff. There’s a plugin for that (TM) that will help to reduce a lot of the tedious work of mapping existing tables: http://grails.org/plugin/db-reverse-engineer, and I did use Sakila as one of the test databases when developing the plugin. Also, tangentially related – I discovered recently that they’re doing a new edition of Java Persistence with Hibernate: http://www.manning.com/bauer3/

  2. Ken Kousen says:

    I’ve recommended your db-reverse-engineer plugin many times. I’ve had difficulty applying it to Sakila, though, mostly because Sakila uses some odd datatypes. I’ll discuss that in a different blog post.

    I am subscribed to the second edition of Java Persistence with Hibernate, but haven’t yet spent a lot of time with it.

    Great to hear from you, btw. I’m still waiting for you to publicly announce what you’re doing these days. 🙂

  3. Ty Connell says:

    Thanks Ken, glad I stumbled on this. This seems like “closure as method argument” implies “separate the logic of the computation itself from ‘what to do with the result’ and ‘how to obtain the input'” (Chiusano, Bjaranson, “Functional Programming in Scala”, pp. 12). Surprised how much I still learn every day, after all these years.

  4. Ken Kousen says:

    Ty — You and me both, friend. 🙂 Thanks for your comment.

  5. So, I’ve used the former pattern for calling a stored procedure in Oracle and had the same vague unease about it. Still, when what you want is the return value of a stored proc, your original pattern seems to be the most straightforward way to get it. The new pattern definitely makes your app more functional, and in other scenarios, I could see that being a good thing. But for this case, where the result comes from a horribly stateful place (the database), doesn’t functional modeling ultimately just push the problem up to the client?

    Sure, if most of your use cases _want_ to pass the result of the stored procedure through a closure, then this is great. But if all you really want is to get the count, then the client is going to be crafting a closure to modify a variable outside of the closure’s scope, just like before. And worse, you may end up doing that in every place you call the procedure, which means your app isn’t as DRY as it could be. If what you want is the count, why not construct the method to return the count, warts and all? At least then you’ve isolated the expectation of getting a count back, rather than proliferating it throughout your code.

    It could very well be that I haven’t fully grokked the power of functional programming for this case. Still, I’d posit that this kind of interaction with the DB is precisely where the capacity for Groovy to switch freely between functional and OO (even imperative) styles would shine. Though that could just be me being a Gary Bernhardt sycophant (viz https://www.destroyallsoftware.com/talks/boundaries).

  6. Pingback: Diario di Grails (settimana 11 del 2015) | BME

  7. Ken Kousen says:

    As I’ve talked to some functional people, I agree that a big part of the issue is the awkwardness of the API. The call method returns void, and that’s part of the problem. If it returned an object, or even a list, then this situation would be much easier.

    As you say, the existing design favors an OO (or even imperative) approach, and trying to do it “correctly” with the functional approach may be overkill.

    In the end, I’d say I agree with you. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: