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 varchar(30)
)''')

// now let's populate the table
def people = sql.dataSet("PERSON")
people.add( firstname:"James", lastname:"Strachan", id:1, location_id:10, location_name:'London' )
people.add( firstname:"Bob", lastname:"Mcwhirter", id:2, location_id:20, location_name:'Atlanta' )
people.add( firstname:"Sam", lastname:"Pullara", id:3, location_id:30, location_name:'California' )

// do a query to check it all worked ok
def results = sql.firstRow("select firstname, lastname from PERSON where id=1").firstname
def expected = "James"
assert results == expected

// allow resultSets to be able to be changed
sql.resultSetConcurrency = java.sql.ResultSet.CONCUR_UPDATABLE

// change the data
sql.eachRow("select * from PERSON") {
    it.firstname = it.firstname * 2
}

// reset resultSetsConcurrency back to read only (no further changes required)
sql.resultSetConcurrency = java.sql.ResultSet.CONCUR_READ_ONLY

// do a query to confirm that our change actually worked
results = sql.firstRow("select firstname, lastname from PERSON where id=1").firstname
expected = "JamesJames"
assert results == expected

Combining with MarkupBuilder Example

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

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

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
                      "pswd", "com.mysql.jdbc.Driver")

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

def ignore = 'James'
sql.eachRow("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.

Stored procedure support

import java.sql.Connection
import java.sql.DriverManager
import javax.sql.DataSource
import groovy.sql.Sql
import oracle.jdbc.driver.OracleTypes

driver = oracle.jdbc.driver.OracleDriver
Connection conn = DriverManager.getConnection(
    'jdbc:oracle:thin:sirtest/sirtest@duck.aplpi.lan:1521:orcl');

/*
 *
 * Here we call a procedural block with a closure.
 * ${Sql.INTEGER} and ${Sql.VARCHAR} are out parameters
 * which are passed to the closure.
 *
 */
Sql sql = new Sql(conn);
def a="foo";
String foo = "x";
println "${a}=${a}"
undefinedVar = null
println """
--Simple demonstration of call with closure.
--Closure is called once with all returned values.
"""
sql.call("begin ${Sql.INTEGER}:=20; ${Sql.VARCHAR}:='hello world';end;") {
   answer,string ->

   println "number=[${answer}] string=[${string}]"
   println "answer is a ${answer.class}";
   println "string is a ${string.class}";
   answer += 1;
   println "now number=${answer}"
   println """[${string.replaceAll('o','O')}]"""
}


/*
 * Here we execute a procedural block. The block returns four out
 * parameters, two of which are cursors. We use Sql.resultSet function
 * to indicate that the cursors should be returned as GroovyResultSet.
 *
 *
 *
 */
println """--next we see multiple return values including two ResultSets
--(ResultSets become GroovyResultSets)
--Note the GroovyResultSet.eachRow() function!!
"""

def tableClosure = {println "table:${it.table_name}"};
println("tableClosure is a ${tableClosure.class}");
String owner = 'SIRTEST';

sql.call("""declare
type crsr is ref cursor;
tables crsr;
objects crsr;
begin
select count(*) into ${Sql.INTEGER} from all_tables where owner= ${owner} ;
open tables for select * from all_tables where owner= ${owner} ;
${Sql.resultSet OracleTypes.CURSOR} := tables;
select count(*) into ${Sql.INTEGER} from all_objects where owner= ${owner} ;
open objects for select * from all_objects where owner= ${owner};
${Sql.resultSet OracleTypes.CURSOR} := objects;
end;
"""
){t,user_tables,o,user_objects ->
	println "found ${t} tables from a total of ${o} objects"
        // eachRow is a new method on GroovyResultSet
	user_tables.eachRow(){x ->println "table:${x.table_name}"}
	user_objects.eachRow(){println "object:${it.object_name}"}
}


/*
 * Determine if we have the stored procedure 'fred' needed
 * for the next test.
 *
 */
Integer procLines = 0
sql.eachRow("select count(*) lines from user_source where name='FRED' and type='FUNCTION'"){
    procLines = it.lines
}

if(procLines ==0) {
  print """
--to demonstrate a function accepting an inout parameter
--and returning a value, create the following function in your schema
create or replace function fred(foo in out varchar2) return number is
begin
foo:='howdy doody';
return 99;
end;
"""

}else{
 /*
  * Here is a call to a function, passing in inout parameter.
  * The function also returns a value.
  */
  println "Next call demonstrates a function accepting inout parameter and returning a value"
  sql.call("{ ${Sql.INTEGER} = call fred(${Sql.inout(Sql.VARCHAR(foo))}) }") {
    answer,string ->
    println "returned number=[${answer}] inout string coming back=[${string}]"
}


println "--Same again, but this time passing a null inout parameter"
sql.call("{ ${Sql.INTEGER} = call fred(${Sql.inout(Sql.VARCHAR(undefinedVar))}) }") {
   answer,string ->
   println "returned number=[${answer}] inout string coming back=[${string}]"
   answer = answer + 1;
   println "Checked can increment returned number, now number=${answer}"
   println """[${string.replaceAll('o','O')}]"""
}

}

/*
 * Finally a handy function to tell Sql to expand a variable in the
 * GString rather than passing the value as a parameter.
 *
 */
["user_tables","all_tables"].each(){table ->
    sql.eachRow("select count(*) nrows from ${Sql.expand table}") {
        println "${table} has ${it.nrows} rows"
    }
}

Clob Notes

CLOB are objects that cannot be extracted with a piece of code like:

data = sql.rows("select clobdata from ....")

because later

data.each { ... do something ... }

can fail. This happens cause the object is not reachable any more since the connection might be already closed: note I say might cause at least on Oracle the errors are random (and hard to understand).

A possible way to act on CLOB data is to use eachRow

data = sql.eachRow("select clobdata from ....") { ... do something ... }

Further Information

There is also an additional GSQL module you might want to check out.