tessl/maven-org-codehaus-groovy--groovy-sql

A facade over Java's JDBC APIs providing greatly simplified resource management and result set handling with Groovy-specific enhancements.

—

Pending

Overview

Eval results

Files

Batch Processing and Transactions

Name: tessl/maven-org-codehaus-groovy--groovy-sql
Author: tessl

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

Batch Processing

Statement Batching

Execute multiple SQL statements in batches for improved performance:

// Basic batch processing
int[] withBatch(Closure closure) throws SQLException

// Batch processing with custom batch size
int[] withBatch(int batchSize, Closure closure) throws SQLException

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

Prepared Statement Batching

Execute multiple prepared statements with different parameter sets:

// Prepared statement batch processing
int[] withBatch(String sql, Closure closure) throws SQLException

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

The closure receives a BatchingPreparedStatementWrapper for parameter binding.

BatchingStatementWrapper

Wrapper class that provides automatic batch execution for regular statements:

Constructor

BatchingStatementWrapper(Statement delegate, int batchSize, Logger log)

Batch Operations

void addBatch(String sql)      // Add SQL statement to batch
void clearBatch()              // Clear current batch
int[] executeBatch()           // Execute accumulated batch
void close()                   // Close underlying statement

Method Delegation

Object invokeMethod(String name, Object args)  // Delegate to underlying Statement

BatchingPreparedStatementWrapper

Wrapper class that extends BatchingStatementWrapper for prepared statements:

Constructor

BatchingPreparedStatementWrapper(PreparedStatement delegate, List<Tuple> indexPropList, int batchSize, Logger log, Sql sql)

Parameter Batch Operations

void addBatch(Object[] parameters)       // Add parameter array to batch
void addBatch(List<Object> parameters)   // Add parameter list to batch

Transaction Management

Declarative Transactions

Execute operations within a transaction with automatic rollback on exceptions:

void withTransaction(Closure closure) throws SQLException

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

Manual Transaction Control

Explicit transaction control methods:

void commit() throws SQLException      // Commit current transaction
void rollback() throws SQLException   // Rollback current transaction

Connection Management

Connection Caching

Cache connections for improved performance during closure execution:

void cacheConnection(Closure closure) throws SQLException

Statement Caching

Cache prepared statements for reuse during closure execution:

void cacheStatements(Closure closure) throws SQLException

Configuration Properties

Control caching behavior:

boolean isCacheStatements()
void setCacheStatements(boolean cacheStatements)

Batch State Checking

Check if currently within a batch operation:

boolean isWithinBatch()

Usage Examples

Basic Statement Batching

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

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

println "Batch results: ${results}"  // Array of update counts

Prepared Statement Batching

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

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

println "Inserted ${results.sum()} rows"

Custom Batch Size

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

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

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

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

Mixed Batch Operations

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

// Combine multiple operations in a single batch
def results = sql.withBatch(100) { stmt ->
    // Insert new records
    (1..50).each { i ->
        stmt.addBatch("INSERT INTO test_data (value) VALUES (${i})")
    }
    
    // Update existing records
    stmt.addBatch("UPDATE test_data SET processed = true WHERE value < 25")
    
    // Clean up old records
    stmt.addBatch("DELETE FROM test_data WHERE created_date < '2024-01-01'")
}

println "Batch execution completed: ${results.length} operations"

Simple Transaction

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

try {
    sql.withTransaction {
        // All operations within this block are part of the same transaction
        sql.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ["Alice", 1000])
        sql.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ["Bob", 500])
        
        // Transfer money between accounts
        sql.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", [200, "Alice"])
        sql.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", [200, "Bob"])
        
        // If we reach here, transaction will be committed automatically
        println "Transfer completed successfully"
    }
} catch (Exception e) {
    // Transaction was automatically rolled back
    println "Transfer failed: ${e.message}"
}

Manual Transaction Control

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

try {
    // Start transaction (auto-commit is disabled)
    sql.connection.autoCommit = false
    
    // Perform operations
    sql.execute("INSERT INTO orders (customer_id, amount) VALUES (?, ?)", [100, 250.00])
    def orderKeys = sql.executeInsert("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)", 
                                     [1, 500, 2])
    
    // Check some business rule
    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])
    
    if (orderTotal.total > 1000) {
        sql.rollback()
        println "Order exceeds limit, rolled back"
    } else {
        sql.commit()
        println "Order committed successfully"
    }
    
} catch (Exception e) {
    sql.rollback()
    println "Error occurred, rolled back: ${e.message}"
} finally {
    sql.connection.autoCommit = true  // Restore auto-commit
}

Nested Transactions with Batch

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

