Sunday, October 25, 2009

Groovy Goodness: Groovy SQL

Groovy has powerful SQL support to work with in our scripts and classes. It is all based on JDBC, but so much easier to code. For example to create a connection to a database we only need one line of code and we get a powerful object in return. With this object we can run queries and SQL statements to manipulate data. In this post we look at the basic SQL support in Groovy. In a next post we learn a more powerful Groovy SQL feature: DataSets.

In the following code snippet we are accessing a MySQL database with the name groovy. Both username and password for the database are groovy and MySQL is running on localhost and port 3306 (is default port).

import groovy.sql.*

def username = 'groovy', password = 'groovy', database = 'groovy', server = 'localhost'

// Create connection to MySQL with classic JDBC DriverManager.
def db = Sql.newInstance("jdbc:mysql://$server/$database", username, password, 'com.mysql.jdbc.Driver')

// Or we can create a connection with a DataSource (also via JNDI possible)
def ds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource(
    databaseName: database, user: username, password: password, serverName: server
assert 'jdbc:mysql://localhost:3306/groovy' == ds.url
def dbDS = new Sql(ds)

// Create a new table
db.execute 'drop table if exists languages'
// We can use multi-line strings to create readable SQL in our code.
db.execute '''
    create table languages(
        id integer not null auto_increment,
        name varchar(20) not null,
        primary key(id)

// Fill table with data in different ways.
// First a normal statement.
db.execute 'insert into languages values(null, "Groovy")'
assert 1 == db.updateCount
// String with extra parameters will become a prepared statement.
db.execute 'insert into languages values(null, ?)', ['Java']
assert 1 == db.updateCount
// GString will become a prepared statement.
def langValue = 'JRuby'
db.execute "insert into languages values(null, $langValue)"
assert 1 == db.updateCount

// With executeInsert we get the generated id(s) back.
def insertedIds = db.executeInsert 'insert into languages values(null, "Scalaa")'
assert 4 == insertedIds[0][0]

// executeUpdate return number of rows affected.
def old = 'Scalaa', new = 'Scala'
def updated = db.executeUpdate "update languages set name=$new where name=$old"
assert 1 == updated

// Now let's get data from the table Groovy style.
// With rows we get a list of GroovyResultSet objects and this means we can
// use column names to access data in a row.
def all = db.rows('select * from languages')
assert 4 == all.size()
assert ['Groovy', 'Java', 'JRuby', 'Scala'] == all.collect{ it.name }
assert ['Groovy', 'JRuby'] == all.findAll{ it.name ~= /y/ }

// With eachRow we can use a closure to do something with each row.
// The closure parameter is also of type GroovyResultSet.
def maxId = 3
db.eachRow("select id, name from languages where id < $maxId") { row ->
    if (row.id == 1) assert 'Groovy' == row.name
    if (row.id == 2) assert 'Java' == row.name
db.eachRow("select name from language where name=?", ['Java']) { 
    assert 'Java' == it.name

def countRows = db.firstRow("select count(*) as numberOfRows from languages")
assert 4 == countRows.numberOfRows


Anonymous said...

It would be nice to show the example with Derby as the JDK already comes with it

mrhaki said...

@Anonymous: The sample can be run with Derby, but then the JDBC url changes to: jdbc:derby:$database;create=true and the driver must be org.apache.derby.jdbc.EmbeddedDriver (if we want to run an embedded database).
We can use the org.apache.derby.jdbc.EmbeddedDataSource class to create a DataSource for Derby.
The "if exists" is not recognized by Derby so we must change the drop table statement.

Anonymous said...

Be aware that using GStrings for SQL statements opens you up to SQL injections. So best stick to the parameterised versions in SQL involving user input.

Anonymous said...

Which application uses userid/password in plain text? Please include code that is useful for the reader.

Anonymous said...

There is a typo line 62 for the table name: it should be languages instead of language.

Your examples are interesting.
Thanks for sharing your knowledge.

Post a Comment