2. Working with a relational database¶

Groovy 中的 groovy-sql 模块相较于 Java JDBC 技术提供了更高层的抽象。 JDBC 其自身提供了更低层，并且相当全面的 API，其提供对于所有种类的关系型数据库系统统一访问方式。这里例子中，我们将使用 HSQLDB ，你也可以替换使用 Oracle , SQL Server, MySQL 或其他数据库系统。 groovy-sql 模块中使用频率最高的类是 groovy.sql.Sql.

2.1. Connecting to the database¶

使用 Groovy 的 Sql 类连接数据库，需要4部分信息：

The database uniform resource locator (URL)
Username
Password
The driver class name (which can be derived automatically in some situations)

以 HSQLDB 为例，参考下面表格：

Property	Value
url	jdbc:hsqldb:mem:yourdb
user	sa (or your username)
password	yourPassword
driver	org.hsqldb.jdbcDriver

参考 JDBC 驱动文档，来确定你所需要配置的具体值。

Sql 类中有一个 newInstance 工厂方法，来接收以上参数，你可以通常向下面这样使用：

Connecting to HSQLDB

import groovy.sql.Sql

def url = 'jdbc:hsqldb:mem:yourDB'
def user = 'sa'
def password = ''
def driver = 'org.hsqldb.jdbcDriver'
def sql = Sql.newInstance(url, user, password, driver)

// use 'sql' instance ...

sql.close()

If you don’t want to have to handle resource handling yourself (i.e. call close() manually) then you can use the withInstance variation as shown here: 如果你不想手动的管理资源（即：手动调用 close()）, 你可以使用 withInstance 替换。

Connecting to HSQLDB (withInstance variation)

Sql.withInstance(url, user, password, driver) { sql ->
  // use 'sql' instance ...
}

2.1.1. Connecting with a DataSource¶

数据源相对来说，更为常用。你可以从连接池中获取一个有效的连接。这里我们使用 HSQLDB 中提供的一个数据源：

Connecting to HSQLDB with a DataSource

import groovy.sql.Sql
import org.hsqldb.jdbc.JDBCDataSource

def dataSource = new JDBCDataSource(
    database: 'jdbc:hsqldb:mem:yourDB', user: 'sa', password: '')
def sql = new Sql(dataSource)

// use then close 'sql' instance ...

If you have your own connection pooling, the details will be different, e.g. for Apache Commons DBCP: 如果使用你自己的连接池，一些代码细节上就会有些不同，例如使用 Apache Commons DBCP：

Connecting to HSQLDB with a DataSource using Apache Commons DBCP

@Grab('commons-dbcp:commons-dbcp:1.4')
import groovy.sql.Sql
import org.apache.commons.dbcp.BasicDataSource

def ds = new BasicDataSource(driverClassName: "org.hsqldb.jdbcDriver",
    url: 'jdbc:hsqldb:mem:yourDB', username: 'sa', password: '')
def sql = new Sql(ds)
// use then close 'sql' instance ...

2.1.2. Connecting using @Grab¶

The previous examples assume that the necessary database driver jar is already on your classpath. For a self-contained script you can add @Grab statements to the top of the script to automatically download the necessary jar as shown here: 上面例子中都假设所需要的数据库驱动 jar 都在 classpath 中。在你的脚本中，可以在脚本头部通过 @Grab 语句，动态的下载所依赖的 jar ,例如：

Connecting to HSQLDB using @Grab

@Grab('org.hsqldb:hsqldb:2.3.2')
@GrabConfig(systemClassLoader=true)
// create, use, and then close sql instance ...

这里 @GrabConfig 语句是必须的，并确认 system classloader 被使用。这样可以确保，驱动类和 java.sql.DriverManager 这样的系统类都在一个 classloader 中。

2.2. 执行 SQL （Executing SQL）¶

你可以通过 execute() 执行任何 SQL 命令。下面我们来看看，使用它来创建一张表。

2.2.1. 创建表¶

The simplest way to execute SQL is to call the execute() method passing the SQL you wish to execute as a String as shown here: 最简单的执行 SQL 的方式，就是将 SQL 语句传递给 execute() ：

Creating a table

