用法1:
?
1
2
3
4
5
6
7
sql.withBatch { stmt ->
          mymap.each { k,v ->
              stmt.addBatch("""UPDATE some_table
                                  SET some_column = '${v}'
                                WHERE id = ${k} """)
          }
}
用法2:
?
1
2
3
4
5
6
7
8
sql.withBatch(20, """UPDATE some_table
                        SET some_column = ?
                      WHERE id = ? """) { ps ->  
               
          mymap.each { k,v ->
              ps.addBatch(v, k)
          }
}

You can find more options on how use batching with prepared statements in Groovy 1.8.1 docs.

API DOC:

 

public int[] withBatch(Closure closure)

Performs the closure (containing batch operations) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.

This means that executeBatch() will be called automatically after the withBatch closure has finished but may be called explicitly if desired as well for more fine-grained partitioning of the batch.

The closure will be called with a single argument; the database statement (actually a BatchingStatementWrapper helper object) associated with this batch.

Use it like this:

 def updateCounts = sql.withBatch { stmt ->
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     ...
 }
For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
 sql.withTransaction {
     def result1 = sql.withBatch { ... }
     ...
 }
throws:
SQLException if a database access error occurs, or this method is called on a closed Statement, or the driver does not support batch statements. Throws BatchUpdateException (a subclass ofSQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
Parameters:
closure - the closure containing batch and optionally other statements
Returns:
an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
 

public int[] withBatch(int batchSize, Closure closure)

Performs the closure (containing batch operations) within a batch using a given batch size.

After every batchSize addBatch(sqlBatchOperation) operations, automatically calls an executeBatch() operation to "chunk" up the database operations into partitions. Though not normally needed, you can also explicitly call executeBatch() which after executing the current batch, resets the batch count back to zero.

The closure will be called with a single argument; the database statement (actually a BatchingStatementWrapper helper object) associated with this batch.

Use it like this for batchSize of 20:

 def updateCounts = sql.withBatch(20) { stmt ->
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     ...
 }
For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
 sql.withTransaction {
     def result1 = sql.withBatch { ... }
     ...
 }
throws:
SQLException if a database access error occurs, or this method is called on a closed Statement, or the driver does not support batch statements. Throws BatchUpdateException (a subclass ofSQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
Parameters:
batchSize - partition the batch into batchSize pieces, i.e. after batchSize addBatch() invocations, call executeBatch() automatically; 0 means manual calls to executeBatch are required
closure - the closure containing batch and optionally other statements
Returns:
an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
 

public int[] withBatch(String sql, Closure closure)

Performs the closure (containing batch operations specific to an associated prepared statement) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.

This means that executeBatch() will be called automatically after the withBatch closure has finished but may be called explicitly if desired as well for more fine-grained partitioning of the batch.

The closure will be called with a single argument; the prepared statement (actually a BatchingPreparedStatementWrapper helper object) associated with this batch.

An example:

 def updateCounts = sql.withBatch('insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps ->
     ps.addBatch([10, 12, 5])
     ps.addBatch([7, 3, 98])
     ps.addBatch(22, 67, 11)
     def partialUpdateCounts = ps.executeBatch() // optional interim batching
     ps.addBatch(30, 40, 50)
     ...
 }
For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
 sql.withTransaction {
     def result1 = sql.withBatch { ... }
     ...
 }
throws:
SQLException if a database access error occurs, or this method is called on a closed Statement, or the driver does not support batch statements. Throws BatchUpdateException (a subclass ofSQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
Parameters:
sql - batch update statement
closure - the closure containing batch statements (to bind parameters) and optionally other statements
Returns:
an array of update counts containing one element for each binding in the batch. The elements of the array are ordered according to the order in which commands were executed.
 

public int[] withBatch(int batchSize, String sql, Closure closure)

Performs the closure (containing batch operations specific to an associated prepared statement) within a batch using a given batch size.

After every batchSize addBatch(params) operations, automatically calls an executeBatch() operation to "chunk" up the database operations into partitions. Though not normally needed, you can also explicitly call executeBatch() which after executing the current batch, resets the batch count back to zero.

The closure will be called with a single argument; the prepared statement (actually a BatchingPreparedStatementWrapper helper object) associated with this batch.

Below is an example using a batchSize of 20:

 def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps ->
     ps.addBatch(10, 12, 5)      // varargs style
     ps.addBatch([7, 3, 98])     // list
     ps.addBatch([22, 67, 11])
     ...
 }
Named parameters (into maps or domain objects) are also supported:
 def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (:foo, :bar, :baz)') { ps ->
     ps.addBatch([foo:10, bar:12, baz:5])  // map
     ps.addBatch(foo:7, bar:3, baz:98)     // Groovy named args allow outer brackets to be dropped
     ...
 }
Named ordinal parameters (into maps or domain objects) are also supported:
 def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?1.foo, ?2.bar, ?2.baz)') { ps ->
     ps.addBatch([[foo:22], [bar:67, baz:11]])  // list of maps or domain objects
     ps.addBatch([foo:10], [bar:12, baz:5])     // varargs allows outer brackets to be dropped
     ps.addBatch([foo:7], [bar:3, baz:98])
     ...
 }
 // swap to batch size of 5 and illustrate simple and domain object cases ...
 class Person { String first, last }
 def updateCounts2 = sql.withBatch(5, 'insert into PERSON(id, first, last) values (?1, ?2.first, ?2.last)') { ps ->
     ps.addBatch(1, new Person(first:'Peter', last:'Pan'))
     ps.addBatch(2, new Person(first:'Snow', last:'White'))
     ...
 }
For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
 sql.withTransaction {
     def result1 = sql.withBatch { ... }
     ...
 }
throws:
SQLException if a database access error occurs, or this method is called on a closed Statement, or the driver does not support batch statements. Throws BatchUpdateException (a subclass ofSQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
Parameters:
batchSize - partition the batch into batchSize pieces, i.e. after batchSize addBatch() invocations, call executeBatch() automatically; 0 means manual calls to executeBatch are required if additional partitioning of the batch is required
sql - batch update statement
closure - the closure containing batch statements (to bind parameters) and optionally other statements
Returns:
an array of update counts containing one element for each binding in the batch. The elements of the array are ordered according to the order in which commands were executed.

MySql支持Batch Sql的参数设定:

MySQL does have batch processing support though, it just requires different SQL.

The MySQL JDBC driver does support this, but requires the rewriteBatchedStatements=true JDBC connect property to be set.

This can easily be set by modifying your connect URL, such as;

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true