CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/maven-org-apache-groovy--groovy-sql

Higher-level abstraction over Java's JDBC technology providing simplified database operations with resource management

—

Pending
Overview
Eval results
Files

result-processing.mddocs/

Result Processing and Row Handling

Advanced result set processing with Groovy-enhanced row access, metadata handling, and collection-style operations on database results.

Capabilities

Row Iteration

Process query results row-by-row using closures with automatic resource management and optional paging support.

// Basic row iteration
public void eachRow(String sql, Closure closure) throws SQLException;
public void eachRow(String sql, List<?> params, Closure closure) throws SQLException;
public void eachRow(String sql, Map params, Closure closure) throws SQLException;
public void eachRow(Map params, String sql, Closure closure) throws SQLException;
public void eachRow(GString gstring, Closure closure) throws SQLException;

// With paging
public void eachRow(String sql, int offset, int maxRows, Closure closure) throws SQLException;
public void eachRow(String sql, List<?> params, int offset, int maxRows, Closure closure) throws SQLException;
public void eachRow(String sql, Map params, int offset, int maxRows, Closure closure) throws SQLException;

// With metadata processing
public void eachRow(String sql, Closure metaClosure, Closure rowClosure) throws SQLException;
public void eachRow(String sql, List<?> params, Closure metaClosure, Closure rowClosure) throws SQLException;
public void eachRow(String sql, Map params, Closure metaClosure, Closure rowClosure) throws SQLException;

Example:

// Basic iteration
sql.eachRow('SELECT id, name, email FROM users') { row ->
    println "User: ${row.name} (${row.email})"
}

// With parameters
sql.eachRow('SELECT * FROM orders WHERE user_id = ?', [userId]) { row ->
    println "Order ${row.id}: ${row.total}"
}

// With paging (skip first 10, process next 20)
sql.eachRow('SELECT * FROM products ORDER BY name', 10, 20) { row ->
    println "${row.name}: \$${row.price}"
}

// With metadata processing
sql.eachRow('SELECT * FROM users',
    { meta ->
        println "Columns: ${(1..meta.columnCount).collect { meta.getColumnName(it) }}"
    },
    { row ->
        println "Row: ${row}"
    }
)

// Using GString
def category = 'electronics'
sql.eachRow("SELECT * FROM products WHERE category = $category") { row ->
    println row.name
}

GroovyRowResult Access

Access database row data using property-style syntax and Map interface methods.

public class GroovyRowResult extends GroovyObjectSupport implements Map<String, Object> {
    // Property-style access (case-insensitive)
    public Object getProperty(String property);
    
    // Index-based access (supports negative indices)
    public Object getAt(int index);
    
    // Map interface methods
    public Object get(Object property);
    public Object put(String key, Object value);
    public Object remove(Object rawKey);
    public boolean containsKey(Object key);
    public boolean containsValue(Object value);
    public boolean isEmpty();
    public int size();
    public Set<String> keySet();
    public Collection<Object> values();
    public Set<Map.Entry<String, Object>> entrySet();
    public void clear();
    public void putAll(Map<? extends String, ?> t);
    
    // Standard methods
    public boolean equals(Object o);
    public int hashCode();
    public String toString();
}

Example:

sql.eachRow('SELECT id, first_name, last_name, email FROM users') { row ->
    // Property-style access (case-insensitive)
    println row.first_name    // or row.FIRST_NAME or row.First_Name
    println row.lastName      // matches last_name column
    
    // Index-based access
    println row[0]            // id (first column)
    println row[-1]           // email (last column)
    
    // Map-style access
    println row.get('email')
    println row['id']
    
    // Check for columns
    if (row.containsKey('phone')) {
        println "Phone: ${row.phone}"
    }
    
    // Iterate over columns
    row.each { key, value ->
        println "$key: $value"
    }
    
    // Get all column names
    println "Columns: ${row.keySet()}"
    
    // Convert to string representation
    println row.toString()  // [ID:1, FIRST_NAME:Alice, LAST_NAME:Smith, EMAIL:alice@example.com]
}

Enhanced ResultSet Interface

Work with ResultSet objects enhanced with Groovy-style access methods and additional functionality.