// ... create 'sql' instance
sql.execute '''
  CREATE TABLE Author (
    id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    firstname   VARCHAR(64),
    lastname    VARCHAR(64)
  );
'''
// close 'sql' instance ...

There is a variant of this method which takes a GString and another with a list of parameters. There are also other variants with similar names: executeInsert and executeUpdate. We’ll see examples of these variants in other examples in this section. 这里还有一些方法来支持 GString 和参数列表。还有一些名字类似的方法，如： executeInsert 和 executeUpdate . 在这章节中我们也会看到相关的一些例子。

2.2.2. Basic CRUD operations¶

数据库上的基础操作：create, Read, update, Delete (简称为：CRUD). 这里将一一来验证。

2.2.3. 创建／写入数据¶

你可以使用 insert SQL 语句，调用 execute() 方法，来写入一条记录： You can use the same execute() statement we saw earlier but to insert a row by using a SQL insert statement as follows:

Inserting a row

sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"

You can use a special executeInsert method instead of execute. This will return a list of all keys generated. Both the execute and executeInsert methods allow you to place ‘?’ placeholders into your SQL string and supply a list of parameters. In this case a PreparedStatement is used which avoids any risk of SQL injection. The following example illustrates executeInsert using placeholders and parameters: 你可以使用 executeInsert 方法来替代 execute. 这个方法将返回写入数据主键的列表结构。 executeInsert 和 execute 方法都可以在 SQL 语句中使用 ? 占位符，以及匹配的参数列表。这种预编译方法是可以避免 SQL 的注入风险。下面的例子，将说明如何使用 executeInsert ，占位符，以及参数列表：

Inserting a row using executeInsert with placeholders and parameters

def insertSql = 'INSERT INTO Author (firstname, lastname) VALUES (?,?)'
def params = ['Jon', 'Skeet']
def keys = sql.executeInsert insertSql, params
assert keys[0] == [1]

此外， execute 和 executeInsert 中都可以使用 GString. SQL 中 $ 被假定为占位符。如果你在 SQL 中非常规的位置使用占位符，可以通过 GString 附带其变量实现。可以在 GroovyDoc 中查看更详细的描述。executeInsert 中，允许你提供主键列名称，来指定复合主键的返回值。下面的代码片段，将说明以上的使用规范：

Inserting a row using executeInsert with a GString and specifying key names

def first = 'Guillaume'
def last = 'Laforge'
def myKeyNames = ['ID']
def myKeys = sql.executeInsert """
  INSERT INTO Author (firstname, lastname)
  VALUES (${first}, ${last})
""", myKeyNames
assert myKeys[0] == [ID: 2]

2.2.4. 读取数据¶

可以通过这些方法来读取数据：query, eachRow, firstRow and rows

如果你希望使用 JDBC API 提供的 ResultSet , 你可以使用 query 方法：

Reading data using query

def expected = ['Dierk Koenig', 'Jon Skeet', 'Guillaume Laforge']

def rowNum = 0
sql.query('SELECT firstname, lastname FROM Author') { resultSet ->
  while (resultSet.next()) {
    def first = resultSet.getString(1)
    def last = resultSet.getString('lastname')
    assert expected[rowNum++] == "$first $last"
  }
}

如果你想使用略微高级的抽象，其通过 Groovy map 方式来抽象 ResultSet ，你可以使用 eachRow 方法。

Reading data using eachRow

rowNum = 0
sql.eachRow('SELECT firstname, lastname FROM Author') { row ->
  def first = row[0]
  def last = row.lastname
  assert expected[rowNum++] == "$first $last"
}

你可以使用 list-style 和 map-style 来访问数据。

如果你想使用与 eachRow 类似的功能，并只返回一条记录，你可以调用 firstRow 方法。

Reading data using firstRow

def first = sql.firstRow('SELECT lastname, firstname FROM Author')
assert first.values().sort().join(',') == 'Dierk,Koenig'

使用 rows 方法将返回数据结构的列表：

Reading data using rows

List authors = sql.rows('SELECT firstname, lastname FROM Author')
assert authors.size() == 3
assert authors.collect { "$it.FIRSTNAME ${it[-1]}" } == expected

注意 map-like 抽象是大小写不敏感的 keys （如：可以使用 ‘FIRSTNAME’ or ‘firstname’ ）并且能够使用负数作为索引。

