SQL Database Operations

/**
 * Main class for database operations with automatic connection management
 */
class Sql {
    /**
     * Create Sql instance with JDBC URL and credentials
     */
    static Sql newInstance(String url, String user, String password, String driver) throws SQLException;
    
    /**
     * Create Sql instance with JDBC URL only
     */
    static Sql newInstance(String url) throws SQLException;
    
    /**
     * Create Sql instance from existing Connection
     */
    static Sql newInstance(Connection connection);
    
    /**
     * Create Sql instance from DataSource
     */
    static Sql newInstance(DataSource dataSource);
    
    /**
     * Create Sql instance with Properties configuration
     */
    static Sql newInstance(Properties properties) throws SQLException;
    
    /**
     * Close the database connection and clean up resources
     */
    void close() throws SQLException;
    
    /**
     * Get the underlying JDBC Connection
     */
    Connection getConnection();
    
    /**
     * Check if connection is closed
     */
    boolean isClosed() throws SQLException;
}

import groovy.sql.Sql

// Connect with full credentials
def sql = Sql.newInstance("jdbc:h2:mem:testdb", 
                         "sa", 
                         "", 
                         "org.h2.Driver")

// Connect with DataSource (recommended for production)
import javax.sql.DataSource
import org.apache.commons.dbcp2.BasicDataSource

def dataSource = new BasicDataSource()
dataSource.url = "jdbc:postgresql://localhost:5432/mydb"
dataSource.username = "user"
dataSource.password = "password"
dataSource.driverClassName = "org.postgresql.Driver"

def sql = Sql.newInstance(dataSource)

// Always close when done
try {
    // database operations
} finally {
    sql.close()
}

// Or use with try-with-resources pattern
Sql.withInstance("jdbc:h2:mem:testdb", "sa", "", "org.h2.Driver") { sql ->
    // database operations - connection automatically closed
}

/**
 * Query execution methods for retrieving data
 */
class Sql {
    /**
     * Execute query and return all rows as List of GroovyRowResult
     */
    List<GroovyRowResult> rows(String sql) throws SQLException;
    List<GroovyRowResult> rows(String sql, List params) throws SQLException;
    List<GroovyRowResult> rows(String sql, Object... params) throws SQLException;
    List<GroovyRowResult> rows(String sql, Map params) throws SQLException;
    
    /**
     * Execute query and iterate over each row
     */
    void eachRow(String sql, Closure closure) throws SQLException;
    void eachRow(String sql, List params, Closure closure) throws SQLException;
    void eachRow(String sql, Map params, Closure closure) throws SQLException;
    
    /**
     * Execute query and return first row only
     */
    GroovyRowResult firstRow(String sql) throws SQLException;
    GroovyRowResult firstRow(String sql, List params) throws SQLException;
    GroovyRowResult firstRow(String sql, Map params) throws SQLException;
    
    /**
     * Execute prepared statement with result set processing
     */
    void query(String sql, Closure closure) throws SQLException;
    void query(String sql, List params, Closure closure) throws SQLException;
}

/**
 * Row result that provides map-like and object-like access to column data
 */
interface GroovyRowResult extends Map<String, Object> {
    /**
     * Get column value by name (case-insensitive)
     */
    Object getProperty(String columnName);
    
    /**
     * Get column value by index (0-based)
     */
    Object getAt(int columnIndex);
    
    /**
     * Convert row to Map
     */
    Map<String, Object> toRowResult();
}

import groovy.sql.Sql

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

// Create sample table and data
sql.execute("""
    CREATE TABLE employees (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        department VARCHAR(30),
        salary DECIMAL(10,2)
    )
""")

sql.execute("INSERT INTO employees VALUES (1, 'Alice', 'Engineering', 85000)")
sql.execute("INSERT INTO employees VALUES (2, 'Bob', 'Marketing', 65000)")
sql.execute("INSERT INTO employees VALUES (3, 'Carol', 'Engineering', 90000)")

// Query all rows
def employees = sql.rows("SELECT * FROM employees ORDER BY name")
employees.each { row ->
    println "${row.name} (${row.department}): \$${row.salary}"
}

// Query with parameters (safe from SQL injection)
def engineers = sql.rows("SELECT * FROM employees WHERE department = ?", ["Engineering"])
assert engineers.size() == 2

