0
# Batch Processing and Transactions
1

2
Groovy SQL provides advanced features for batch processing multiple statements efficiently and declarative transaction management with automatic rollback on exceptions.
3

4
## Batch Processing
5

6
### Statement Batching
7

8
Execute multiple SQL statements in batches for improved performance:
9

10
```groovy { .api }
11
// Basic batch processing
12
int[] withBatch(Closure closure) throws SQLException
13

14
// Batch processing with custom batch size
15
int[] withBatch(int batchSize, Closure closure) throws SQLException
16
```
17

18
The closure receives a `BatchingStatementWrapper` that collects statements and executes them in batches.
19

20
### Prepared Statement Batching
21

22
Execute multiple prepared statements with different parameter sets:
23

24
```groovy { .api }
25
// Prepared statement batch processing
26
int[] withBatch(String sql, Closure closure) throws SQLException
27

28
// Prepared statement batch with custom batch size
29
int[] withBatch(int batchSize, String sql, Closure closure) throws SQLException
30
```
31

32
The closure receives a `BatchingPreparedStatementWrapper` for parameter binding.
33

34
## BatchingStatementWrapper
35

36
Wrapper class that provides automatic batch execution for regular statements:
37

38
### Constructor
39

40
```groovy { .api }
41
BatchingStatementWrapper(Statement delegate, int batchSize, Logger log)
42
```
43

44
### Batch Operations
45

46
```groovy { .api }
47
void addBatch(String sql)      // Add SQL statement to batch
48
void clearBatch()              // Clear current batch
49
int[] executeBatch()           // Execute accumulated batch
50
void close()                   // Close underlying statement
51
```
52

53
### Method Delegation
54

55
```groovy { .api }
56
Object invokeMethod(String name, Object args)  // Delegate to underlying Statement
57
```
58

59
## BatchingPreparedStatementWrapper
60

61
Wrapper class that extends BatchingStatementWrapper for prepared statements:
62

63
### Constructor
64

65
```groovy { .api }
66
BatchingPreparedStatementWrapper(PreparedStatement delegate, List<Tuple> indexPropList, int batchSize, Logger log, Sql sql)
67
```
68

69
### Parameter Batch Operations
70

71
```groovy { .api }
72
void addBatch(Object[] parameters)       // Add parameter array to batch
73
void addBatch(List<Object> parameters)   // Add parameter list to batch
74
```
75

76
## Transaction Management
77

78
### Declarative Transactions
79

80
Execute operations within a transaction with automatic rollback on exceptions:
81

82
```groovy { .api }
83
void withTransaction(Closure closure) throws SQLException
84
```
85

86
The closure executes within a database transaction. If any exception occurs, the transaction is automatically rolled back.
87

88
### Manual Transaction Control
89

90
Explicit transaction control methods:
91

92
```groovy { .api }
93
void commit() throws SQLException      // Commit current transaction
94
void rollback() throws SQLException   // Rollback current transaction
95
```
96

97
## Connection Management
98

99
### Connection Caching
100

101
Cache connections for improved performance during closure execution:
102

103
```groovy { .api }
104
void cacheConnection(Closure closure) throws SQLException
105
```
106

107
### Statement Caching
108

109
Cache prepared statements for reuse during closure execution:
110

111
```groovy { .api }
112
void cacheStatements(Closure closure) throws SQLException
113
```
114

115
### Configuration Properties
116

117
Control caching behavior:
118

119
```groovy { .api }
120
boolean isCacheStatements()
121
void setCacheStatements(boolean cacheStatements)
122
```
123

124
### Batch State Checking
125

126
Check if currently within a batch operation:
127

128
```groovy { .api }
129
boolean isWithinBatch()
130
```
131

132
## Usage Examples
133

134
### Basic Statement Batching
135

136
```groovy
137
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
138

139
// Batch multiple different statements  
140
def results = sql.withBatch { stmt ->
141
    stmt.addBatch("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')")
142
    stmt.addBatch("INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')")
143
    stmt.addBatch("UPDATE users SET active = true WHERE name = 'Alice'")
144
    stmt.addBatch("DELETE FROM users WHERE name = 'inactive_user'")
145
}
146

147
println "Batch results: ${results}"  // Array of update counts
148
```
149

150
### Prepared Statement Batching
151

