April 8, 2009

Groovy even makes database access easy

Sigh. Why is everything so easy in Groovy? I had a small request from a client to change a lot of database records. Now, I am no SQL guy, but I know Java and Groovy. So I wrote a little script to access the database and change the value of some records:

import groovy.sql.Sql

// Just copy mysql-connector.jar in $GROOVY_HOME/lib and we
// use the MySql driver.
def sql = Sql.newInstance("jdbc:mysql://localhost/db", "user",
                          "password", "com.mysql.jdbc.Driver")

// Make sure we can update the records.
sql.resultSetConcurrency = java.sql.ResultSet.CONCUR_UPDATABLE

sql.eachRow("select * from articles where description like '%IN STOCK%'") {
    println "Change record with id ${}"
    it.description = it.description.substring(0, it.description.indexOf('IN STOCK'))

println "Done."