You can also use any of the above methods to return scalar values, though typically firstRow is all that is required in such cases. An example returning the count of rows is shown here: 你可以使用上面的方法返回纯数值，通常使用 firstRow ，这里例子返回计算当前数据的条数：

Reading scalar values

assert sql.firstRow('SELECT COUNT(*) AS num FROM Author').num == 3

2.2.5. 更新数据¶

使用 execute 方法也可以用来更新数据。你可以先 insert author 的 lastname , 其后再更新 firstname :

Updating a row

sql.execute "INSERT INTO Author (lastname) VALUES ('Thorvaldsson')"
sql.execute "UPDATE Author SET firstname='Erik' where lastname='Thorvaldsson'"

这里同样有一个扩展的方法 executeUpdate , 可以返回更新数据行数：

Using executeUpdate

def updateSql = "UPDATE Author SET lastname='Pragt' where lastname='Thorvaldsson'"
def updateCount = sql.executeUpdate updateSql
assert updateCount == 1

def row = sql.firstRow "SELECT * FROM Author where firstname = 'Erik'"
assert "${row.firstname} ${row.lastname}" == 'Erik Pragt'

2.2.6. 删除数据¶

execute 方法，同样可以用来删除数据：

Deleting rows

assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 3
sql.execute "DELETE FROM Author WHERE lastname = 'Skeet'"
assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 2

2.3. 高级 SQL 操作¶

2.3.1. 事务控制¶

使用事务最简单的一种方式，就是使用 withTransaction 必包：

A successful transaction

assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 0
sql.withTransaction {
  sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
  sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')"
}
assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 2

这里数据库初始没有数据，当操作完成后，有两条数据。

If something goes wrong, any earlier operations within the withTransaction block are rolled back. We can see that in operation in the following example where we use database metadata (more details coming up shortly) to find the maximum allowable size of the firstname column and then attempt to enter a firstname one larger than that maximum value as shown here: 如果有任何错误出现，withTransaction 中的操作都讲回滚。我们将在下面的例子中看到，通过给 firstname 列上一个超出最大长度的值，来构造这种操作异常：

A failed transaction will cause a rollback

def maxFirstnameLength
def metaClosure = { meta -> maxFirstnameLength = meta.getPrecision(1) }
def rowClosure = {}
def rowCountBefore = sql.firstRow('SELECT COUNT(*) as num FROM Author').num
try {
  sql.withTransaction {
    sql.execute "INSERT INTO Author (firstname) VALUES ('Dierk')"
    sql.eachRow "SELECT firstname FROM Author WHERE firstname = 'Dierk'", metaClosure, rowClosure
    sql.execute "INSERT INTO Author (firstname) VALUES (?)", 'X' * (maxFirstnameLength + 1)
  }
} catch(ignore) { println ignore.message }
def rowCountAfter = sql.firstRow('SELECT COUNT(*) as num FROM Author').num
assert rowCountBefore == rowCountAfter

尽管第一条语句已经执行成功，但是其也将回滚，数据行数前后也是一致的。

2.3.2. 批量处理¶

当处理大量数据，特别是在写入大量数据时，分段批量处理将更加高效。这里通过使用 withBatch 来操作：

Batching SQL statements

sql.withBatch(3) { stmt ->
  stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
  stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Paul', 'King')"
  stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Guillaume', 'Laforge')"
  stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Hamlet', 'D''Arcy')"
  stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Cedric', 'Champeau')"
  stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Erik', 'Pragt')"
  stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')"
}

执行以上语句，数据库中将添加 7 条记录。如果你想弄清楚这中间到底发生了什么，可以添加一些日志在你的程序中，例如：

Logging additional SQL information

import java.util.logging.*

// next line will add fine logging
Logger.getLogger('groovy.sql').level = Level.FINE
// also adjust logging.properties file in JRE_HOME/lib to have:
// java.util.logging.ConsoleHandler.level = FINE

With this extra logging turned on, and the changes made as per the above comment for the logging.properties file, you should see output such as: 这样打开日志，使用注释里的方法也能达到同样效果：

SQL logging output with batching enable