152
```groovy
153
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
154

155
// Batch the same prepared statement with different parameters
156
def results = sql.withBatch("INSERT INTO users (name, email, age) VALUES (?, ?, ?)") { ps ->
157
    ps.addBatch(["Alice Smith", "alice@example.com", 28])
158
    ps.addBatch(["Bob Jones", "bob@example.com", 35])
159
    ps.addBatch(["Carol White", "carol@example.com", 42])
160
    ps.addBatch(["David Brown", "david@example.com", 31])
161
}
162

163
println "Inserted ${results.sum()} rows"
164
```
165

166
### Custom Batch Size
167

168
```groovy
169
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
170

171
// Process large data set with custom batch size
172
def largeDataSet = loadLargeDataSet()  // Assume this returns many records
173

174
def results = sql.withBatch(500, "INSERT INTO large_table (col1, col2, col3) VALUES (?, ?, ?)") { ps ->
175
    largeDataSet.each { record ->
176
        ps.addBatch([record.col1, record.col2, record.col3])
177
    }
178
}
179

180
println "Processed ${largeDataSet.size()} records in batches of 500"
181
println "Total inserts: ${results.sum()}"
182
```
183

184
### Mixed Batch Operations
185

186
```groovy
187
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
188

189
// Combine multiple operations in a single batch
190
def results = sql.withBatch(100) { stmt ->
191
    // Insert new records
192
    (1..50).each { i ->
193
        stmt.addBatch("INSERT INTO test_data (value) VALUES (${i})")
194
    }
195
    
196
    // Update existing records
197
    stmt.addBatch("UPDATE test_data SET processed = true WHERE value < 25")
198
    
199
    // Clean up old records
200
    stmt.addBatch("DELETE FROM test_data WHERE created_date < '2024-01-01'")
201
}
202

203
println "Batch execution completed: ${results.length} operations"
204
```
205

206
### Simple Transaction
207

208
```groovy
209
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
210

211
try {
212
    sql.withTransaction {
213
        // All operations within this block are part of the same transaction
214
        sql.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ["Alice", 1000])
215
        sql.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ["Bob", 500])
216
        
217
        // Transfer money between accounts
218
        sql.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", [200, "Alice"])
219
        sql.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", [200, "Bob"])
220
        
221
        // If we reach here, transaction will be committed automatically
222
        println "Transfer completed successfully"
223
    }
224
} catch (Exception e) {
225
    // Transaction was automatically rolled back
226
    println "Transfer failed: ${e.message}"
227
}
228
```
229

230
### Manual Transaction Control
231

232
```groovy
233
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
234

235
try {
236
    // Start transaction (auto-commit is disabled)
237
    sql.connection.autoCommit = false
238
    
239
    // Perform operations
240
    sql.execute("INSERT INTO orders (customer_id, amount) VALUES (?, ?)", [100, 250.00])
241
    def orderKeys = sql.executeInsert("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)", 
242
                                     [1, 500, 2])
243
    
244
    // Check some business rule
245
    def orderTotal = sql.firstRow("SELECT SUM(quantity * price) as total FROM order_items oi JOIN products p ON oi.product_id = p.id WHERE order_id = ?", [1])
246
    
247
    if (orderTotal.total > 1000) {
248
        sql.rollback()
249
        println "Order exceeds limit, rolled back"
250
    } else {
251
        sql.commit()
252
        println "Order committed successfully"
253
    }
254
    
255
} catch (Exception e) {
256
    sql.rollback()
257
    println "Error occurred, rolled back: ${e.message}"
258
} finally {
259
    sql.connection.autoCommit = true  // Restore auto-commit
260
}
261
```
262

263
### Nested Transactions with Batch
264

265
```groovy
266
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
267

268
sql.withTransaction {
269
    // Insert base records
270
    sql.execute("INSERT INTO projects (name, status) VALUES (?, ?)", ["Project Alpha", "active"])
271
    
272
    // Batch insert related records
273
    sql.withBatch("INSERT INTO tasks (project_id, name, status) VALUES (?, ?, ?)") { ps ->
274
        ps.addBatch([1, "Task 1", "pending"])
275
        ps.addBatch([1, "Task 2", "pending"])
276
        ps.addBatch([1, "Task 3", "pending"])
277
    }
278
    
279
    // Batch update in same transaction
280
    sql.withBatch { stmt ->
281
        stmt.addBatch("UPDATE tasks SET assigned_to = 'user1' WHERE name = 'Task 1'")
282
        stmt.addBatch("UPDATE tasks SET assigned_to = 'user2' WHERE name = 'Task 2'")
283
        stmt.addBatch("UPDATE projects SET task_count = 3 WHERE id = 1")
284
    }
285
    
286
    println "Project and tasks created successfully"
287
}
288
```
289

