Groovy的SQL模块

Groovy的SQL模块提供了对JDBC的抽象，让我们使用JDBC更简单，相关类在groovy.sql包下。

1.插入数据

我们可以使用execute或executeInsert方法，主要的区别是executeInsert方法会返回一个列表，包含了播放数据对应的主键。这两种方法都支持？点位符和额外的参数列表，这时Groovy会使用PreparedStatement来执行SQL。它们都支持GString和内插字符串。

static void insertRow(Sql sql) {
        println('插入数据')
        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]
    }

2.查询数据

如果喜欢传统方式，可以使用query方法，会返回一个JDBC结果集可供查询。

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也提供了几个方便的方法来获取数据。eachRow方法接受一个闭包参数，在闭包中，我们可以使用索引或成员访问符来获取每行的结果。

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

如果结果只有一行，还可以使用firstRow方法，它会返回GroovyRowResult对象。我们可以使用该对象提供的方法获取数据。

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

3.更新数据

更新数据也可以使用execute方法或者使用executeUpdate方法，executeUpdate方法会返回受影响的行数。

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
    }

4.删除数据

删除数据使用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
    }

5.事物管理

withTransaction方法，在闭包中执行的语句会自动包括在事务中。

static void sqlTransaction(Sql sql) {
        println '事务管理'
        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
    }

6.批处理

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

还可以使用预处理语句进行批处理，使用预处理的优点是执行语句的速度更快，缺点是所有批处理都是同一类型的语句。

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

7.分页

只需要传入起始索引和要获取的数据量，即可得到相应的数据。

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

8.元数据

要获取数据库元数据，调用sql的connection.metaData属性即可。

如果要获取结果的元数据，最好的办法就是定义一个元数据闭包，然后传给相关方法，Groovy会保证元数据闭包只调用一次。

static void sqlMetaData(Sql sql) {
        println '元数据'
        def connection = sql.connection.metaData
        println("数据库驱动名称:${connection.driverName}")
        println("数据库版本号:${connection.databaseMajorVersion}.${connection.databaseMinorVersion}")
        println("数据库产品名:${connection.databaseProductName}")

        println('结果元数据')
        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)
    }

9.命名参数和序号参数

SQL语句的参数不仅可以使用问号占位符，还可以使用命名参数序号参数。

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或者对象形式，还可以使用带序号的命名参数形式。

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