Groovy的SQL模塊提供了對(duì)JDBC的抽象翼抠，讓我們使用JDBC更簡(jiǎn)單苹丸，相關(guān)類在groovy.sql包下邀窃。本文參考自Working with a relational database挚瘟，一些代碼引用了官方文檔哄褒，需要了解詳細(xì)信息請(qǐng)參見原文绵载。
連接數(shù)據(jù)庫
和原文一樣埂陆，為了簡(jiǎn)單這里使用嵌入式數(shù)據(jù)庫HSQLDB，連接時(shí)在內(nèi)存中創(chuàng)建一個(gè)數(shù)據(jù)庫娃豹。數(shù)據(jù)庫驅(qū)動(dòng)可以使用Maven或Gradle導(dǎo)入焚虱，也可以使用Groovy自帶的Grape依賴管理器。不過在Intellij IDEA上懂版，下面的代碼有時(shí)候無法編譯鹃栽，說是找不到數(shù)據(jù)庫驅(qū)動(dòng)。這時(shí)候可以使用Gradle等工具管理依賴躯畴。
Groovy SQL的所有操作都在Sql類中民鼓，我們調(diào)用Sql的newInstance方法，傳遞URL蓬抄、用戶名丰嘉、密碼等參數(shù)即可連接到數(shù)據(jù)庫。這種方式需要自己手動(dòng)調(diào)用close方法關(guān)閉數(shù)據(jù)庫嚷缭。如果希望Groovy自動(dòng)關(guān)閉連接饮亏，可以使用withInstance方法耍贾，所有操作都在該方法的參數(shù)閉包中完成，之后會(huì)自動(dòng)關(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)建了一個(gè)數(shù)據(jù)表砰识。另外還有executeInsert和executeUpdate方法用于執(zhí)行插入和更新操作能扒。由于Groovy支持多行字符串，所以我們不用像Java那么費(fèi)勁仍翰。
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方法會(huì)返回一個(gè)列表观话，包含了插入數(shù)據(jù)對(duì)應(yīng)的所有主鍵予借。這兩個(gè)方法都支持?占位符和額外的參數(shù)列表，這時(shí)候Groovy會(huì)使用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方法瀑粥，會(huì)返回一個(gè)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也提供了幾個(gè)方便的方法來獲取數(shù)據(jù)三圆。eachRow方法接受一個(gè)閉包參數(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方法，它會(huì)返回GroovyRowResult對(duì)象路媚。我們可以使用該對(duì)象提供的方法獲取數(shù)據(jù)黄琼。
def first = sql.firstRow('SELECT lastname, firstname FROM Author')
Groovy還提供了rows方法，它會(huì)返回一個(gè)GroovyRowResult對(duì)象列表整慎。
List authors = sql.rows('SELECT firstname, lastname FROM Author')
assert authors.size() == 3
更新數(shù)據(jù)
更新數(shù)據(jù)也可以使用execute方法脏款，或者使用executeUpdate方法。executeUpdate方法會(huì)返回受影響的行數(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
}
高級(jí)SQL操作
事務(wù)管理
事務(wù)管理使用Sql的withTransaction方法，在閉包中執(zhí)行的語句會(huì)自動(dòng)包括在事務(wù)中拧揽。如果某個(gè)操作發(fā)生異常剃盾，整個(gè)事務(wù)就會(huì)回滾。下面的例子中，第二個(gè)SQL語句故意寫錯(cuò)了万俗，整個(gè)事務(wù)會(huì)回滾湾笛，我們可以看到事務(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方法提供了批處理功能闰歪。該方法的第一個(gè)參數(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)點(diǎn)是執(zhí)行語句的速度更快库倘，缺點(diǎn)是所有批處理都是同一類型的語句临扮。如果需要處理多個(gè)表，需要多個(gè)批處理語句教翩。
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ù)，最好的辦法就是定義一個(gè)元數(shù)據(jù)閉包配猫，然后傳給相關(guān)方法幅恋，Groovy會(huì)保證元數(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ū)動(dòng)名稱:${connection.driverName}")
println("數(shù)據(jù)庫版本號(hào):${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ù)和序號(hào)參數(shù)
SQL語句的參數(shù)不僅可以使用問號(hào)占位符，還可以使用命名參數(shù)和序號(hào)參數(shù)腐巢。命名參數(shù)很簡(jiǎn)單品追，Hibernate等很多其他框架都支持。
sql.execute "INSERT INTO Author (firstname, lastname) VALUES (:first, :last)", first: 'Dierk', last: 'Koenig'
還可以使用問號(hào)形式的命名參數(shù)系忙，和上面等價(jià)诵盼。
sql.execute "INSERT INTO Author (firstname, lastname) VALUES (?.first, ?.last)", first: 'Jon', last: 'Skeet'
如果傳遞的參數(shù)是Map或者對(duì)象形式的，還可以使用帶序號(hào)的命名參數(shù)形式银还。Groovy會(huì)自動(dòng)解析合適的參數(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