290
### Connection and Statement Caching
291

292
```groovy
293
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
294

295
// Cache connection for multiple operations
296
sql.cacheConnection {
297
    // Connection is reused for all operations in this block
298
    
299
    sql.cacheStatements {
300
        // Prepared statements are also cached and reused
301
        
302
        (1..1000).each { i ->
303
            sql.execute("INSERT INTO performance_test (value, created) VALUES (?, ?)", [i, new Date()])
304
        }
305
        
306
        // Same prepared statement is reused for all iterations
307
        println "Completed 1000 inserts with cached connection and statements"
308
    }
309
}
310
```
311

312
### Error Handling in Batches
313

314
```groovy
315
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
316

317
try {
318
    def results = sql.withBatch("INSERT INTO users (id, name, email) VALUES (?, ?, ?)") { ps ->
319
        ps.addBatch([1, "Alice", "alice@example.com"])
320
        ps.addBatch([2, "Bob", "bob@example.com"])
321
        ps.addBatch([1, "Charlie", "charlie@example.com"])  // Duplicate ID will cause error
322
        ps.addBatch([3, "David", "david@example.com"])
323
    }
324
    
325
    println "All batches succeeded: ${results}"
326
    
327
} catch (BatchUpdateException e) {
328
    // Handle batch-specific errors
329
    def updateCounts = e.updateCounts
330
    println "Batch partially failed:"
331
    updateCounts.eachWithIndex { count, index ->
332
        if (count == Statement.EXECUTE_FAILED) {
333
            println "  Batch ${index} failed"
334
        } else {
335
            println "  Batch ${index} succeeded with ${count} updates"
336
        }
337
    }
338
    
339
} catch (SQLException e) {
340
    println "SQL Error in batch: ${e.message}"
341
}
342
```
343

344
### Performance Monitoring
345

346
```groovy
347
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
348

349
// Monitor batch performance
350
def startTime = System.currentTimeMillis()
351

352
def results = sql.withBatch(1000, "INSERT INTO performance_data (value, timestamp) VALUES (?, ?)") { ps ->
353
    (1..10000).each { i ->
354
        ps.addBatch([Math.random(), new Timestamp(System.currentTimeMillis())])
355
    }
356
}
357

358
def endTime = System.currentTimeMillis()
359
def duration = endTime - startTime
360

361
println "Batch inserted ${results.sum()} rows in ${duration}ms"
362
println "Average: ${results.sum() / (duration / 1000.0)} rows/second"
363
```
364

365
### Transaction Rollback Scenarios
366

367
```groovy
368
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
369

370
// Scenario 1: Exception causes automatic rollback
371
try {
372
    sql.withTransaction {
373
        sql.execute("INSERT INTO accounts (id, name, balance) VALUES (?, ?, ?)", [1, "Alice", 1000])
374
        sql.execute("INSERT INTO accounts (id, name, balance) VALUES (?, ?, ?)", [2, "Bob", 500])
375
        
376
        // This will throw an exception due to business logic
377
        if (sql.firstRow("SELECT balance FROM accounts WHERE id = ?", [1]).balance < 2000) {
378
            throw new RuntimeException("Insufficient initial balance")
379
        }
380
        
381
        // This line won't be reached
382
        sql.execute("UPDATE accounts SET status = 'active'")
383
    }
384
} catch (Exception e) {
385
    println "Transaction rolled back: ${e.message}"
386
    
387
    // Verify rollback worked
388
    def count = sql.firstRow("SELECT COUNT(*) as cnt FROM accounts")
389
    println "Accounts in database: ${count.cnt}"  // Should be 0
390
}
391

392
// Scenario 2: Conditional rollback within transaction
393
sql.withTransaction {
394
    sql.execute("INSERT INTO orders (id, customer_id, amount) VALUES (?, ?, ?)", [1, 100, 750])
395
    
396
    def fraudCheck = performFraudCheck(100, 750)  // Assume this exists
397
    
398
    if (fraudCheck.suspicious) {
399
        // Manual rollback within transaction
400
        sql.connection.rollback()
401
        println "Order rolled back due to fraud suspicion"
402
        return  // Exit transaction block
403
    }
404
    
405
    sql.execute("UPDATE customers SET last_order_date = ? WHERE id = ?", [new Date(), 100])
406
    println "Order processed successfully"
407
}
408
```