public interface GroovyResultSet extends GroovyObject, ResultSet {
    // Groovy-style column access
    Object getAt(int index);
    Object getAt(String columnName);
    
    // Groovy-style column updates
    void putAt(int index, Object newValue);
    void putAt(String columnName, Object newValue);
    
    // Row operations
    void add(Map values);
    void eachRow(Closure closure);
}

Example:

sql.query('SELECT id, name, email FROM users') { resultSet ->
    // Cast to GroovyResultSet for enhanced functionality
    def grs = resultSet as GroovyResultSet
    
    grs.eachRow { 
        // Access columns with Groovy syntax
        println grs['name']      // by name
        println grs[1]           // by index
        
        // Update columns (if ResultSet is updatable)
        if (grs['email'] == null) {
            grs['email'] = 'no-email@example.com'
            grs.updateRow()
        }
    }
}

Metadata Handling

Access and process result set metadata for dynamic query handling and schema introspection.

public class ResultSetMetaDataWrapper extends GroovyObjectSupport {
    public ResultSetMetaDataWrapper(ResultSetMetaData target, int index);
    
    // Property access delegates to ResultSetMetaData methods for specific column
    public Object getProperty(String property);
    public void setProperty(String property, Object newValue);
    public Object invokeMethod(String name, Object args);
}

Example:

sql.eachRow('SELECT * FROM users', 
    { meta ->
        // Access metadata
        def columnCount = meta.columnCount
        println "Query returned $columnCount columns:"
        
        (1..columnCount).each { i ->
            def wrapper = new ResultSetMetaDataWrapper(meta, i)
            println "Column $i: ${wrapper.columnName} (${wrapper.columnTypeName})"
            
            // Or access directly
            println "  - Type: ${meta.getColumnType(i)}"
            println "  - Nullable: ${meta.isNullable(i)}"
            println "  - Precision: ${meta.getPrecision(i)}"
        }
    },
    { row ->
        // Process row data
        row.each { key, value ->
            println "$key = $value (${value?.class?.simpleName})"
        }
    }
)

// Using metadata in dynamic queries
def buildSelectQuery(tableName) {
    def columns = []
    sql.eachRow("SELECT * FROM $tableName LIMIT 0", 
        { meta ->
            (1..meta.columnCount).each { i ->
                columns << meta.getColumnName(i)
            }
        },
        { /* no rows to process */ }
    )
    return "SELECT ${columns.join(', ')} FROM $tableName"
}

Collection Integration

Use standard Groovy collection methods on query results for powerful data processing.

Example:

// Get results as collection and apply collection methods
def users = sql.rows('SELECT * FROM users')

// Filter active users
def activeUsers = users.findAll { it.active }

// Extract email addresses
def emails = users.collect { it.email }

// Group by department
def byDepartment = users.groupBy { it.department }

// Find specific user
def admin = users.find { it.role == 'admin' }

// Sort by name
def sortedUsers = users.sort { it.last_name }

// Calculate statistics
def totalSalary = users.sum { it.salary ?: 0 }
def avgAge = users.sum { it.age ?: 0 } / users.size()

// Complex processing
def summary = users
    .findAll { it.active && it.salary > 50000 }
    .groupBy { it.department }
    .collectEntries { dept, people ->
        [dept, [
            count: people.size(),
            avgSalary: people.sum { it.salary } / people.size(),
            names: people.collect { "${it.first_name} ${it.last_name}" }
        ]]
    }

println summary

Error Handling

Handle database exceptions and result processing errors with proper resource cleanup.

Example:

try {
    sql.eachRow('SELECT * FROM non_existent_table') { row ->
        println row
    }
} catch (SQLException e) {
    println "Database error: ${e.message}"
    // Resources automatically cleaned up
}

// Validate row data during processing
sql.eachRow('SELECT id, email FROM users') { row ->
    if (!row.email?.contains('@')) {
        println "Warning: Invalid email for user ${row.id}: ${row.email}"
    }
    
    // Defensive null checking
    def name = row.name ?: 'Unknown'
    println "Processing user: $name"
}

Install with Tessl CLI

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

docs

batch-operations.md

core-operations.md

dataset.md

index.md

parameters.md

result-processing.md

stored-procedures.md

transactions.md

tile.json