groovy-sql: doco for transactions
Project: http://git-wip-us.apache.org/repos/asf/incubator-groovy/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-groovy/commit/46db4bc8 Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/46db4bc8 Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/46db4bc8 Branch: refs/heads/GROOVY_2_4_X Commit: 46db4bc807a7fcb39223fbb40fecc301c99961bb Parents: fb1eeb5 Author: Paul King <pa...@asert.com.au> Authored: Sat Apr 18 21:41:10 2015 +1000 Committer: Paul King <pa...@asert.com.au> Committed: Mon Apr 20 17:23:31 2015 +1000 ---------------------------------------------------------------------- .../groovy-sql/src/spec/doc/sql-userguide.adoc | 23 +++++++++- .../groovy-sql/src/spec/test/SqlTest.groovy | 46 ++++++++++++++++++++ 2 files changed, 68 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/46db4bc8/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc ---------------------------------------------------------------------- diff --git a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc index ba2f11d..b78d5d1 100644 --- a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc +++ b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc @@ -224,7 +224,28 @@ include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[ta === Working with transactions -(TBD) +The easiest way to perform database operations within a transaction is to include the database operation within a `withTransaction` closure as shown in the following example: + +[source,groovy] +.A successful transaction +---- +include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_transaction_pass,indent=0] +---- + +Here the database starts empty and has two rows after successful completion of the operation. Outside the scope of the +transaction, the database is never seen as having just one row. + +If something goes wrong, any earlier operations within the `withTransaction` block are rolled back. +We can see that in operation in the following example where we use database metadata (more details coming up shortly) to find the +maximum allowable size of the `firstname` column and then attempt to enter a firstname one larger than that maximum value as shown here: + +[source,groovy] +.A failed transaction will cause a rollback +---- +include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_transaction_fail,indent=0] +---- + +Even though the first sql execute succeeds initially, it will be rolled back and the number of rows will remain the same. === Using batches http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/46db4bc8/subprojects/groovy-sql/src/spec/test/SqlTest.groovy ---------------------------------------------------------------------- diff --git a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy index 21d3959..070e2ac 100644 --- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy +++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy @@ -382,6 +382,52 @@ class SqlTest extends GroovyTestCase { ''' } + void testTransactions() { + assertScript ''' + import groovy.sql.Sql + + def url = 'jdbc:hsqldb:mem:yourDB' + def user = 'sa' + def password = '' + def driver = 'org.hsqldb.jdbcDriver' + Sql.withInstance(url, user, password, driver) { sql -> + sql.execute """ + DROP TABLE Author IF EXISTS + """ + sql.execute """ + CREATE TABLE Author ( + id INTEGER GENERATED BY DEFAULT AS IDENTITY, + firstname VARCHAR(64), + lastname VARCHAR(64) + ) + """ + // tag::sql_transaction_pass[] + 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 + // end::sql_transaction_pass[] + // tag::sql_transaction_fail[] + 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 + // end::sql_transaction_fail[] + } + ''' + } + void testDeletingRows() { assertScript ''' import groovy.sql.Sql
