Batch insert using groovy Sql? discusses how to execute multiple prepared statements in a batch. But all the statements must have the same structure (passed in as a top-level argument to withBatch).
Is there a way to batch up heterogeneous prepared statements like:
sql.withBatch {ps ->
ps.addBatch("insert into t1 values(:a, :b)", [a:1, b:2])
ps.addBatch("insert into t2 values(:c)", [c:3])
}
(This throws an exception because addBatch doesn't have that signature.)
As described in Oracle's documentation:
Prepared statements: The same statement is repeated with different bind variables.
Batch updates: You can reduce the number of round-trips to the database, thereby improving application performance, by grouping multiple UPDATE, DELETE, or INSERT statements into a single batch and having the whole batch sent to the database and processed in one trip. This is especially useful in combination with a prepared statement.
As described in IBM's documentation and taken from here:
The JDBC drivers that support JDBC 2.0 and above support batch updates. With batch updates, instead of updating rows of a DB2(R) table one at a time, you can direct JDBC to execute a group of updates at the same time. Statements that can be included in the same batch of updates are known as batchable statements.
If a statement has input parameters or host expressions, you can include that statement only in a batch that has other instances of the same statement. This type of batch is known as a homogeneous batch. If a statement has no input parameters, you can include that statement in a batch only if the other statements in the batch have no input parameters or host expressions. This type of batch is known as a heterogeneous batch. Two statements that can be included in the same batch are known as batch compatible.
This means that your request is not possible. The only advantage you can get is performance improvement of batching the same type of statements AND preparing only once:
When you execute a single SQL statement the database performs the following actions:
When you use batch commands the following happens:
Since the preparation is performed only once you save time.
But you can sort and split the commands:
sql.withBatch(20, "insert into t1 values(:a, :b)") {
...
}
sql.withBatch(20, "insert into t2 values(:c)") {
...
}
BTW, what will compile is
sql.withBatch {ps ->
ps.addBatch("insert into t1 values(1, 2)")
ps.addBatch("insert into t2 values(3)")
}
But in this case I am curious what will happen: I expect that the JDBC driver will simply not use batching.
