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.
This recipe also uses the same table structure and the way to start a database server defined in the Creating a database table recipe.
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.
import static DBUtil.* import groovy.sql.Sql def server = startServer() createSchema()
def cookbooks = [ [ id: 1, title: '30-minute-meals', author: 'Jamie Oliver', year: 2010], [ id: 2, title: 'Ministry of food', author: 'Jamie Oliver', year: 2005], [ id: 3, title: 'Vegan food', author: 'Mr. Spock', year: 2105] ]
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 ) }
assert 3 == sql.rows('SELECT * FROM COOKBOOK').size()
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' )
assert '15-minutes meals' == sql.rows('SELECT * FROM COOKBOOK ' + 'WHERE ID = 1')[0].title
ID
with value 3.sql.execute( 'DELETE from COOKBOOK WHERE ID = :id', [id: 3] ) assert 2 == sql.rows('SELECT * FROM COOKBOOK').size()
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]
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.
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( 14, '50 Superfoods Recipes', 'Rebecca Fallon', 2012) ... }
The previous example shows withBatch
used with a batch size value of 20. For each 20 INSERT
s, the JDBC's executeBatch
will be automatically called. Also in this case, the withBatch
operation works only if the database driver supports it.