Groovy代码示例 – 数据库高级操作

数据库操作之事务处理

最简单的实现数据库事务的方式是使用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

上面的例子中尽管第一条语句执行成功，但是由于后续的操作失败导致整个闭包的操作被回滚，因此行数据没有变化。

数据库操作之批量处理

当需要处理大量数据，尤其是插入数据的时候，将这些操作组合成批量来处理效率会更高。groovy中可以使用withBatch方法来实现批量操作。

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条数据到数据库。实际上，数据已经被加入批量，虽然我们不知道批量是如何插入的。如果你想知道批量插入的细节，你可以添加运行日志。在执行批量之前加上以下代码：

import java.util.logging.*

//下一行开始详细的日志记录
Logger.getLogger('groovy.sql').level = Level.FINE
// 还需要调整JRE_HOME/lib目录下logging.properties的下述配置
// java.util.logging.ConsoleHandler.level = FINE
开启了日志以及调整了logging.properties配置以后，将会看到以下日志输出：

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

注意：

1）任何的SQL语句组合都可以加到批量中，并不需要是插入同张表。

2）为避免SQL注入，强烈建议使用预编译语句prepared statment，可以通过witchBatch同态方法，使用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的作用于单个表。

数据库操作之分页处理

当需要向用户展现一个数据量较大的库表时，通过分页展示是比较合理和方便的。groovy中许多查询方法都可以指定分页参数。以下代码通过指定起始位置和页面大小确定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']

数据库操作之获取库表元数据

JDBC元数据可以通过多种方式获取。最常用的方式可能是通过行数据row获取表名、列名、列类型，如下所示：

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提供了多态方法指定元数据闭包，该闭包只执行一次用于获取元数据，同时指定行数据闭包用于每行数据的处理。以下是两个闭包的使用示例：

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)

注意：获取元数据只需要一行数据即可，因此上面的例子使用firstRow也可以。

最后，JDBC可以从连接connection中获取连接相关元数据。你可以通过下面的方式获取连接元数据：

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

具体还请参考javadoc看看能从元数据中能够获取到的信息。

数据库操作之命名参数和名称序号参数

Groovy支持SQL占位符的几种扩展方式。很多时候GString的扩展对我们来说已经足够，但是当需要与java集成或者觉得GString不够轻量级的时候，可以使用这些占位符扩展。命名参数跟SQL语句+参数列表的方式类似，不同的是，占位符不是一串的?字符。你可以使用:propName或者?.propName的形式占位符，以及使用map对象、命名参数、普通对象作为参数。map对象和普通对象必须有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'

如果参数信息跨越多个map对象或者域对象(domain object)的时候，可以使用?开头形式的占位符配合参数顺序数，如下例所示：

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

数据库操作之存储过程调用

不同的数据库创建存储过程或函数的方式略有不同。对于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语句执行函数获取数据

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

下面的代码创建另外一个函数，该函数接受一个lastname作为参数。

创建带有参数的函数：

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存放存储过程参数，我们使用一个比较特别的调用方式。任何的输入参数都被看做是存储过程的调用参数。对于输出参数，可以像下面的方式输出：

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