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<Object> or Object[] or Map) 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. Reminder: when you see a variant with Object[] as
the type of the last parameter, Groovy allows vararg style parameters so you don't explicitly need to
create an Object[] and if the first parameter is of type Map, Groovy supports named arguments - examples
of both are contained in the examples below.
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/array/map. 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:
// using rows() with a named parameter with the parameter supplied in a map
println sql.rows('select * from PROJECT where name=:foo', [foo:'Gradle'])
// as above for eachRow()
sql.eachRow('select * from PROJECT where name=:foo', [foo:'Gradle']) {
// process row
}
// an example using both the ':' and '?.' variants of the notation
println sql.rows('select * from PROJECT where name=:foo and id=?.bar', [foo:'Gradle', bar:40])
// as above but using Groovy's named arguments instead of an explicit map
println sql.rows('select * from PROJECT where name=:foo and id=?.bar', foo:'Gradle', bar:40)
// an example showing rows() with a domain object instead of a map
class MyDomainClass { def baz = 'Griffon' }
println sql.rows('select * from PROJECT where name=?.baz', new MyDomainClass())
// as above for eachRow() with the domain object supplied in a list
sql.eachRow('select * from PROJECT where name=?.baz', [new MyDomainClass()]) {
// process row
}
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 are some examples:
// an example showing the model objects as vararg style parameters (since rows() has an Object[] variant)
println sql.rows("select * from PROJECT where name=?1.baz and id=?2.num", new MyDomainClass(), [num:30])
// an example showing the model objects (one domain class and one map) provided in a list
sql.eachRow("select * from PROJECT where name=?1.baz and id=?2.num", [new MyDomainClass(), [num:30]]) {
// do something with row
}
More details
See the method and constructor JavaDoc for more details.
For advanced usage, the class provides numerous extension points for overriding the
facade behavior associated with the various aspects of managing
the interaction with the underlying database.
This class is not thread-safe.