Copy Page

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account. You can also Sign Up for a new account.

<h1>Groovy SQL</h1><p>This section borrows some content from this <a href="http://www-106.ibm.com/developerworks/java/library/j-pg01115.html?ca=dgr-lnxw07">GroovySQL article</a>, 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:</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>piEstimate = 3
println("Pi is about ${piEstimate}")
println("Pi is closer to ${22/7}")
</pre></td></tr></table><p>As you can see, in a string literal, Groovy interprets anything inside ${} as a groovy expression.</p><p>This feature is used extensively below.</p><h2>Performing a simple query</h2><p>Your first Groovy SQL code consists of three lines.</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>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} --" }
</pre></td></tr></table><p>The first line is a Java import. It simply tells Groovy the full name of the Sql object. The second line creates a new connection to the SQL database, and stores the connection in the variable sql.</p><p>This code is written for a jTDS connection to a MS SQL Server database. You will need to adjust all the parameters to newInstance to connect to your database, especially <strong>username</strong> and <strong>password</strong>.</p><p>Finally the third line calls the eachRow method of sql, passing in two arguments, the first being the query string, the second being a closure to print out some values.</p><p>Notice that in the closure the fields of &quot;it&quot; are accessed in two different ways. The first is as a simple field reference, accessing the id field of it. The second is the included Groovy expression mentioned above.</p><p>So the output from a row might look like:</p><table class="wysiwyg-macro" data-macro-name="code" data-macro-default-parameter="sql" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGU6c3FsfQ&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>001 -- Lane --
</pre></td></tr></table><h3>Retrieving a single value from DB</h3><p>If all you need is a value of one or a few columns of a single row in the DB, you could do this</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>row = sql.firstRow('select columnA, columnB from tableName')
println "Row: columnA = ${row.columnA} and columnB = ${row.columnB}"
</pre></td></tr></table><h2>Doing more complex queries</h2><p>The previous examples are fairly simple, but GroovySql is just as solid when it comes to more complex data manipulation queries such as insert, update, and delete queries. For these, you wouldn't necessarily want to use closures, so Groovy's Sql object provides the execute and executeUpdate methods instead. These methods are reminiscent of the normal JDBC statement class, which has an execute and an executeUpdate method as well.</p><p>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.</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>firstName = 'yue'
lastName = "O'shea"
sql.execute("insert into people (firstName, lastName) values (${firstName}, ${lastName})")
</pre></td></tr></table><p>Because the sql statement is expressed in a GString, the values firstName and lastName are provided as parameters and so the quote mark in lastName will not be seen as part of the statement.</p><p>Another way to do the same thing is to use prepared statements as follows:</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>firstName = 'yue'
lastName = 'wu'
sql.execute('insert into people (firstName, lastName) values (?,?)', [firstName, lastName])
</pre></td></tr></table><p>The data that you want to insert is replaced with &quot;?&quot; 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.</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>comment = 'Lazy bum'
sql.executeUpdate('update people set comment = ? where id=002', [comment])
</pre></td></tr></table><p>Deletes are essentially the same as inserts, except, of course, that the query's syntax is different.</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>sql.execute('delete from word where word_id = ?' , [5])
</pre></td></tr></table><h2>Other Tips</h2><p>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:</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>def getPersons() {
    def persons = []
    sql.eachRow('Select * from Person') {
        persons &lt;&lt; it.toRowResult()
    }
    persons
}
</pre></td></tr></table><p>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:</p><table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>Person p = new Person( it.toRowResult() )
</pre></td></tr></table>