Transaction Management

// Execute closure within transaction
public Object withTransaction(Closure closure) throws SQLException;

// Manual transaction control
public void commit() throws SQLException;
public void rollback() throws SQLException;

// Automatic transaction management
sql.withTransaction {
    sql.executeUpdate('INSERT INTO accounts (id, balance) VALUES (1, 1000)')
    sql.executeUpdate('INSERT INTO accounts (id, balance) VALUES (2, 500)')
    
    // Transfer money between accounts
    sql.executeUpdate('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1])
    sql.executeUpdate('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2])
    
    // If any operation fails, entire transaction is rolled back
}

// Manual transaction control
try {
    sql.executeUpdate('INSERT INTO orders (customer_id, total) VALUES (?, ?)', [customerId, total])
    def orderId = sql.firstRow('SELECT LAST_INSERT_ID() as id').id
    
    orderItems.each { item ->
        sql.executeUpdate('INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)',
                         [orderId, item.productId, item.quantity])
    }
    
    sql.commit()
} catch (Exception e) {
    sql.rollback()
    throw e
}

// Cache connection during closure execution
public Object cacheConnection(Closure closure) throws SQLException;

// Access cached connection
public Connection getConnection() throws SQLException;
public DataSource getDataSource();

// Cache connection for multiple operations
sql.cacheConnection {
    // All operations in this block reuse the same connection
    def users = sql.rows('SELECT * FROM users')
    users.each { user ->
        sql.executeUpdate('UPDATE user_stats SET last_seen = NOW() WHERE user_id = ?', [user.id])
    }
    
    // Update summary statistics
    sql.executeUpdate('UPDATE system_stats SET total_users = (SELECT COUNT(*) FROM users)')
}

// Without caching, each operation would get a new connection from pool

// ResultSet type (scrollability)
public int getResultSetType();
public void setResultSetType(int resultSetType);

// ResultSet concurrency (updatability)  
public int getResultSetConcurrency();
public void setResultSetConcurrency(int resultSetConcurrency);

// ResultSet holdability (transaction behavior)
public int getResultSetHoldability();
public void setResultSetHoldability(int resultSetHoldability);

import java.sql.ResultSet

// Configure for scrollable, updatable ResultSet
sql.resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE
sql.resultSetConcurrency = ResultSet.CONCUR_UPDATABLE

sql.query('SELECT * FROM users') { rs ->
    // Can scroll backwards and forwards
    rs.last()
    println "Total rows: ${rs.row}"
    
    rs.first()
    while (rs.next()) {
        if (rs.getString('email') == null) {
            // Can update directly in ResultSet
            rs.updateString('email', 'no-email@example.com')
            rs.updateRow()
        }
    }
}

// Configure holdability
sql.resultSetHoldability = ResultSet.HOLD_CURSORS_OVER_COMMIT

// Statement caching configuration
public boolean isCacheStatements();
public void setCacheStatements(boolean cacheStatements);

// Execute with cached statements
public Object cacheStatements(Closure closure) throws SQLException;

// Statement configuration hooks
public Object withStatement(Closure configureStatement) throws SQLException;
public Object withCleanupStatement(Closure cleanupStatement) throws SQLException;

// Enable statement caching
sql.cacheStatements = true

// Cached statements will be reused for identical SQL
def userIds = [1, 2, 3, 4, 5]
userIds.each { id ->
    // Same PreparedStatement reused for each call
    def user = sql.firstRow('SELECT * FROM users WHERE id = ?', [id])
    println user.name
}

// Temporary statement caching
sql.cacheStatements {
    // Statements cached only within this block
    100.times { i ->
        sql.executeUpdate('INSERT INTO temp_data (value) VALUES (?)', [i])
    }
}

// Configure statement properties
sql.withStatement { stmt ->
    stmt.queryTimeout = 30
    stmt.fetchSize = 1000
    stmt.maxRows = 10000
} {
    def results = sql.rows('SELECT * FROM large_table')
    // Statement configured for this operation
}

// Named query configuration
public boolean isEnableNamedQueries();
public void setEnableNamedQueries(boolean enableNamedQueries);
public boolean isCacheNamedQueries();
public void setCacheNamedQueries(boolean cacheNamedQueries);

// Enable named query support and caching
sql.enableNamedQueries = true
sql.cacheNamedQueries = true

// Named parameters are parsed and cached
def params = [minAge: 25, dept: 'Engineering']
sql.eachRow('SELECT * FROM users WHERE age >= :minAge AND department = :dept', params) { row ->
    println "${row.name} - ${row.department}"
}

// Query parsing is cached for subsequent calls
def otherParams = [minAge: 30, dept: 'Sales']  
sql.eachRow('SELECT * FROM users WHERE age >= :minAge AND department = :dept', otherParams) { row ->
    println "${row.name} - ${row.department}"
}

import org.apache.commons.dbcp2.BasicDataSource

// Configure connection pool
def dataSource = new BasicDataSource()
dataSource.url = 'jdbc:mysql://localhost:3306/mydb'
dataSource.username = 'user'
dataSource.password = 'pass'
dataSource.driverClassName = 'com.mysql.cj.jdbc.Driver'
dataSource.initialSize = 5
dataSource.maxTotal = 20
dataSource.maxIdle = 10
dataSource.minIdle = 5

def sql = new Sql(dataSource)

// Use within transactions to ensure connection reuse
sql.withTransaction {
    // All operations use same connection from pool
    def orders = sql.rows('SELECT * FROM orders WHERE date = CURDATE()')
    orders.each { order ->
        sql.executeUpdate('UPDATE orders SET status = ? WHERE id = ?', ['processed', order.id])
    }
}

// Connection automatically returned to pool when transaction completes

import java.sql.Connection

// Get underlying connection to set isolation level
sql.cacheConnection {
    def conn = sql.connection
    def originalLevel = conn.transactionIsolation
    
    try {
        // Set stricter isolation for critical operations
        conn.transactionIsolation = Connection.TRANSACTION_SERIALIZABLE
        
        sql.withTransaction {
            // Critical financial transaction
            def balance = sql.firstRow('SELECT balance FROM accounts WHERE id = ?', [accountId]).balance
            if (balance >= amount) {
                sql.executeUpdate('UPDATE accounts SET balance = balance - ? WHERE id = ?', 
                                 [amount, accountId])
            } else {
                throw new RuntimeException('Insufficient funds')
            }
        }
    } finally {
        // Restore original isolation level
        conn.transactionIsolation = originalLevel
    }
}