数据库特性

Groovy使用更简洁更简单的方式操作SQL，因此Groovy的SQL是更groovy的 ，通过闭包在执行查询或者SQL语句的时候，你可以传入变量来处理语句（statements）、连接（connections）、异常等，这一切都要归功与Groovy的闭包。看如下示例代码：

import groovy.sql.Sql
 
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}"
}

在上述示例中，你可以在行变量上使用属性名称的语法（比如it.name）来访问字段，也可以通过索引（比如it[0]）来访问字段，再看如下示例：

import groovy.sql.Sql
 
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

或者你也可以创建一个DataSet对象来查询SQL，这时候，你可以使用熟悉的闭包语法来在内存对象或者SQL上做同样的查询。比如：

import groovy.sql.Sql
 
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user",
                      "pswd", "com.mysql.jdbc.Driver")
 
def food = sql.dataSet('FOOD')
def cheese = food.findAll { it.type == 'cheese' }
cheese.each { println "Eat ${it.name}" }

在下面的示例中，我们创建了一个数据库表，并且修改其记录然后再确认所做的修改。

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb",
    "user", "pswd", "com.mysql.jdbc.Driver")
 
// 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

以下是一个结合GroovyMarkup使用的示例：

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" )
}

动态生成的结果可能如下：

<customers>
  <customer id="123" type="Customer" foo="whatever">
    <role>partner</role>
    <name>James</name>
    <location id="5" name="London"/>
  </customer>
</customers>

一个演示各种查询的测试用例。(连接已经失效–译者注）

请看如下示例：

import java.sql.Connection
import java.sql.DriverManager
import javax.sql.DataSource
import groovy.sql.Sql
import oracle.jdbc.driver.OracleTypes
 
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"
    }
}

CLOB的对象不可以象如下代码片段所示的那样提取：

data = sql.rows("select clobdata from ....")

因为在后来

data.each { ... do something ... }

的代码中将会失败，这是因为数据库连接可能已经释放/关闭了，要访问的对象变得不可读：注意我说的只是可能，至少在Oracle中这样的错误好像是随机的（确实让人迷惑）

另外一个访问CLOB数据可行的办法是使用eachRow：

data = sql.eachRow("select clobdata from ....") { ... do something ... }

访问GSQL单独模块