Quantcast

incubator-groovy git commit: groovy-sql: doco for transactions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

incubator-groovy git commit: groovy-sql: doco for transactions

paulk
Repository: incubator-groovy
Updated Branches:
  refs/heads/master cba36623f -> f8e75b74e


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/f8e75b74
Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/f8e75b74
Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/f8e75b74

Branch: refs/heads/master
Commit: f8e75b74ea6c68a085b8c5a4888760cd4155b7b2
Parents: cba3662
Author: Paul King <[hidden email]>
Authored: Sat Apr 18 21:41:10 2015 +1000
Committer: Paul King <[hidden email]>
Committed: Sat Apr 18 21:50:17 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/f8e75b74/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/f8e75b74/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


Loading...