Groovy的SQL模塊提供了對JDBC的抽象，讓我們使用JDBC更簡單，相關(guān)類在groovy.sql包下。本文參考自Working with a relational database，一些代碼引用了官方文檔，需要了解詳細(xì)信息請參見原文。

連接數(shù)據(jù)庫

和原文一樣，為了簡單這里使用嵌入式數(shù)據(jù)庫HSQLDB，連接時在內(nèi)存中創(chuàng)建一個數(shù)據(jù)庫。數(shù)據(jù)庫驅(qū)動可以使用Maven或Gradle導(dǎo)入，也可以使用Groovy自帶的Grape依賴管理器。不過在Intellij IDEA上，下面的代碼有時候無法編譯，說是找不到數(shù)據(jù)庫驅(qū)動。這時候可以使用Gradle等工具管理依賴。

Groovy SQL的所有操作都在Sql類中，我們調(diào)用Sql的newInstance方法，傳遞URL、用戶名、密碼等參數(shù)即可連接到數(shù)據(jù)庫。這種方式需要自己手動調(diào)用close方法關(guān)閉數(shù)據(jù)庫。如果希望Groovy自動關(guān)閉連接，可以使用withInstance方法，所有操作都在該方法的參數(shù)閉包中完成，之后會自動關(guān)閉連接。

@GrabResolver(name = 'aliyun', root = 'http://maven.aliyun.com/nexus/content/groups/public/')
@GrabConfig(systemClassLoader = true)
@Grab(group = 'org.hsqldb', module = 'hsqldb', version = '2.3.4')

class SqlDatabase {
    static void main(String[] args) {
        def sql = setUpDatabase()
    }

    static Sql setUpDatabase() {
        def url = 'jdbc:hsqldb:mem:test'
        def user = 'sa'
        def password = ''
        def driver = 'org.hsqldb.jdbcDriver'
        def sql = Sql.newInstance(url, user, password, driver)
        return sql
    }

如果使用數(shù)據(jù)源創(chuàng)建連接。可以直接將數(shù)據(jù)源傳遞給Sql的構(gòu)造方法，即可由數(shù)據(jù)源創(chuàng)建連接。

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

創(chuàng)建數(shù)據(jù)表

我們可以使用Sql的execute方法執(zhí)行SQL語句。這里創(chuàng)建了一個數(shù)據(jù)表。另外還有executeInsert和executeUpdate方法用于執(zhí)行插入和更新操作。由于Groovy支持多行字符串，所以我們不用像Java那么費勁。

    static void setUpTables(Sql sql) {
        println('準(zhǔn)備表')
        sql.execute('''
  CREATE TABLE author (
    id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    firstname   VARCHAR(64),
    lastname    VARCHAR(64)
  );
''')
        println('準(zhǔn)備表完成')
    }

增刪查改

插入數(shù)據(jù)

插入數(shù)據(jù)可以使用execute或executeInsert方法。它們的主要區(qū)別是executeInsert方法會返回一個列表，包含了插入數(shù)據(jù)對應(yīng)的所有主鍵。這兩個方法都支持?占位符和額外的參數(shù)列表，這時候Groovy會使用PreparedStatement來執(zhí)行SQL。它們也都支持GString和內(nèi)插字符串。

下面的例子使用了占位符和參數(shù)列表執(zhí)行插入操作。

    static void insertRow(Sql sql) {
        println('插入數(shù)據(jù)')
        String stmt = 'insert into author(firstname,lastname) values(?,?)'
        def params = ['Yi', 'Tian']
        def id = sql.executeInsert(stmt, params)
        assert id[0] == [0]
        params = ['Zhang', 'San']
        id = sql.executeInsert(stmt, params)
        assert id[0] == [1]
    }

查詢數(shù)據(jù)

如果喜歡傳統(tǒng)方式，可以使用query方法，會返回一個JDBC結(jié)果集可供查詢。

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也提供了幾個方便的方法來獲取數(shù)據(jù)。eachRow方法接受一個閉包參數(shù)，在閉包中，我們可以使用索引或成員訪問符來獲取每行的結(jié)果。

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

如果結(jié)果只有一行，還可以使用firstRow方法，它會返回GroovyRowResult對象。我們可以使用該對象提供的方法獲取數(shù)據(jù)。

def first = sql.firstRow('SELECT lastname, firstname FROM Author')

Groovy還提供了rows方法，它會返回一個GroovyRowResult對象列表。

List authors = sql.rows('SELECT firstname, lastname FROM Author')
assert authors.size() == 3

更新數(shù)據(jù)

更新數(shù)據(jù)也可以使用execute方法，或者使用executeUpdate方法。executeUpdate方法會返回受影響的行數(shù)。

