Groovy 的SQL模块

淡淡的烟草味﹌ 2022-09-26 15:55 136阅读 0赞

Groovy的SQL模块提供了对JDBC的抽象，让我们使用JDBC更简单，相关类在`groovy.sql`包下。本文参考自[Working with a relational database][]，一些代码引用了官方文档，需要了解详细信息请参见原文。

## 连接数据库 ##

和原文一样，为了简单这里使用嵌入式数据库HSQLDB，连接时在内存中创建一个数据库。数据库驱动可以使用Maven或Gradle导入，也可以使用Groovy自带的Grape依赖管理器。不过在Intellij IDEA上，下面的代码有时候无法编译，说是找不到数据库驱动。这时候可以使用Gradle等工具管理依赖。

Groovy SQL的所有操作都在Sql类中，我们调用Sql的newInstance方法，传递URL、用户名、密码等参数即可连接到数据库。这种方式需要自己手动调用close方法关闭数据库。如果希望Groovy自动关闭连接，可以使用withInstance方法，所有操作都在该方法的参数闭包中完成，之后会自动关闭连接。

@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
        }

如果使用数据源创建连接。可以直接将数据源传递给Sql的构造方法，即可由数据源创建连接。

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

## 创建数据表 ##

我们可以使用Sql的execute方法执行SQL语句。这里创建了一个数据表。另外还有executeInsert和executeUpdate方法用于执行插入和更新操作。由于Groovy支持多行字符串，所以我们不用像Java那么费劲。

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

## 增删查改 ##

### 插入数据 ###

插入数据可以使用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]
        }

### 查询数据 ###

如果喜欢传统方式，可以使用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

### 更新数据 ###

更新数据也可以使用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
        }

### 删除数据 ###

删除数据使用execute方法。

## 高级SQL操作 ##

### 事务管理 ###

事务管理使用Sql的withTransaction方法，在闭包中执行的语句会自动包括在事务中。如果某个操作发生异常，整个事务就会回滚。下面的例子中，第二个SQL语句故意写错了，整个事务会回滚，我们可以看到事务前后的数据数量是相同的。

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
        }

### 批处理 ###

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

### 分页 ###

Groovy提供了SQL数据库的分页功能。前面提到的很多方法都有分页的版本，我们只要传入起始索引和要获取的数据量，即可得到相应的数据。

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

### 元数据 ###

Groovy也能方便的获取数据库的元数据。要获取数据库元数据的话，调用Sql的`connection.metaData`属性即可。如果要获取结果的元数据，最好的办法就是定义一个元数据闭包，然后传给相关方法，Groovy会保证元数据闭包只调用一次。当然也可以直接在结果闭包中调用结果的`getMetaData()`方法获取元数据，不过这样这些代码可能随着结果的迭代重复执行多次。

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

### 命名参数和序号参数 ###

SQL语句的参数不仅可以使用问号占位符，还可以使用命名参数和序号参数。命名参数很简单，Hibernate等很多其他框架都支持。

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或者对象形式的，还可以使用带序号的命名参数形式。Groovy会自动解析合适的参数。

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

[Working with a relational database]: http://www.groovy-lang.org/databases.html