sql.withTransaction {
    // Insert base records
    sql.execute("INSERT INTO projects (name, status) VALUES (?, ?)", ["Project Alpha", "active"])
    
    // Batch insert related records
    sql.withBatch("INSERT INTO tasks (project_id, name, status) VALUES (?, ?, ?)") { ps ->
        ps.addBatch([1, "Task 1", "pending"])
        ps.addBatch([1, "Task 2", "pending"])
        ps.addBatch([1, "Task 3", "pending"])
    }
    
    // Batch update in same transaction
    sql.withBatch { stmt ->
        stmt.addBatch("UPDATE tasks SET assigned_to = 'user1' WHERE name = 'Task 1'")
        stmt.addBatch("UPDATE tasks SET assigned_to = 'user2' WHERE name = 'Task 2'")
        stmt.addBatch("UPDATE projects SET task_count = 3 WHERE id = 1")
    }
    
    println "Project and tasks created successfully"
}

Connection and Statement Caching

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

// Cache connection for multiple operations
sql.cacheConnection {
    // Connection is reused for all operations in this block
    
    sql.cacheStatements {
        // Prepared statements are also cached and reused
        
        (1..1000).each { i ->
            sql.execute("INSERT INTO performance_test (value, created) VALUES (?, ?)", [i, new Date()])
        }
        
        // Same prepared statement is reused for all iterations
        println "Completed 1000 inserts with cached connection and statements"
    }
}

Error Handling in Batches

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

try {
    def results = sql.withBatch("INSERT INTO users (id, name, email) VALUES (?, ?, ?)") { ps ->
        ps.addBatch([1, "Alice", "alice@example.com"])
        ps.addBatch([2, "Bob", "bob@example.com"])
        ps.addBatch([1, "Charlie", "charlie@example.com"])  // Duplicate ID will cause error
        ps.addBatch([3, "David", "david@example.com"])
    }
    
    println "All batches succeeded: ${results}"
    
} catch (BatchUpdateException e) {
    // Handle batch-specific errors
    def updateCounts = e.updateCounts
    println "Batch partially failed:"
    updateCounts.eachWithIndex { count, index ->
        if (count == Statement.EXECUTE_FAILED) {
            println "  Batch ${index} failed"
        } else {
            println "  Batch ${index} succeeded with ${count} updates"
        }
    }
    
} catch (SQLException e) {
    println "SQL Error in batch: ${e.message}"
}

Performance Monitoring

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

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

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

def endTime = System.currentTimeMillis()
def duration = endTime - startTime

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

Transaction Rollback Scenarios

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

// Scenario 1: Exception causes automatic rollback
try {
    sql.withTransaction {
        sql.execute("INSERT INTO accounts (id, name, balance) VALUES (?, ?, ?)", [1, "Alice", 1000])
        sql.execute("INSERT INTO accounts (id, name, balance) VALUES (?, ?, ?)", [2, "Bob", 500])
        
        // This will throw an exception due to business logic
        if (sql.firstRow("SELECT balance FROM accounts WHERE id = ?", [1]).balance < 2000) {
            throw new RuntimeException("Insufficient initial balance")
        }
        
        // This line won't be reached
        sql.execute("UPDATE accounts SET status = 'active'")
    }
} catch (Exception e) {
    println "Transaction rolled back: ${e.message}"
    
    // Verify rollback worked
    def count = sql.firstRow("SELECT COUNT(*) as cnt FROM accounts")
    println "Accounts in database: ${count.cnt}"  // Should be 0
}

// Scenario 2: Conditional rollback within transaction
sql.withTransaction {
    sql.execute("INSERT INTO orders (id, customer_id, amount) VALUES (?, ?, ?)", [1, 100, 750])
    
    def fraudCheck = performFraudCheck(100, 750)  // Assume this exists
    
    if (fraudCheck.suspicious) {
        // Manual rollback within transaction
        sql.connection.rollback()
        println "Order rolled back due to fraud suspicion"
        return  // Exit transaction block
    }
    
    sql.execute("UPDATE customers SET last_order_date = ? WHERE id = ?", [new Date(), 100])
    println "Order processed successfully"
}

Install with Tessl CLI

npx tessl i tessl/maven-org-codehaus-groovy--groovy-sql

docs

batch-transactions.md

core-operations.md

dataset-operations.md

index.md

parameters-procedures.md

result-handling.md

tile.json

tessl/maven-org-codehaus-groovy--groovy-sql

batch-transactions.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Batch Processing and Transactions

Batch Processing

Statement Batching

Prepared Statement Batching

BatchingStatementWrapper

Constructor

Batch Operations

Method Delegation

BatchingPreparedStatementWrapper

Constructor

Parameter Batch Operations

Transaction Management

Declarative Transactions

Manual Transaction Control

Connection Management

Connection Caching

Statement Caching

Configuration Properties

Batch State Checking

Usage Examples

Basic Statement Batching

Prepared Statement Batching

Custom Batch Size

Mixed Batch Operations

Simple Transaction

Manual Transaction Control

Nested Transactions with Batch

Connection and Statement Caching

Error Handling in Batches

Performance Monitoring

Transaction Rollback Scenarios

batch-transactions.mddocs/