// Query with named parameters
def highEarners = sql.rows("""
    SELECT * FROM employees 
    WHERE salary > :minSalary 
    ORDER BY salary DESC
""", [minSalary: 80000])

// Iterate over large result sets efficiently
sql.eachRow("SELECT * FROM employees") { row ->
    println "Processing employee: ${row.name}"
    // Process one row at a time - memory efficient
}

// Get single row
def employee = sql.firstRow("SELECT * FROM employees WHERE id = ?", [1])
if (employee) {
    println "Found: ${employee.name}"
}

// Access columns by index or name
employees.each { row ->
    println "ID: ${row[0]}"           // By index
    println "Name: ${row.name}"       // By property name  
    println "Dept: ${row['department']}" // By map key
}

sql.close()

/**
 * Data modification methods
 */
class Sql {
    /**
     * Execute UPDATE, INSERT, or DELETE statement
     */
    int executeUpdate(String sql) throws SQLException;
    int executeUpdate(String sql, List params) throws SQLException;
    int executeUpdate(String sql, Map params) throws SQLException;
    
    /**
     * Execute any SQL statement
     */
    boolean execute(String sql) throws SQLException;
    boolean execute(String sql, List params) throws SQLException;
    
    /**
     * Execute INSERT and return generated keys
     */
    List<GroovyRowResult> executeInsert(String sql) throws SQLException;
    List<GroovyRowResult> executeInsert(String sql, List params) throws SQLException;
    
    /**
     * Batch operations for efficient bulk processing
     */
    int[] withBatch(String sql, Closure closure) throws SQLException;
    int[] withBatch(int batchSize, String sql, Closure closure) throws SQLException;
}

import groovy.sql.Sql

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

// Insert single record
def rowsAffected = sql.executeUpdate("""
    INSERT INTO employees (id, name, department, salary) 
    VALUES (?, ?, ?, ?)
""", [4, "Dave", "Sales", 55000])

assert rowsAffected == 1

// Insert with named parameters
sql.executeUpdate("""
    INSERT INTO employees (id, name, department, salary) 
    VALUES (:id, :name, :dept, :salary)
""", [id: 5, name: "Eve", dept: "HR", salary: 70000])

// Update records
def updated = sql.executeUpdate("""
    UPDATE employees 
    SET salary = salary * 1.1 
    WHERE department = ?
""", ["Engineering"])

println "Updated $updated engineering salaries"

// Delete records  
def deleted = sql.executeUpdate("DELETE FROM employees WHERE salary < ?", [60000])

// Insert and get generated keys
def keys = sql.executeInsert("""
    INSERT INTO employees (name, department, salary) 
    VALUES (?, ?, ?)
""", ["Frank", "IT", 75000])

keys.each { key ->
    println "Generated ID: ${key[0]}"
}

// Batch processing for bulk operations
def newEmployees = [
    [name: "Grace", dept: "Finance", salary: 68000],
    [name: "Henry", dept: "Operations", salary: 62000],
    [name: "Iris", dept: "Legal", salary: 95000]
]

sql.withBatch("INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)") { stmt ->
    newEmployees.each { emp ->
        stmt.addBatch([emp.name, emp.dept, emp.salary])
    }
}

sql.close()

/**
 * Transaction management methods
 */
class Sql {
    /**
     * Execute closure within a database transaction
     */
    void withTransaction(Closure closure) throws SQLException;
    
    /**
     * Begin a new transaction
     */
    void begin() throws SQLException;
    
    /**
     * Commit current transaction
     */
    void commit() throws SQLException;
    
    /**
     * Rollback current transaction
     */
    void rollback() throws SQLException;
    
    /**
     * Check if currently in a transaction
     */
    boolean isInTransaction();
    
    /**
     * Set auto-commit mode
     */
    void setAutoCommit(boolean autoCommit) throws SQLException;
}

import groovy.sql.Sql

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

// Automatic transaction management
sql.withTransaction { 
    sql.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = ?", [1])
    sql.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = ?", [2])
    
    // If any exception occurs, transaction is automatically rolled back
    def balance = sql.firstRow("SELECT balance FROM accounts WHERE id = ?", [1])
    if (balance.balance < 0) {
        throw new RuntimeException("Insufficient funds")
    }
    // Transaction commits automatically if no exception
}

