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.

<p>Groovy supports a few neat ways to work with SQL more easily and to make SQL more Groovy. You can perform queries and SQL statements, passing in variables easily with proper handling of statements, connections and exception handling thanks to closures.</p>

def foo = 'cheese'
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
                      "pswd", "com.mysql.jdbc.Driver")

sql.eachRow("select * from FOOD where type=${foo}") {
    println "Gromit likes ${it.name}"
}
</pre></td></tr></table>
<p>In the above example, you can refer to the various columns by name, using the property syntax on the row variable (e.g. it.name) or you can refer to the columns by their index (e.g. it[0]) For example:</p>

def foo = 'cheese'
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
                      "pswd", "com.mysql.jdbc.Driver")

def answer = 0
sql.eachRow("select count(*) from FOOD where type=${foo}") { row ->
	answer = row[0]
}
assert answer > 0
</pre></td></tr></table>
<p>Or you can create a <strong>DataSet</strong> which allows you to query SQL using familar closure syntax so that the same query could work easily on in memory objects or via SQL. e.g.</p>

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
                      "pswd", "com.mysql.jdbc.Driver")

def food = sql.dataSet('FOOD')
def cheeses = food.findAll { it.type == 'cheese' }
cheeses.each { println "Eat ${it.name}" }
</pre></td></tr></table>

<h2>Advanced Usage</h2>

<p>In this example, we create a table, make changes to it and confirm the changes worked.</p>

// delete table if previously created
try {
   sql.execute("drop table PERSON")
} catch(Exception e){}

// create table
sql.execute('''create table PERSON (
    id integer not null primary key,
    firstname varchar(20),
    lastname varchar(20),
    location_id integer,
    location_name varchar(30)
)''')

// now let's populate the table
def people = sql.dataSet("PERSON")
people.add( firstname:"James", lastname:"Strachan", id:1, location_id:10, location_name:'London' )
people.add( firstname:"Bob", lastname:"Mcwhirter", id:2, location_id:20, location_name:'Atlanta' )
people.add( firstname:"Sam", lastname:"Pullara", id:3, location_id:30, location_name:'California' )

// do a query to check it all worked ok
def results = sql.firstRow("select firstname, lastname from PERSON where id=1").firstname
def expected = "James"
assert results == expected

// allow resultSets to be able to be changed
sql.resultSetConcurrency = java.sql.ResultSet.CONCUR_UPDATABLE

// change the data
sql.eachRow("select * from PERSON") {
    it.firstname = it.firstname * 2
}

// reset resultSetsConcurrency back to read only (no further changes required)
sql.resultSetConcurrency = java.sql.ResultSet.CONCUR_READ_ONLY

// do a query to confirm that our change actually worked
results = sql.firstRow("select firstname, lastname from PERSON where id=1").firstname
expected = "JamesJames"
assert results == expected
</pre></td></tr></table>

<h3>Combining with MarkupBuilder Example</h3>

<p>Here's an example of using Groovy SQL along with <a class="confluence-link" href="/display/GROOVY/GroovyMarkup" data-linked-resource-id="2779" data-linked-resource-type="page" data-linked-resource-default-alias="GroovyMarkup" data-base-url="http://docs.codehaus.org">GroovyMarkup</a></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
import groovy.xml.MarkupBuilder

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
                      "pswd", "com.mysql.jdbc.Driver")

// lets output some XML builder
// could be SAX / StAX / DOM / TrAX / text etc
def xml = new MarkupBuilder()

def ignore = 'James'
sql.eachRow("select * from person where firstname != ${ignore}") { person ->
    // lets process each row by emitting some markup
    xml.customer(id:person.id, type:'Customer',
        name:"$person.firstname $person.lastname" )
}
</pre></td></tr></table>
<p>This could generate, dynamically something like</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>&lt;customers>
  &lt;customer id="123" type="Customer" foo="whatever">
    &lt;role>partner&lt;/role>
    &lt;name>James&lt;/name>
    &lt;location id="5" name="London"/>
  &lt;/customer>
&lt;/customers>
</pre></td></tr></table>
<p>There's an example <a href="https://svn.codehaus.org/groovy/trunk/groovy/groovy-core/src/test/groovy/sql/SqlCompleteTest.groovy">test case which demonstrates</a> all of these query mechanisms in action.</p>

<h3>Stored procedure support</h3>

<p>An example of calling a <code>FullName</code> stored procedure which takes a param ('Sam' in the example) and returns a <code>VARCHAR</code>.</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.call("{? = call FullName(?)}", [Sql.VARCHAR, 'Sam']) { name ->
    assert name == 'Sam Pullara'
}
</pre></td></tr></table>
<p>The same example again but with a GString variation:</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 first = 'Sam'
sql.call("{$Sql.VARCHAR = call FullName($first)}") { name ->
    assert name == 'Sam Pullara'
}
</pre></td></tr></table>
<p>Here is an example of a stored procedure with an out parameter:</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.call '{call Hemisphere(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells ->
    println dwells // => Northern Hemisphere
}
</pre></td></tr></table>

