Chapter 7

14,

'50 Superfoods Recipes', 'Rebecca Fallon',

2012)

...

}

The previous example shows withBatch used with a batch size value of 20. For each 20 INSERTs, the JDBC's executeBatch will be automatically called. Also in this case, the withBatch operation works only if the database driver supports it.

See also

ff http://groovy.codehaus.org/api/groovy/sql/Sql.html

Calling a stored procedure

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.

Getting ready

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(*)

239

www.it-ebooks.info

Working with Databases in Groovy

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.

How to do it...

The following simple steps demonstrate how we can accomplish our recipe's goal:

1.In order to call a stored procedure you need to create a list of parameter types and values first:

def params = [ Sql.INTEGER, 'sugar' ]

2.Then by using the 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"

}

3.The script should print something similar to:

Sugar usage: 2

How it works...

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.

240

www.it-ebooks.info

Chapter 7

There's more...

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.

See also

ff Creating a database table ff Querying an SQL database

ff http://docs.oracle.com/javase/tutorial/jdbc/basics/ storedprocedures.html

ff http://groovy.codehaus.org/api/groovy/sql/Sql.html

ff http://groovy.codehaus.org/api/groovy/sql/InParameter.html ff http://groovy.codehaus.org/api/groovy/sql/OutParameter.html

ff http://groovy.codehaus.org/api/groovy/sql/InOutParameter.html

241

www.it-ebooks.info

Working with Databases in Groovy

Reading BLOB/CLOB from a database

Large binary object types are meant to store data, which is otherwise not splittable into relational tables and columns, or which is easier to store in a binary form for applicationspecific or performance needs. Groovy does not offer any special methods for handling SQL's large binary objects, but, on the other hand, this recipe will show how to apply Groovy's I/O API extensions to make the code more readable.

Getting ready

Let's again use the database model we created in the Creating a database table recipe. In the RECIPE table, we have the DESCRIPTION column of CLOB type and the IMAGE column of BLOB type.

How to do it...

To read the data, we can just use the same query methods we used in the Querying an SQL database recipe:

sql.eachRow('SELECT * FROM RECIPE') { recipe ->

println recipe.description.characterStream.text

def recipeImage = new File("recipe-${recipe.id}.jpg") recipeImage.delete()

recipeImage << recipe.image.binaryStream

}

How it works...

The types of recipe.description and recipe.image fields are java.jdbc.Clob and java.jdbc.Blob respectively. Those are standard JDBC types used to handle large binary data. By using instances of those types, you can create an InputStream to get the actual data from the database. And that's what happens in the previous code snippet. The only difference between the fields is that they serve different stream types. We just make use

of Clob.getCharacterStream and Blob.getBinaryStream methods.

Thanks to the Groovy extensions of standard Java API, we can transform an InputStream directly into String with the help of the getText method (or simply text). We can as well send another stream directly to a file with the help of the leftShift method

(or simply << operator) available in java.io.File extension.

242

www.it-ebooks.info

Chapter 7

There's more...

You can also write BLOB/CLOB data into the database with simple insert statements. Let's assume we have the following recipe information stored in a Map:

def recipe = [

id: 1000, chapter_id: 100, title: 'steak indian-style', description: '''\

TO START Get all your ingredients and equipment ready. Put a griddle pan on a high heat...

''',

image: Sql.BLOB(new File('steak.jpg').bytes)

]

As you can see we defined the CLOB data of description field as simple string and wrapped the binary image file data into a groovy.sql.InParameter instance with the help of Sql. BLOB method. Now you can just call the execute method to insert data as we did in the

Modifying data in an SQL database recipe:

sql.execute(

'INSERT INTO RECIPE ' +

'VALUES(:id, :chapter_id, :title, :description, :image)', recipe

)

It is important to consider if the data persisted using a BLOB is small enough to comply

with your memory usage requirements. If the size of the BLOB is large then you risk getting a dreaded OutOfMemoryException. To avoid any memory related issue, you have to use plain old JDBC API and stream data into the database.

Also, if CLOB/BLOB support in your database driver is not optimal you need to consider using native support. For example, the Oracle JDBC driver is widely known to require handling of BLOB/CLOB in a native way that is bypassing generic JDBC classes and interfaces, using classes of your driver implementation directly.

See also

ff Creating a database table ff Querying an SQL database

ff http://docs.oracle.com/javase/6/docs/api/java/sql/Blob.html ff http://docs.oracle.com/javase/6/docs/api/java/sql/Clob.html ff http://groovy.codehaus.org/groovy-jdk/java/io/File.html

ff http://groovy.codehaus.org/groovy-jdk/java/io/InputStream.html

243

www.it-ebooks.info