// Manual transaction control
try {
    sql.begin()
    
    sql.executeUpdate("INSERT INTO audit_log VALUES (?, ?)", [new Date(), "Operation started"])
    sql.executeUpdate("UPDATE critical_data SET status = 'processing'")
    
    // Simulate complex operation
    performComplexOperation()
    
    sql.executeUpdate("UPDATE critical_data SET status = 'completed'")
    sql.commit()
    
} catch (Exception e) {
    sql.rollback()
    println "Transaction rolled back: ${e.message}"
}

sql.close()

/**
 * DataSet provides table-like operations on database tables
 */
class DataSet {
    /**
     * Create DataSet for specific table
     */
    DataSet(Sql sql, String tableName);
    
    /**
     * Add new row to the dataset/table
     */
    void add(Map<String, Object> values);
    
    /**
     * Find all rows matching criteria
     */
    DataSet findAll(Closure criteria);
    
    /**
     * Iterate over all rows in dataset
     */
    void each(Closure closure);
    
    /**
     * Get first row matching criteria
     */
    GroovyRowResult firstRow();
    
    /**
     * Get all rows as List
     */
    List<GroovyRowResult> rows();
}

import groovy.sql.Sql
import groovy.sql.DataSet

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

// Create DataSet for employees table
def employees = new DataSet(sql, "employees")

// Add new employees
employees.add(name: "John", department: "IT", salary: 72000)
employees.add(name: "Jane", department: "IT", salary: 78000)

// Find IT employees
def itEmployees = employees.findAll { it.department == "IT" }
itEmployees.each { emp ->
    println "${emp.name}: \$${emp.salary}"
}

// Iterate over all employees
employees.each { employee ->
    if (employee.salary > 75000) {
        println "High earner: ${employee.name}"
    }
}

sql.close()

import groovy.sql.Sql
import org.apache.commons.dbcp2.BasicDataSource

// Configure connection pool
def setupDataSource() {
    def dataSource = new BasicDataSource()
    dataSource.url = "jdbc:postgresql://localhost:5432/production"
    dataSource.username = "app_user"
    dataSource.password = "secure_password"
    dataSource.driverClassName = "org.postgresql.Driver"
    
    // Connection pool settings
    dataSource.initialSize = 5
    dataSource.maxTotal = 20
    dataSource.maxIdle = 10
    dataSource.minIdle = 5
    dataSource.maxWaitMillis = 30000
    
    // Connection validation
    dataSource.validationQuery = "SELECT 1"
    dataSource.testOnBorrow = true
    dataSource.testWhileIdle = true
    
    return dataSource
}

def dataSource = setupDataSource()

// Use pooled connections
Sql.withInstance(dataSource) { sql ->
    // Database operations using pooled connection
    def results = sql.rows("SELECT COUNT(*) as total FROM orders WHERE status = 'completed'")
    println "Completed orders: ${results[0].total}"
}

// Connection is automatically returned to pool

/**
 * Row result interface providing multiple access patterns
 */
interface GroovyRowResult extends Map<String, Object> {
    /**
     * Get column value by property-style access
     */
    Object getProperty(String columnName);
    
    /**
     * Get column value by array-style access  
     */
    Object getAt(int columnIndex);
    Object getAt(String columnName);
    
    /**
     * Convert to standard Map
     */
    Map<String, Object> toRowResult();
    
    /**
     * Get column metadata
     */
    ResultSetMetaData getMetaData();
}

/**
 * Exception thrown for SQL-related errors
 */
class SQLException extends Exception {
    String getSQLState();
    int getErrorCode();
    SQLException getNextException();
}

/**
 * Table-like abstraction over SQL operations
 */
class DataSet {
    /**
     * The underlying Sql instance
     */
    Sql getSql();
    
    /**
     * The table name this DataSet represents
     */
    String getTableName();
    
    /**
     * Add row to dataset
     */
    void add(Map<String, Object> values);
    
    /**
     * Find rows matching criteria
     */
    DataSet findAll(Closure criteria);
    
    /**
     * Iterate over rows
     */
    void each(Closure closure);
    
    /**
     * Get first matching row
     */
    GroovyRowResult firstRow();
    
    /**
     * Get all rows
     */
    List<GroovyRowResult> rows();
}