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:
[sourcecode language=”sql”]
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)
[/sourcecode]
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:
[sourcecode language=”groovy”]
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
}
}
[/sourcecode]
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:
[sourcecode language=”groovy”]
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"
}
}
// …
}
[/sourcecode]
(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:
[sourcecode language=”groovy”]
dependencies {
runtime ‘mysql:mysql-connector-java:5.1.29’
test "org.grails:grails-datastore-test-support:1.0-grails-2.4"
}
[/sourcecode]
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.
[sourcecode language=”groovy”]
@Transactional
class StoredProcService {
def dataSource
// …
}
[/sourcecode]
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:
[sourcecode language=”groovy”]
void call(String sql, List<Object> params, Closure closure)
[/sourcecode]
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:
[sourcecode language=”groovy”]
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
}
}
}
[/sourcecode]
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:
[sourcecode language=”groovy”]
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
}
[/sourcecode]
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:
[sourcecode language=”groovy”]
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)
}
[/sourcecode]
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:
[sourcecode language=”groovy”]
void "call film_in_stock stored procedure"() {
expect:
storedProcService.callFilmInStock(1, 1) { count ->
assert count == 4
}
}
[/sourcecode]
(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…
Leave a Reply