FINE: Successfully executed batch with 3 command(s)
Apr 19, 2015 8:38:42 PM groovy.sql.BatchingStatementWrapper processResult

FINE: Successfully executed batch with 3 command(s)
Apr 19, 2015 8:38:42 PM groovy.sql.BatchingStatementWrapper processResult

FINE: Successfully executed batch with 1 command(s)
Apr 19, 2015 8:38:42 PM groovy.sql.Sql getStatement

我们还应该注意，任何 SQL 语句都可以加入到批量处理中，并不是只能向同一张表中写入数据.

在之前的提示中，为了避免 SQL 注入，我们尽可能使用预编译语句：

Batching prepared statements

def qry = 'INSERT INTO Author (firstname, lastname) VALUES (?,?)'
sql.withBatch(3, qry) { ps ->
  ps.addBatch('Dierk', 'Koenig')
  ps.addBatch('Paul', 'King')
  ps.addBatch('Guillaume', 'Laforge')
  ps.addBatch('Hamlet', "D'Arcy")
  ps.addBatch('Cedric', 'Champeau')
  ps.addBatch('Erik', 'Pragt')
  ps.addBatch('Jon', 'Skeet')
}

如果数据来自脚本或 web 表单，这提供更加安全选择，这里已经使用预编译语句，我们的批处理语句也就限制为相同的 SQL 操作。

2.3.3. 分页¶

When presenting large tables of data to a user, it is often convenient to present information a page at a time. Many of Groovy’s SQL retrieval methods have extra parameters which can be used to select a particular page of interest. The starting position and page size are specified as integers as shown in the following example using rows: 当给用户展示大表数据，通常都会选择分页展示。Groovy 中很多获取数据方法都扩展可相关参数，可以用于选择分页操作。起始位置，单页大小，被作为参数指定：

Retrieving pages of data

def qry = 'SELECT * FROM Author'
assert sql.rows(qry, 1, 3)*.firstname == ['Dierk', 'Paul', 'Guillaume']
assert sql.rows(qry, 4, 3)*.firstname == ['Hamlet', 'Cedric', 'Erik']
assert sql.rows(qry, 7, 3)*.firstname == ['Jon']

2.3.4. 获取元数据¶

JDBC 中的元数据，可以通过多种方式获取。最基础的方式如下面例子中从数据行中获取：

Using row metadata

sql.eachRow("SELECT * FROM Author WHERE firstname = 'Dierk'") { row ->
  def md = row.getMetaData()
  assert md.getTableName(1) == 'AUTHOR'
  assert (1..md.columnCount).collect{ md.getColumnName(it) } == ['ID', 'FIRSTNAME', 'LASTNAME']
  assert (1..md.columnCount).collect{ md.getColumnTypeName(it) } == ['INTEGER', 'VARCHAR', 'VARCHAR']
}

和上面例子有略微不同，这里查找列名称：

Also using row metadata

sql.eachRow("SELECT firstname AS first FROM Author WHERE firstname = 'Dierk'") { row ->
  def md = row.getMetaData()
  assert md.getColumnName(1) == 'FIRSTNAME'
  assert md.getColumnLabel(1) == 'FIRST'
}

Accessing metadata is quite common, so Groovy also provides variants to many of its methods that let you supply a closure that will be called once with the row metadata in addition to the normal row closure which is called for each row. The following example illustrates the two closure variant for eachRow:

Using row and metadata closures

def metaClosure = { meta -> assert meta.getColumnName(1) == 'FIRSTNAME' }
def rowClosure = { row -> assert row.FIRSTNAME == 'Dierk' }
sql.eachRow("SELECT firstname FROM Author WHERE firstname = 'Dierk'", metaClosure, rowClosure)

注意，我们的 SQL 这里只返回一条数据，可以使用 firstRow 达到同样的效果。

最后， JDBC 还提供了连接上的元数据（不仅仅在数据行上）：

Using connection metadata

def md = sql.connection.metaData
assert md.driverName == 'HSQL Database Engine Driver'
assert md.databaseProductVersion == '2.3.2'
assert ['JDBCMajorVersion', 'JDBCMinorVersion'].collect{ md[it] } == [4, 0]
assert md.stringFunctions.tokenize(',').contains('CONCAT')
def rs = md.getTables(null, null, 'AUTH%', null)
assert rs.next()
assert rs.getString('TABLE_NAME') == 'AUTHOR'

