关系型数据库操作

Groovy 的 groovy-sql 模块在 Java 的 JDBC 技术之上提供了一个更高层次的抽象。JDBC 本身提供了一个较低层次但相当全面的 API，它提供了对各种支持的关系型数据库系统的统一访问。我们将在示例中使用 HSQLDB，但你也可以选择使用 Oracle、SQL Server、MySQL 和其他许多数据库。groovy-sql 模块中最常用的类是 groovy.sql.Sql 类，它将 JDBC 抽象提升了一个层次。我们将首先介绍它。

1. 连接到数据库

使用 Groovy 的 Sql 类连接到数据库需要四部分信息

数据库统一资源定位符 (URL)
用户名
密码
驱动程序类名（在某些情况下可以自动推导）

对于我们的 HSQLDB 数据库，这些值将如下表所示

属性值

属性	值
url	`jdbc:hsqldb:mem:yourdb`
user	sa（或你的用户名）
password	yourPassword
driver	`org.hsqldb.jdbcDriver`

url

jdbc:hsqldb:mem:yourdb

user

sa（或你的用户名）

password

yourPassword

driver

org.hsqldb.jdbcDriver

请查阅你计划使用的 JDBC 驱动程序的文档，以确定适用于你的情况的正确值。

Sql 类有一个 newInstance 工厂方法，它接受这些参数。你通常会按如下方式使用它

连接到 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()

如果你不想自己处理资源（即手动调用 close()），那么你可以使用 withInstance 变体，如下所示

连接到 HSQLDB (withInstance 变体)

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

1.1. 使用 DataSource 连接

通常更倾向于使用 DataSource。你可能从连接池中获得了一个 DataSource。这里我们将使用 HSQLDB 驱动程序 jar 中提供的一个，如下所示

使用 DataSource 连接到 HSQLDB

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 ...

如果你有自己的连接池，详细信息将有所不同，例如对于 Apache Commons DBCP

使用 Apache Commons DBCP 通过 DataSource 连接到 HSQLDB

@Grab('org.apache.commons:commons-dbcp2:2.7.0')
import groovy.sql.Sql
import org.apache.commons.dbcp2.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 ...

1.2. 使用 @Grab 连接

前面的示例假设所需的数据库驱动程序 jar 已经在你的 classpath 上。对于一个独立的脚本，你可以在脚本顶部添加 @Grab 语句，以自动下载所需的 jar，如下所示

使用 @Grab 连接到 HSQLDB

<<<<<<< HEAD
        @Grab('org.hsqldb:hsqldb:2.7.2:jdk8')
=======
        @Grab('org.hsqldb:hsqldb:2.7.3')
>>>>>>> 35be169b6c (GROOVY-11418: Bump hsqldb to 2.7.3 (test dependency))
        @GrabConfig(systemClassLoader=true)
        // create, use, and then close sql instance ...

@GrabConfig 语句是必要的，以确保使用系统类加载器。这确保了驱动程序类和系统类（如 java.sql.DriverManager）位于同一个类加载器中。

2. 执行 SQL

你可以使用 execute() 方法执行任意 SQL 命令。我们来看看如何使用它来创建表。

2.1. 创建表

执行 SQL 的最简单方法是调用 execute() 方法，并将你希望执行的 SQL 作为字符串传递，如下所示

创建表

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

此方法有一个接受 GString 的变体，以及一个接受参数列表的变体。还有其他名称相似的变体：executeInsert 和 executeUpdate。我们将在本节的其他示例中看到这些变体的示例。

3. 基本 CRUD 操作

数据库的基本操作是创建、读取、更新和删除（即所谓的 CRUD 操作）。我们将依次检查这些操作。

3.1. 创建/插入数据

你可以使用我们前面看到的相同 execute() 语句，但通过使用 SQL 插入语句来插入行，如下所示

插入行

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

你可以使用特殊的 executeInsert 方法而不是 execute。这将返回所有生成的键的列表。execute 和 executeInsert 方法都允许你将 '?' 占位符放入 SQL 字符串中并提供参数列表。在这种情况下，将使用 PreparedStatement，这可以避免任何 SQL 注入的风险。以下示例说明了使用占位符和参数的 executeInsert

使用带占位符和参数的 executeInsert 插入行

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

