使用关系型数据库

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，如下所示

使用 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 已经位于您的类路径上。对于自包含的脚本，您可以在脚本顶部添加 @Grab 语句来自动下载必要的 jar，如下所示

使用 @Grab 连接到 HSQLDB

@Grab('org.hsqldb:hsqldb:2.7.1:jdk8')
@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 方法都允许您使用 GString。SQL 中的任何 '$' 占位符都被认为是占位符。如果要在一个位置提供一个包含变量的 GString 部分，而该位置不是 SQL 中正常占位符所在的位置，则存在一种转义机制。有关更多详细信息，请参阅 GroovyDoc。此外，executeInsert 允许您提供一个键列名列表，当返回多个键时，而您只对其中一些键感兴趣时。以下是一个说明键名称指定和 GString 的片段

使用 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 友好的类似于 map 的抽象，请使用 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 列表风格和 map 风格的符号。

如果您需要类似于 eachRow 的功能，但只返回一行数据，请使用 firstRow 方法，如下所示

使用 firstRow 读取数据

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

如果您想处理一个类似于 map 的数据结构列表，请使用 rows 方法，如下所示

使用 rows 读取数据

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

请注意，类似于 map 的抽象具有不区分大小写的键（因此我们可以使用 '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 注入，我们鼓励您使用预备语句，这是通过使用接受 GString 或额外参数列表的方法变体来实现的。预备语句可以与批处理一起使用，如下面的示例所示

批处理预备语句

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 操作（在本例中为插入）的批处理。

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.1'
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') == 'AUTHOR'

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

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

Groovy 支持一些额外的替代占位符语法变体。GString 变体通常优于这些替代方案，但替代方案对于 Java 集成目的以及有时在模板场景中（GString 可能已被大量使用作为模板的一部分）很有用。命名参数变体非常类似于 String 加参数列表的变体，但不是使用 `?` 占位符列表后跟参数列表，而是使用一个或多个占位符，其形式为 `: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. 使用数据集

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
}

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