<p>Some additional advanced stored procedure examples:</p>

driver = oracle.jdbc.driver.OracleDriver
Connection conn = DriverManager.getConnection(
    'jdbc:oracle:thin:sirtest/sirtest@duck.aplpi.lan:1521:orcl');

/*
 *
 * Here we call a procedural block with a closure.
 * ${Sql.INTEGER} and ${Sql.VARCHAR} are out parameters
 * which are passed to the closure.
 *
 */
Sql sql = new Sql(conn);
def a="foo";
String foo = "x";
println "${a}=${a}"
undefinedVar = null
println """
--Simple demonstration of call with closure.
--Closure is called once with all returned values.
"""
sql.call("begin ${Sql.INTEGER}:=20; ${Sql.VARCHAR}:='hello world';end;") {
   answer,string ->

println "number=[${answer}] string=[${string}]"
   println "answer is a ${answer.class}";
   println "string is a ${string.class}";
   answer += 1;
   println "now number=${answer}"
   println """[${string.replaceAll('o','O')}]"""
}

/*
 * Here we execute a procedural block. The block returns four out
 * parameters, two of which are cursors. We use Sql.resultSet function
 * to indicate that the cursors should be returned as GroovyResultSet.
 *
 *
 *
 */
println """--next we see multiple return values including two ResultSets
--(ResultSets become GroovyResultSets)
--Note the GroovyResultSet.eachRow() function!!
"""

def tableClosure = {println "table:${it.table_name}"};
println("tableClosure is a ${tableClosure.class}");
String owner = 'SIRTEST';

sql.call("""declare
type crsr is ref cursor;
tables crsr;
objects crsr;
begin
select count(*) into ${Sql.INTEGER} from all_tables where owner= ${owner} ;
open tables for select * from all_tables where owner= ${owner} ;
${Sql.resultSet OracleTypes.CURSOR} := tables;
select count(*) into ${Sql.INTEGER} from all_objects where owner= ${owner} ;
open objects for select * from all_objects where owner= ${owner};
${Sql.resultSet OracleTypes.CURSOR} := objects;
end;
"""
){t,user_tables,o,user_objects ->
	println "found ${t} tables from a total of ${o} objects"
        // eachRow is a new method on GroovyResultSet
	user_tables.eachRow(){x ->println "table:${x.table_name}"}
	user_objects.eachRow(){println "object:${it.object_name}"}
}

/*
 * Determine if we have the stored procedure 'fred' needed
 * for the next test.
 *
 */
Integer procLines = 0
sql.eachRow("select count(*) lines from user_source where name='FRED' and type='FUNCTION'"){
    procLines = it.lines
}

if(procLines ==0) {
  print """
--to demonstrate a function accepting an inout parameter
--and returning a value, create the following function in your schema
create or replace function fred(foo in out varchar2) return number is
begin
foo:='howdy doody';
return 99;
end;
"""

}else{
 /*
  * Here is a call to a function, passing in inout parameter.
  * The function also returns a value.
  */
  println "Next call demonstrates a function accepting inout parameter and returning a value"
  sql.call("{ ${Sql.INTEGER} = call fred(${Sql.inout(Sql.VARCHAR(foo))}) }") {
    answer,string ->
    println "returned number=[${answer}] inout string coming back=[${string}]"
}

println "--Same again, but this time passing a null inout parameter"
sql.call("{ ${Sql.INTEGER} = call fred(${Sql.inout(Sql.VARCHAR(undefinedVar))}) }") {
   answer,string ->
   println "returned number=[${answer}] inout string coming back=[${string}]"
   answer = answer + 1;
   println "Checked can increment returned number, now number=${answer}"
   println """[${string.replaceAll('o','O')}]"""
}

}

/*
 * Finally a handy function to tell Sql to expand a variable in the
 * GString rather than passing the value as a parameter.
 *
 */
["user_tables","all_tables"].each(){table ->
    sql.eachRow("select count(*) nrows from ${Sql.expand table}") {
        println "${table} has ${it.nrows} rows"
    }
}
</pre></td></tr></table>

<h3>Clob Notes</h3>

<p>CLOB are objects that cannot be extracted with a piece of code like:</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>data = sql.rows("select clobdata from ....")
</pre></td></tr></table>
<p>because later</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>data.each { ... do something ... }
</pre></td></tr></table>
<p>can fail. This happens cause the object is not <em>reachable</em> any more since the connection might be already closed: note I say might cause at least on Oracle the errors are random (and hard to understand).</p>

<p>A possible way to act on CLOB data is to use eachRow</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>data = sql.eachRow("select clobdata from ....") { ... do something ... }
</pre></td></tr></table>

<h3>Further Information</h3>

<p>There is also an additional <a class="confluence-link" href="/display/GROOVY/GSQL" data-linked-resource-id="17023" data-linked-resource-type="page" data-linked-resource-default-alias="GSQL" data-base-url="http://docs.codehaus.org">GSQL</a> module you might want to check out.</p>