Stored procedure implementation in most SQL database servers is vendor-specific. JDBC offers a generic way for calling those and Groovy's Sql
class helps to simplify that task.
This recipe will demonstrate how to utilize the Sql
class to make stored procedure calls.
Let's use the same cookbook database, created and populated, like in the Querying an SQL database recipe:
import static DBUtil.* import groovy.sql.Sql def server = startServer() createSchema() populate()
Let's also assume we have defined a stored procedure with the following structure:
CREATE PROCEDURE INGREDIENT_USAGE( OUT INGREDIENTS_RATE INTEGER, IN INGREDIENT_NAME VARCHAR(100)) READS SQL DATA BEGIN ATOMIC SELECT COUNT(*) INTO INGREDIENTS_RATE FROM INGREDIENT WHERE NAME LIKE '%' || INGREDIENT_NAME || '%'; END
The INGREDIENT_USAGE
procedure declares one IN
parameter and one OUT
parameter. The input parameter determines what kind of ingredient we are searching for, and the output parameter returns the number of times that ingredient appears in our recipes.
You can use the same approach we used for other DDL statements in the Creating a database table recipe to append a new stored procedure definition to the database schema.
The following simple steps demonstrate how we can accomplish our recipe's goal:
def params = [ Sql.INTEGER, 'sugar' ]
call
method and JDBC syntax we can invoke the desired procedure in the database:def sql = Sql.newInstance(dbSettings) sql.call( '{ CALL INGREDIENT_USAGE(:rate, :pattern) }', params) { rate -> println "Sugar usage: $rate" }
Sugar usage: 2
We have specified two values in the list passed to the call
method. The first value is the type of the OUT
parameter that we expect to receive, and the second one is the value for the IN
parameter that we pass to the procedure. Sql.DOUBLE
is a constant (of groovy.sql.OutParameter
type) defined in the groovy.sql.Sql
class. There is a constant for every standard JDBC type.
OUT
parameter values received back from the procedure are passed to the closure given to the call
method as a second parameter. Inside that closure, you are free to manipulate received values, for example, by printing them.
If a stored procedure contains several OUT
, IN
, or even INOUT
parameters, then invoking that does not look much more complex. Let's assume we have the following stored procedure signature:
CREATE PROCEDURE INGREDIENT_USAGE2( IN COOKBOOK_ID INTEGER, OUT INGREDIENTS_RATE INTEGER, INOUT INGREDIENT_NAME VARCHAR(100)) READS SQL DATA BEGIN ATOMIC ... END
Calling that procedure will look as follows:
def params = [1, Sql.INTEGER, Sql.inout(Sql.VARCHAR('sugar'))] sql.call( '{ CALL INGREDIENT_USAGE2(:cookbook_id, :rate, :pattern)}', params) { rate, pattern -> println rate println pattern }
The difference from our original snippet is that we pass the INOUT
parameter with the help of the Sql.inout(Sql.VARCHAR('sugar'))
construct and that final closure runs over two output parameters, rate and pattern.