groovy.sql

Class Sql

java.lang.Object

groovy.sql.Sql

Direct Known Subclasses:

DataSet

public class Sql
extends java.lang.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.jdbc.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.

If instead of newInstance you use withInstance, then close() will be called automatically for you.

Avoiding SQL injection

If you find yourself creating queries based on any kind of input from the user or a 3rd party application you might wish to avoid the pure string method variants in this class. While this is safe: sql.firstRow('select * from PersonTable') This example is potentially at risk of SQL injection: sql.firstRow('select * from PersonTable where SurnameColumn = ' + userInput) This in turn will be fine if 'userInput' is something like 'Smith' but maybe not so fine if 'userInput' is something like 'Smith; DROP table PersonTable'. Instead, use one of the variants with parameters and placeholders: sql.firstRow("select * from PersonTable where SurnameColumn = ?", [userInput]) or the GString variants which will be converted to the placeholder variants under the covers: sql.firstRow("select * from PersonTable where SurnameColumn = $userInput") or the named parameter variants discussed next.

Named and named ordinal parameters

Several of the methods in this class (ones which have a String-based sql query and params in a List