Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: typo correction as reported by Raymond Camden

Groovy SQL

This section borrows some content from this GroovySQL article, by Andrew Glover. If some of the references to JDBC don't make sense, don't worry. There is one new language construct that is used below, which is the inclusion of variables in string definitions. For example try the following:

Code Block

piEstimate = 3
println("Pi is about ${piEstimate}")
println("Pi is closer to ${22/7}")

...

Your first Groovy SQL code consists of three lines.

Code Block

import groovy.sql.Sql
sql = Sql.newInstance( 'jdbc:jtds:sqlserver://serverName/dbName-CLASS;domain=domainName', 'username',
                       'password', 'net.sourceforge.jtds.jdbc.Driver' )
sql.eachRow( 'select * from tableName' ) { println "$it.id -- ${it.firstName} --" }

...

So the output from a row might look like:

Code Block
sql
sql

001 -- Lane --

Retrieving a single value from DB

If all you need is a value of one or a few columns of a single row in the DB, you could do this

Code Block

row = sql.firstRow('select columnA, columnB from tableName')
println "Row: columnA = ${row.columnA} and columnB = ${row.columnB}"

...

Here you see a simple insert that uses variable substitution again with the ${} syntax. This code simply inserts a new row into the people table.

Code Block

firstName = 'yue'
lastName = "O'shea"
sql.execute("insert into people (firstName, lastName) values (${firstName}, ${lastName})")

...

Another way to do the same thing is to use prepared statements as follows:

Code Block

firstName = 'yue'
lastName = 'wu'
sql.execute('insert into people (firstName, lastName) values (?,?)', [firstName, lastName])

The data that you want to insert is replaced with "?" in the insert statement, and then the values are passed in as an array of data items. Updates are much the same in that they utilize the executeUpdate method. Notice, too, that in Listing 8 the executeUpdate method takes a list of values that will be matched to the corresponding ? elements in the query.

Code Block

comment = 'Lazy bum'
sql.executeUpdate('update people set comment = ? where id=002', [comment])

Deletes are essentially the same as inserts, except, of course, that the query's syntax is different.

Code Block

sql.execute('delete from word where word_id = ?' , [5])

...

If you are content with using your resulting database columns in your business logic, it's nice and easy to just return a collection of GroovyRowResult objects which you can use directly:

Code Block

def getPersons() {
    def persons = []
    sql.eachRow('Select * from Person') {
        persons << it.toRowResult()
    }
    persons
}

If you prefer to use a defined type instead of a GroovyRowResult, as long as your type has all the fields returned from your query you can just do:

Code Block

Person p = new Person( it.toRowResult() )