Groovy has great SQL support built-in. With just a few lines of code we can access a database and write SQL statements and execute them. To query data we can for example use the rows() method. We pass a SQL query and we get a List of GroovyResultSet objects back. But we can also use other methods like query() or eachRow().
The query may contain named placeholders. For example :name or ?.id. Both the : and ?. syntax are supported. To give the named parameters a value we pass an extra object to the query method. This object can be a Map, Expando object or any object with properties matching the names of the named parameter.
Besides we can even used named ordinal parameters. Then we must use the ?. syntax. We set the ordinal (1-based) after the question mark. Now we pass multiple objects with values for the named parameters to the query method. For each ordinal we must pass an object with values for that named ordinal parameter.
The following sample shows the possibilities:
@Grapes([
@Grab(group='com.h2database', module='h2', version='1.3.160'),
@GrabConfig(systemClassLoader = true)
])
import com.h2database.*
import groovy.sql.*
def db = Sql.newInstance('jdbc:h2:mem:', 'sa', '', 'org.h2.Driver')
// Setup database.
db.execute '''
create table if not exists languages(
id int primary key,
name varchar(20) not null
)
'''
db.execute "insert into languages values(1, 'Groovy')"
db.execute "insert into languages values(2, 'Java')"
// ------------------
// Ready for queries:
// ------------------
// Use :name syntax for named parameters.
def result = db.rows('select * from languages where name = :name',
[name: 'Groovy'])
assert result[0] == [ID: 1, NAME: 'Groovy']
// Different syntax with ?.name.
result = db.rows('select * from languages where name = ?.name',
[name: 'Groovy'])
assert result[0] == [ID: 1, NAME: 'Groovy']
// We can use our own classes with properties matching
// query parameters.
class QueryParams {
String name = 'Java'
Integer id = 2
}
result = db.rows('select * from languages where name = ?.name and id = :id',
new QueryParams())
assert result[0] == [ID: 2, NAME: 'Java']
// We can use ordinal named parameters.
// For each ordinal placeholder we must pass an object
// with values.
result = db.rows('select * from languages where name = ?1.name and id = ?2.id',
[name: 'Groovy'] /* ?1.name */, [id: 1] /* ?2.id */)
assert result[0] == [ID: 1, NAME: 'Groovy']
result = db.rows('select * from languages where name = ?1.name and id = ?2.id',
[name: 'Groovy'], new Expando([id: 1]))
assert result[0] == [ID: 1, NAME: 'Groovy']
result = db.rows('select * from languages where name = ?1.name or name = ?2.name',
new QueryParams(), [name: 'Groovy'])
assert result[0] == [ID: 1, NAME: 'Groovy']
assert result[1] == [ID: 2, NAME: 'Java']
