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

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

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

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


groovy-sql: doco for batching


Project: http://git-wip-us.apache.org/repos/asf/incubator-groovy/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-groovy/commit/99e70552
Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/99e70552
Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/99e70552

Branch: refs/heads/master
Commit: 99e705526e2c0b07a1e5188404a2de01b9fd498a
Parents: f8e75b7
Author: Paul King <[hidden email]>
Authored: Sun Apr 19 21:26:19 2015 +1000
Committer: Paul King <[hidden email]>
Committed: Sun Apr 19 21:26:19 2015 +1000

----------------------------------------------------------------------
 .../groovy-sql/src/spec/doc/sql-userguide.adoc  | 46 +++++++++++-
 .../groovy-sql/src/spec/test/SqlTest.groovy     | 74 ++++++++++++++++++++
 2 files changed, 119 insertions(+), 1 deletion(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/99e70552/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 b78d5d1..d0e4ff0 100644
--- a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
+++ b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
@@ -249,7 +249,51 @@ Even though the first sql execute succeeds initially, it will be rolled back and
 
 === Using batches
 
-(TBD)
+When dealing with large volumes of data, it can be more efficient to chunk the data into batches. This is done
+using the `withBatch` statement as shown in the following example:
+
+[source,groovy]
+.Batching SQL statements
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_batch_statements,indent=0]
+----
+
+After executing these statements, there will be 7 new rows in the database. In fact, they will have been added in batches
+even though you can't easily tell that after that fact. If you want to confirm what is going on under the covers, you can
+add a little bit of extra logging into your program. Add the following lines before the `withBatch` statement:
+
+[source,groovy]
+.Logging additional SQL information
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_batch_import_for_logging,indent=0]
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_batch_logging,indent=0]
+----
+
+With this extra logging turned on, and the changes made as per the above comment for the logging.properties file, you should see
+output such as:
+
+[source]
+.SQL logging output with batching enable
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_batch_results,indent=0]
+----
+
+We should also note, that any combination of SQL statements can be added to the batch. They don't all have to be
+inserting a new row to the same table.
+
+We noted earlier that to avoid SQL injection, we encourage you to use prepared statements, this is achieved using the
+variants of methods which take GStrings or a list of extra parameters. Prepared statements can be used in combination
+with batches as shown in the following example:
+
+[source,groovy]
+.Batching prepared statements
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_batch_prepared_statements,indent=0]
+----
+
+This provides a much safer option if the data could come from a user such as via a script or a web form. Of course, given
+that a prepared statement is being used, you are limited to a batch of the same SQL operation (insert in our example)
+to the one table.
 
 === Performing pagination
 

http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/99e70552/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 070e2ac..f5b5c9b 100644
--- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
+++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
@@ -428,6 +428,80 @@ class SqlTest extends GroovyTestCase {
         '''
     }
 
+    void testBatching() {
+        assertScript '''
+            import groovy.sql.Sql
+            // tag::sql_batch_import_for_logging[]
+            import java.util.logging.*
+
+            // end::sql_batch_import_for_logging[]
+
+            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_batch_logging[]
+              // next line will add fine logging
+              Logger.getLogger('groovy.sql').level = Level.FINE
+              // also adjust logging.properties file in JRE_HOME/lib to have:
+              // java.util.logging.ConsoleHandler.level = FINE
+              // end::sql_batch_logging[]
+
+              // tag::sql_batch_statements[]
+              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')"
+              }
+              // end::sql_batch_statements[]
+              assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 7
+              /*
+              // tag::sql_batch_results[]
+              FINE: Successfully executed batch with 3 command(s)
+              Apr 19, 2015 8:38:42 PM groovy.sql.BatchingStatementWrapper processResult
+
+              FINE: Successfully executed batch with 3 command(s)
+              Apr 19, 2015 8:38:42 PM groovy.sql.BatchingStatementWrapper processResult
+
+              FINE: Successfully executed batch with 1 command(s)
+              Apr 19, 2015 8:38:42 PM groovy.sql.Sql getStatement
+              // end::sql_batch_results[]
+              */
+              sql.execute('DELETE FROM Author')
+              assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 0
+              // tag::sql_batch_prepared_statements[]
+              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')
+              }
+              // end::sql_batch_prepared_statements[]
+              assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 7
+            }
+        '''
+    }
+
     void testDeletingRows() {
         assertScript '''
         import groovy.sql.Sql