参考 JavaDoc 可以找到驱动上可以找到可以访问的元数据信息。

2.3.5. Named and named-ordinal parameters¶

Groovy 中提供了一些扩展替代占位符的语法变体。通常 GString 变量会优于其他的替代方案。在一些 JAVA 集成或使用模版场景，这些替代方案也是非常有用的。命名参数变量就像字符串加上参数变量列表，将 ? 占位符替换为一组参数名称，占位符的形式变为：:propName 或 ? placeholders ，其参数为 map，命名的参数，或领域对象。 map 和对象中其属性名称需要与占位符相对应。

Here is an example using the colon form:

Named parameters (colon form)

sql.execute "INSERT INTO Author (firstname, lastname) VALUES (:first, :last)", first: 'Dierk', last: 'Koenig'

And another example using the question mark form:

Named parameters (question mark form)

sql.execute "INSERT INTO Author (firstname, lastname) VALUES (?.first, ?.last)", first: 'Jon', last: 'Skeet'

If the information you need to supply is spread across multiple maps or domain objects you can use the question mark form with an additional ordinal index as shown here: 如果你的信息需要通过多个 map 或对象来传递，你可以使用问号标记形式，使用带有顺序的索引号：

Named-ordinal parameters

class Rockstar { String first, last }
def pogo = new Rockstar(first: 'Paul', last: 'McCartney')
def map = [lion: 'King']
sql.execute "INSERT INTO Author (firstname, lastname) VALUES (?1.first, ?2.lion)", pogo, map

2.3.6. 存储过程¶

在不同数据库之间, 存储过程或其函数的差别比较细微。在 HSQLDB 中，我们通过创建存储函数返回表中所有 authors 的缩写。

Creating a stored function

sql.execute """
  CREATE FUNCTION SELECT_AUTHOR_INITIALS()
  RETURNS TABLE (firstInitial VARCHAR(1), lastInitial VARCHAR(1))
  READS SQL DATA
  RETURN TABLE (
    SELECT LEFT(Author.firstname, 1) as firstInitial, LEFT(Author.lastname, 1) as lastInitial
    FROM Author
  )
"""

我们可以使用 Groovy’s 普通的查询方法来调用 CALL 语句。这里是 eachRow 例子：

Creating a stored procedure or function

def result = []
sql.eachRow('CALL SELECT_AUTHOR_INITIALS()') {
  result << "$it.firstInitial$it.lastInitial"
}
assert result == ['DK', 'JS', 'GL']

这下面的代码创建另一个存储函数，并使用 lastname 作为参数：

Creating a stored function with a parameter

sql.execute """
  CREATE FUNCTION FULL_NAME (p_lastname VARCHAR(64))
  RETURNS VARCHAR(100)
  READS SQL DATA
  BEGIN ATOMIC
    DECLARE ans VARCHAR(100);
    SELECT CONCAT(firstname, ' ', lastname) INTO ans
    FROM Author WHERE lastname = p_lastname;
    RETURN ans;
  END
"""

We can use the placeholder syntax to specify where the parameter belongs and note the special placeholder position to indicate the result:

Using a stored function with a parameter

def result = sql.firstRow("{? = call FULL_NAME(?)}", ['Koenig'])
assert result[0] == 'Dierk Koenig'

Finally, here is a stored procedure with input and output parameters:

Creating a stored procedure with input and output parameters

sql.execute """
  CREATE PROCEDURE CONCAT_NAME (OUT fullname VARCHAR(100),
    IN first VARCHAR(50), IN last VARCHAR(50))
  BEGIN ATOMIC
    SET fullname = CONCAT(first, ' ', last);
  END
"""

To use the CONCAT_NAME stored procedure parameter, we make use of a special call method. Any input parameters are simply provided as parameters to the method call. For output parameters, the resulting type must be specified as shown here:

Using a stored procedure with input and output parameters

sql.call("{call CONCAT_NAME(?, ?, ?)}", [Sql.VARCHAR, 'Dierk', 'Koenig']) {
  fullname -> assert fullname == 'Dierk Koenig'
}

2.4. Using DataSets¶

待续 (TBD)