Groovy Sql数据库高级操作(事务、批量执行、分页查询)

 

分类:JVM数据库groovy
标签:数据库groovy语法groovy SqlwithTransaction事务sql语句批量执行sqlwithBatchgroovy分页查询预编译语句

2019-10-03 16:38:50.0 阅读原文

Groovy Sql 事务withTransaction

使用 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

sql.withTransaction的参数是一个闭包,闭包中的sql操作就是在事务中进行的,如果有一个操作失败,则所有的操作都会回滚。

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

sql.withBatch 有两个参数,第二个参数为要执行操作的闭包,第一个整形的参数,表示每次的执行量。例如代码中的3,就表示调用3次addBatch方法之后,就会执行一次插入操作。

Groovy 批量执行预编译Sql

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分页查询rows

Groovy分页查询也比较简单,如下:

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']

Groovy Sql的基本操作请参考:

http://jvm123.com/2019/10/groovy-sql.html


分类:JVM数据库groovy
标签:数据库groovy语法groovy SqlwithTransaction事务sql语句批量执行sqlwithBatchgroovy分页查询预编译语句