An agile dynamic language for the Java Platform

 Groovy supports a few neat ways to work with SQL more easily and to make SQL more Groovy. You can perform queries and SQL statements, passing in variables easily with proper handling of statements, connections and exception handling thanks to closures.
import groovy.sql.Sql
def foo = 'cheese'
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
"pswd", "com.mysql.jdbc.Driver")
sql.eachRow("select * from FOOD where type=${foo}") {
println "Gromit likes ${it.name}"
}
In the above example, you can refer to the various columns by name, using the property syntax on the row variable (e.g. it.name) or you can refer to the columns by their index (e.g. it[0]) For example:
import groovy.sql.Sql
def foo = 'cheese'
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
"pswd", "com.mysql.jdbc.Driver")
def answer = 0
sql.eachRow("select count(*) from FOOD where type=${foo}") { row ->
answer = row[0]
}
assert answer > 0
Or you can create a DataSet which allows you to query SQL using familar closure syntax so that the same query could work easily on in memory objects or via SQL. e.g.
import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
"pswd", "com.mysql.jdbc.Driver")
def food = sql.dataSet('FOOD')
def cheese = food.findAll { it.type == 'cheese' }
cheese.each { println "Eat ${it.name}" }
Advanced Usage
In this example, we create a table, make changes to it and confirm the changes worked.
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb",
"user", "pswd", "com.mysql.jdbc.Driver")
// delete table if previously created
try {
sql.execute("drop table PERSON")
} catch(Exception e){}
// create table
sql.execute('''create table PERSON (
id integer not null primary key,
firstname varchar(20),
lastname varchar(20),
location_id integer,
location_name varch