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/58ba4de1 Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/58ba4de1 Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/58ba4de1 Branch: refs/heads/GROOVY_2_4_X Commit: 58ba4de16d21376fadc759c4f75b17fe38e0f072 Parents: 46db4bc Author: Paul King <pa...@asert.com.au> Authored: Sun Apr 19 21:26:19 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 | 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/58ba4de1/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/58ba4de1/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