此外，execute 和 executeInsert 方法都允许你使用 GStrings。SQL 中的任何 '$' 占位符都被认为是占位符。如果你想在 GString 中提供一个变量，而该变量不在 SQL 中正常占位符的位置，则存在转义机制。有关更多详细信息，请参阅 GroovyDoc。此外，当返回多个键而你只对其中一些键感兴趣时，executeInsert 允许你提供键列名称列表。这是一个说明键名称规范和 GStrings 的片段

使用带有 GString 并指定键名的 executeInsert 插入行

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]

3.2. 读取行

从数据库读取数据通过几种可用方法之一完成：query、eachRow、firstRow 和 rows。

如果你想遍历底层 JDBC API 返回的 ResultSet，请使用 query 方法，如下所示

使用 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"
  }
}

如果你想要一个稍微更高层次的抽象，它为 ResultSet 提供了一个 Groovy 友好的映射式抽象，请使用 eachRow 方法，如下所示

使用 eachRow 读取数据

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

请注意，访问数据行时可以使用 Groovy 列表样式和映射样式表示法。

如果你想获得与 eachRow 相似的功能但只返回一行数据，请使用 firstRow 方法，如下所示

使用 firstRow 读取数据

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

如果你想处理一个映射式数据结构列表，请使用 rows 方法，如下所示

使用 rows 读取数据

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

请注意，映射式抽象具有不区分大小写的键（因此我们可以使用 'FIRSTNAME' 或 'firstname' 作为键），并且在使用索引值（从右侧计算列号）时，负索引（Groovy 的标准功能）也有效。

你也可以使用上述任何方法返回标量值，尽管在这种情况下通常只需要 firstRow。以下示例显示了返回行数

读取标量值

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

3.3. 更新行

更新行同样可以使用 execute() 方法完成。只需将 SQL 更新语句作为方法的参数。你可以只插入一个姓氏的作者，然后更新该行以同时包含名字，如下所示

更新行

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

还有一个特殊的 executeUpdate 变体，它返回执行 SQL 后更新的行数。例如，你可以按如下方式更改作者的姓氏

使用 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'

3.4. 删除行

execute 方法也用于删除行，如下例所示

删除行

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

4. 高级 SQL 操作

4.1. 使用事务

在事务中执行数据库操作的最简单方法是将数据库操作包含在 withTransaction 闭包中，如下例所示

成功的事务

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

这里，数据库最初为空，操作成功完成后有两行。在事务范围之外，数据库从未被视为只有一行。

如果出现问题，withTransaction 块中任何早期的操作都将被回滚。我们可以在以下示例中看到这一点，其中我们使用数据库元数据（稍后将提供更多详细信息）查找 firstname 列的最大允许大小，然后尝试输入一个比最大值大一的名字，如下所示

失败的事务将导致回滚

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

即使第一个 SQL 执行最初成功，它也会被回滚，并且行数将保持不变。

4.2. 使用批处理

处理大量数据，尤其是在插入此类数据时，将数据分块处理效率更高。这可以通过使用 withBatch 语句来完成，如下例所示

批处理 SQL 语句

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 条新行。事实上，它们将以批处理方式添加，尽管事后你无法轻易判断。如果你想确认幕后发生了什么，可以在程序中添加一些额外的日志记录。在 withBatch 语句之前添加以下行

记录额外的 SQL 信息

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

开启此额外日志记录，并按照上述关于 logging.properties 文件的注释进行更改后，你将看到如下输出

启用批处理时的 SQL 日志输出

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 注入，我们鼓励你使用预处理语句，这可以通过接受 GStrings 或额外参数列表的方法变体来实现。预处理语句可以与批处理结合使用，如下例所示

批处理预处理语句

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

如果数据可能来自用户（例如通过脚本或网页表单），这提供了一个更安全的选择。当然，鉴于正在使用预处理语句，你仅限于对同一张表进行一批相同的 SQL 操作（在我们的示例中为插入）。

4.3. 执行分页

向用户展示大型数据表时，通常方便逐页展示信息。Groovy 的许多 SQL 检索方法都具有额外的参数，可用于选择特定的感兴趣页面。起始位置和页面大小指定为整数，如下例所示，使用 rows

检索数据页面

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']

4.4. 获取元数据

JDBC 元数据可以通过多种方式检索。也许最基本的方法是从任何行中提取元数据，如下例所示，该示例检查表名、列名和列类型名

使用行元数据

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']
}

前面示例的另一个稍微不同的变体，这次也查看列标签

也使用行元数据

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'
}

