使用關聯式資料庫

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. 使用資料來源連線

通常偏好使用資料來源。您可能從連線池中取得一個。在這裡，我們將使用 HSQLDB 驅動程式 jar 中提供的一個，如下所示

使用資料來源連線到 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 使用資料來源連線到 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 中的任何「$」佔位符都假設為佔位符。如果您想要在 SQL 中非正常佔位符的位置提供 GString 的一部分，則存在一個跳脫機制。有關更多詳細資訊，請參閱 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 友善地圖式抽象，請使用 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」作為金鑰），而且在使用索引值時（從右邊計算欄位編號），-ve 索引（標準 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 欄位允許的最大大小，然後嘗試輸入比最大值大一個的 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')
}

如果資料可能來自使用者，例如透過指令碼或網路表單，這會提供更安全的選項。當然，由於使用已準備好的陳述式，您僅限於對一個表格執行相同 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 可能已作為範本的一部分大量使用的情況下，在範本場景中也很有用。命名參數變體很像字串加上參數變體的清單，但不是有一系列 ? 佔位符後接一系列參數，而是有一個或多個具有 :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
}

資料庫存取和操作涉及建立或使用網域類別的執行個體。