Groovy - Groovy SQL

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
import groovy.sql.TestHelper

foo = 'cheese'
sql = TestHelper.makeSql()

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
import groovy.sql.TestHelper

foo = 'cheese'
sql = TestHelper.makeSql()

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
import groovy.sql.TestHelper

sql = TestHelper.makeSql()

food = sql.dataSet('FOOD')
cheese = food.findAll { it.type == 'cheese' }
cheese.each { println "Eat ${it.name}" }

h2 Examples

Here's an example of using Groovy SQL along with GroovyMarkup .

import groovy.sql.Sql
import groovy.sql.TestHelper
import groovy.xml.MarkupBuilder

sql = TestHelper.makeSql()

// lets output some XML builder 
// could be SAX / StAX / DOM / TrAX / text etc
xml = new MarkupBuilder()

ignore = 'James'
sql.queryEach ("select * from person where firstname != ${ignore}") { person | 
    // lets process each row by emitting some markup
    xml.customer(id:person.id, type:'Customer', name:person.firstname + " " + person.lastname) 
}

This could generate, dynamically something like

<customers>
  <customer id="123" type="Customer" foo="whatever">
    <role>partner</role>
    <name>James</name>
    <location id="5" name="London"/>
  </customer>
</customers>

There's an example test case which demonstrates all of these query mechanisms in action.