groovy sql

public class Sql
extends Object

A facade over Java's normal JDBC APIs providing greatly simplified resource management and result set handling. Under the covers the facade hides away details associated with getting connections, constructing and configuring statements, interacting with the connection, closing resources and logging errors. Special features of the facade include using closures to iterate through result sets, a special GString syntax for representing prepared statements and treating result sets like collections of maps with the normal Groovy collection methods available.

Typical usage

First you need to set up your sql instance. There are several constructors and a few newInstance factory methods available to do this. In simple cases, you can just provide the necessary details to set up a connection (e.g. for hsqldb):

 def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbcDriver']
 def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
 

or if you have an existing connection (perhaps from a connection pool) or a datasource use one of the constructors:

 def sql = new Sql(datasource)
 

Now you can invoke sql, e.g. to create a table:

 sql.execute '''
     create table PROJECT (
         id integer not null,
         name varchar(50),
         url varchar(100),
     )
 '''
 

Or insert a row using JDBC PreparedStatement inspired syntax:

 def params = [10, 'Groovy', 'http://groovy.codehaus.org']
 sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', params
 

Or insert a row using GString syntax:

 def map = [id:20, name:'Grails', url:'http://grails.codehaus.org']
 sql.execute "insert into PROJECT (id, name, url) values ($map.id, $map.name, $map.url)"
 

Or a row update:

 def newUrl = 'http://grails.org'
 def project = 'Grails'
 sql.executeUpdate "update PROJECT set url=$newUrl where name=$project"
 

Now try a query using eachRow:

 println 'Some GR8 projects:'
 sql.eachRow('select * from PROJECT') { row ->
     println "${row.name.padRight(10)} ($row.url)"
 }
 

Which will produce something like this:

 Some GR8 projects:
 Groovy     (http://groovy.codehaus.org)
 Grails     (http://grails.org)
 Griffon    (http://griffon.codehaus.org)
 Gradle     (http://gradle.org)
 

Now try a query using rows:

 def rows = sql.rows("select * from PROJECT where name like 'Gra%'")
 assert rows.size() == 2
 println rows.join('\n')
 

with output like this:

 [ID:20, NAME:Grails, URL:http://grails.org]
 [ID:40, NAME:Gradle, URL:http://gradle.org]
 

Also, eachRow and rows support paging. Here's an example:

 sql.eachRow('select * from PROJECT', 2, 2) { row ->
     println "${row.name.padRight(10)} ($row.url)"
 }
 

Which will start at the second row and return a maximum of 2 rows. Here's an example result:

 Grails     (http://grails.org)
 Griffon    (http://griffon.codehaus.org)
 

Finally, we should clean up:

 sql.close()
 

If we are using a DataSource and we haven't enabled statement caching, then strictly speaking the final close() method isn't required - as all connection handling is performed transparently on our behalf; however, it doesn't hurt to have it there as it will return silently in that case.

 

Named and named ordinal parameters

Several of the methods in this class which have a String-based sql query and params in a List or Object[] support named or named ordinal parameters. These methods are useful for queries with large numbers of parameters - though the GString variations are often preferred in such cases too.

Named parameter queries use placeholder values in the query String. Two forms are supported ':propname1' and '?.propname2'. For these variations, a single model object is supplied in the parameter list. The propname refers to a property of that model object. The model object could be a map, Expando or domain class instance. Here are some examples:

 println sql.rows('select * from PROJECT where name=:foo', [foo:'Gradle'])
 println sql.rows('select * from PROJECT where name=:foo and id=?.bar', [foo:'Gradle', bar:40])
 class MyDomainClass { def baz = 'Griffon' }
 println sql.rows('select * from PROJECT where name=?.baz', new MyDomainClass())
 

Named ordinal parameter queries have multiple model objects with the index number (starting at 1) also supplied in the placeholder. Only the question mark variation of placeholder is supported. Here is an example:

 println sql.rows("select * from PROJECT where name=?1.baz and id=?2.num", new MyDomainClass(), [num:30])