    static void updateAuthor(Sql sql) {
        def stmt = 'update author set firstname=?,lastname=? where id=?'
        def row = sql.executeUpdate(stmt, ['li', '4', 1])
        assert row == 1
    }

刪除數(shù)據(jù)

刪除數(shù)據(jù)使用execute方法。

    static void updateAuthor(Sql sql) {
        def stmt = 'update author set firstname=?,lastname=? where id=?'
        def row = sql.executeUpdate(stmt, ['li', '4', 1])
        assert row == 1
    }

高級SQL操作

事務(wù)管理

事務(wù)管理使用Sql的withTransaction方法，在閉包中執(zhí)行的語句會自動包括在事務(wù)中。如果某個操作發(fā)生異常，整個事務(wù)就會回滾。下面的例子中，第二個SQL語句故意寫錯了，整個事務(wù)會回滾，我們可以看到事務(wù)前后的數(shù)據(jù)數(shù)量是相同的。

    static void sqlTransaction(Sql sql) {
        println '事務(wù)管理'
        def rowsBefore = sql.firstRow('SELECT count(*) AS num FROM author').num
        try {
            sql.withTransaction {
                //正確語句
                sql.executeInsert("INSERT INTO author(firstname,lastname) VALUES('wang','5')")
                sql.executeInsert("INSERT INTO author() VALUES(4324,3423)")
            }
        } catch (ignore) {
            println(ignore.message)
        }
        def rowsAfter = sql.firstRow('SELECT count(*) AS num FROM author').num
        assert rowsBefore == rowsAfter
    }

批處理

Sql的withBatch方法提供了批處理功能。該方法的第一個參數(shù)是一次性批處理的數(shù)量。

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

還可以使用預(yù)處理語句進(jìn)行批處理。使用預(yù)處理的優(yōu)點是執(zhí)行語句的速度更快，缺點是所有批處理都是同一類型的語句。如果需要處理多個表，需要多個批處理語句。

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

分頁

Groovy提供了SQL數(shù)據(jù)庫的分頁功能。前面提到的很多方法都有分頁的版本，我們只要傳入起始索引和要獲取的數(shù)據(jù)量，即可得到相應(yīng)的數(shù)據(jù)。

    static void sqlPagination(Sql sql) {
        println('分頁')
        def stmt = 'select id,firstname,lastname from author'
        sql.rows(stmt, 1, 3).each { printRow(it) }
        println('----------------')
        sql.rows(stmt, 4, 3).each { printRow(it) }
        println('----------------')
        sql.rows(stmt, 7, 3).each { printRow(it) }
        println('----------------')
    }

元數(shù)據(jù)

Groovy也能方便的獲取數(shù)據(jù)庫的元數(shù)據(jù)。要獲取數(shù)據(jù)庫元數(shù)據(jù)的話，調(diào)用Sql的connection.metaData屬性即可。如果要獲取結(jié)果的元數(shù)據(jù)，最好的辦法就是定義一個元數(shù)據(jù)閉包，然后傳給相關(guān)方法，Groovy會保證元數(shù)據(jù)閉包只調(diào)用一次。當(dāng)然也可以直接在結(jié)果閉包中調(diào)用結(jié)果的getMetaData()方法獲取元數(shù)據(jù)，不過這樣這些代碼可能隨著結(jié)果的迭代重復(fù)執(zhí)行多次。

    static void sqlMetaData(Sql sql) {
        println '元數(shù)據(jù)'
        def connection = sql.connection.metaData
        println("數(shù)據(jù)庫驅(qū)動名稱:${connection.driverName}")
        println("數(shù)據(jù)庫版本號:${connection.databaseMajorVersion}.${connection.databaseMinorVersion}")
        println("數(shù)據(jù)庫產(chǎn)品名:${connection.databaseProductName}")

        println('結(jié)果元數(shù)據(jù)')
        def metaClosure = { meta ->
            println("${meta.getColumnName(1)}\t${meta.getColumnName(2)}\t${meta.getColumnName(3)}")
        }
        def rowClosure = { row ->
            println "[id=${row[0]},first=${row[1]},last=${row[2]}"
        }
        sql.eachRow('SELECT id,firstname,lastname FROM author', metaClosure, rowClosure)
    }

命名參數(shù)和序號參數(shù)

SQL語句的參數(shù)不僅可以使用問號占位符，還可以使用命名參數(shù)和序號參數(shù)。命名參數(shù)很簡單，Hibernate等很多其他框架都支持。

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

還可以使用問號形式的命名參數(shù)，和上面等價。

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

如果傳遞的參數(shù)是Map或者對象形式的，還可以使用帶序號的命名參數(shù)形式。Groovy會自動解析合適的參數(shù)。

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