访问元数据非常常见，因此 Groovy 还提供了许多方法的变体，允许你提供一个闭包，该闭包将在正常行闭包（为每行调用）之外被调用一次，并提供行元数据。以下示例说明了 eachRow 的两种闭包变体

使用行和元数据闭包

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 还提供了每个连接的元数据（不仅仅是针对行）。你也可以从 Groovy 访问此类元数据，如下例所示

使用连接元数据

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

查阅你的驱动程序的 JavaDoc，以了解你可以访问哪些元数据信息。

4.5. 命名参数和命名序数参数

Groovy 支持一些额外的备用占位符语法变体。GString 变体通常比这些备用方案更受青睐，但这些备用方案对于 Java 集成目的以及有时在 GString 可能已被大量用作模板一部分的模板场景中很有用。命名参数变体与字符串加参数列表变体非常相似，但不是具有 ? 占位符列表后跟参数列表，而是具有一个或多个形式为 :propName 或 ?.propName 的占位符以及单个映射、命名参数或领域对象作为参数。映射或领域对象应该具有与每个提供的占位符相对应的名为 propName 的属性。

以下是使用冒号形式的示例

命名参数（冒号形式）

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

另一个使用问号形式的示例

命名参数（问号形式）

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

如果需要提供的信息分散在多个映射或领域对象中，你可以使用带额外序数索引的问号形式，如下所示

命名序数参数

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

4.6. 存储过程

创建存储过程或函数的精确语法在不同数据库之间略有差异。对于我们正在使用的 HSQLDB 数据库，我们可以创建一个存储函数，该函数返回表中所有作者的首字母，如下所示

创建存储函数

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

我们可以使用 SQL CALL 语句通过 Groovy 的普通 SQL 检索方法调用函数。这是一个使用 eachRow 的示例。

创建存储过程或函数

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

这是创建另一个存储函数的代码，这个函数以姓氏作为参数

创建带参数的存储函数

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

我们可以使用占位符语法来指定参数的位置，并注意表示结果的特殊占位符位置

使用带参数的存储函数

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

最后，这是一个带有输入和输出参数的存储过程

创建带输入和输出参数的存储过程

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

要使用 CONCAT_NAME 存储过程参数，我们使用特殊的 call 方法。任何输入参数都简单地作为参数提供给方法调用。对于输出参数，必须指定结果类型，如下所示

使用带输入和输出参数的存储过程

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

创建带输入/输出参数的存储过程

sql.execute """
  CREATE PROCEDURE CHECK_ID_POSITIVE_IN_OUT ( INOUT p_err VARCHAR(64), IN pparam INTEGER, OUT re VARCHAR(15))
  BEGIN ATOMIC
    IF pparam > 0 THEN
      set p_err = p_err || '_OK';
      set re = 'RET_OK';
    ELSE
      set p_err = p_err || '_ERROR';
      set re = 'RET_ERROR';
    END IF;
  END;
"""

使用带输入/输出参数的存储过程

def scall = "{call CHECK_ID_POSITIVE_IN_OUT(?, ?, ?)}"
sql.call scall, [Sql.inout(Sql.VARCHAR("MESSAGE")), 1, Sql.VARCHAR], {
  res, p_err -> assert res == 'MESSAGE_OK' && p_err == 'RET_OK'
}

5. 使用 DataSets

Groovy 提供了一个 groovy.sql.DataSet 类，它增强了 groovy.sql.Sql 类，提供了可以看作是迷你 ORM 功能。数据库的访问和查询使用 POGO 字段和运算符，而不是 JDBC 级 API 调用和 RDBMS 列名。

因此，不是像这样的查询

def qry = """SELECT * FROM Author
  WHERE (firstname > ?)
  AND (lastname < ?)
  ORDER BY lastname DESC"""
def params = ['Dierk', 'Pragt']
def result = sql.rows(qry, params)
assert result*.firstname == ['Eric', 'Guillaume', 'Paul']

你可以这样编写代码

def authorDS = sql.dataSet('Author')
def result = authorDS.findAll{ it.firstname > 'Dierk' }
        .findAll{ it.lastname < 'Pragt' }
        .sort{ it.lastname }
        .reverse()
assert result.rows()*.firstname == ['Eric', 'Guillaume', 'Paul']

这里我们有一个辅助“领域”类

class Author {
    String firstname
    String lastname
}

数据库访问和操作涉及创建或使用领域类的实例。