Chapter 7

As you can notice we do a nested SQL query, which will benefit from being prepared and cached for performance reasons.

See also

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

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

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

Modifying data in an SQL database

The logical step after querying a database (see the Querying an SQL database recipe) is writing data to it. This recipe shows how a few lines of Groovy and very little boilerplate is needed to do the job of modifying data in a database table.

Getting ready

This recipe also uses the same table structure and the way to start a database server defined in the Creating a database table recipe.

How to do it...

As usual, create a new Groovy script named modifyDb.groovy. As for the previous recipes, in this case we also need to import the DBUtil class.

1.Add the following code to the newly created script:

import static DBUtil.* import groovy.sql.Sql

def server = startServer() createSchema()

2.The next logical step is to define the initial data to write to the database. Add the following code to the script, a variable containing a list of Maps with the data to persist:

def cookbooks = [ [ id: 1,

title: '30-minute-meals', author: 'Jamie Oliver', year: 2010],

[ id: 2,

title: 'Ministry of food',

235

www.it-ebooks.info

Working with Databases in Groovy

author: 'Jamie Oliver', year: 2005],

[ id: 3,

title: 'Vegan food', author: 'Mr. Spock', year: 2105]

]

3.It's all we need to insert data into the COOKBOOK table, by iterating on the list and passing each entry to the execute method:

def sql = Sql.newInstance(dbSettings) cookbooks.each { cookbook ->

sql.execute(

'INSERT INTO COOKBOOK ' + 'VALUES(:id, :title, :author, :year)', cookbook

)

}

4.We can verify that the data was correctly inserted by triggering an SQL query and asserting that data is indeed there:

assert 3 == sql.rows('SELECT * FROM COOKBOOK').size()

5.In order to modify one or more columns of a row, the execute method is again the method to call, this time in conjunction with the UPDATE statement.

sql.execute(

'UPDATE COOKBOOK ' + 'SET title = :title ' + 'WHERE ID = :id',

[title: '15-minutes meals', id: 1]

)

Also in this case, a Map containing the values to alter is passed to the execute method. This method can also be invoked with a single string, as follows:

sql.execute(

'UPDATE COOKBOOK ' +

'SET title = "15-minutes meal" ' + 'WHERE ID = 1'

)

6.Let's check again that our update query has worked as intended:

assert '15-minutes meals' == sql.rows('SELECT * FROM COOKBOOK ' +

'WHERE ID = 1')[0].title

236

www.it-ebooks.info

Chapter 7

7.Deleting one or more rows is as simple. The following statement deletes the row having the ID with value 3.

sql.execute(

'DELETE from COOKBOOK WHERE ID = :id', [id: 3]

)

assert 2 == sql.rows('SELECT * FROM COOKBOOK').size()

How it works...

The execute method of the Sql class does most of the work for you when it comes to executing SQL queries: it gets a connection, builds and configures the SQL statement, fires it, logs any error, and eventually closes the resources, even if an exception is thrown. The method returns a boolean, which is true if the first result is a java.sql.ResultSet object; false if it is an update count or there are no results.

In step 3, we can observe how to execute a simple INSERT statement. What if the INSERT statement is executed on a row with an autogenerated identifier? How do we get the value of the primary key for the newly inserted row?

The groovy.sql.Sql has a handy executeInsert method, which behaves exactly as the execute one, but returns a list of the autogenerated column values for each inserted row. The generated key values are array based. For example, to access the second autogenerated column value of the third row, use keys[3][1]:

def insertedIds = db.executeInsert( 'insert into customers ' + 'values(null, "John", "Doe" )'

)

assert 9000 == insertedIds[0][0]

There's more...

In many cases, updating a database should happen in the context of a transaction. For instance, we may want to execute an INSERT and an UPDATE within a transaction, so that if the second UPDATE statement fails, also the first INSERT is rolled back.

237

www.it-ebooks.info

Working with Databases in Groovy

Groovy's groovy.sql.Sql has a method, which does exactly that, and it's named, not surprisingly, withTransaction. The method accepts a Closure containing the statements to execute.

sql.withTransaction {

sql.executeInsert(

'INSERT INTO COOKBOOK ' + 'VALUES(:id, :title, :author, :year)', [id: 99, title: 'Food for robots',

author: 'Hal Ninethousand', year: 2001]

)

sql.executeInsert(

'INSERT INTO COOKBOOK ' + 'VALUES(:id, :title, :author, :year)', [id: 100, title: '0 Carb Meals', author: 'Harry Slim', year: 1987]

)

}

Should any of the operations inside the withTransaction block fail, the whole block is rolled back, if the underlying database supports transactions.

Another useful feature of the groovy.sql.Sql class introduced in Groovy 1.7 is the withBatch method, which executes a group of SQL statements in a batch. This method is very useful, when for example, we need to read data from a file and persist it in a database:

def updateCounts =

sql.withBatch(20, 'INSERT into COOKBOOK ' + 'values (?, ?, ?,?)') { ps ->

ps.addBatch(

10,

'In Defense of Food: An Eater\'s Manifesto', 'Michael Pokkan',

2009)

ps.addBatch(

12,

'Now....you\'re cooking! Comfort Food Classics', 'Joan Donogh',

2011)

ps.addBatch(

238

www.it-ebooks.info