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

Apache Groovy is a powerful, optionally typed and dynamic language, with static-typing and static compilation capabilities, for the Java platform aimed at improving developer productivity thanks to a concise, familiar and easy to learn syntax.

—

Pending

Overview

Eval results

Files

SQL Database Access

Name: tessl/maven-org-apache-groovy--groovy
Author: tessl

Database operations with enhanced result sets, fluent query building, and comprehensive JDBC integration for seamless database interactions.

Capabilities

Main SQL Class

Primary class for database operations providing connection management and query execution.

/**
 * Main class for database operations
 */
class Sql {
    /** Create Sql instance from DataSource */
    static Sql newInstance(DataSource dataSource)
    
    /** Create Sql instance with connection parameters */
    static Sql newInstance(String url, String user, String password, String driver)
    
    /** Create Sql instance with Properties */
    static Sql newInstance(String url, Properties properties, String driver)
    
    /** Create Sql instance from existing Connection */
    static Sql newInstance(Connection connection)
    
    /** Execute query and process each row with closure */
    void eachRow(String sql, Closure closure)
    
    /** Execute query with parameters and process each row */
    void eachRow(String sql, List<Object> params, Closure closure)
    
    /** Execute query with named parameters and process each row */
    void eachRow(String sql, Map<String, Object> params, Closure closure)
    
    /** Execute query and return list of GroovyRowResult */
    List<GroovyRowResult> rows(String sql)
    
    /** Execute query with parameters and return rows */
    List<GroovyRowResult> rows(String sql, List<Object> params)
    
    /** Execute query with named parameters and return rows */
    List<GroovyRowResult> rows(String sql, Map<String, Object> params)
    
    /** Execute query and return first row */
    GroovyRowResult firstRow(String sql)
    
    /** Execute query with parameters and return first row */
    GroovyRowResult firstRow(String sql, List<Object> params)
    
    /** Execute update/insert/delete statement */
    int executeUpdate(String sql)
    
    /** Execute update with parameters */
    int executeUpdate(String sql, List<Object> params)
    
    /** Execute any SQL statement */
    boolean execute(String sql)
    
    /** Execute stored procedure */
    void call(String sql, Closure closure)
    
    /** Execute in transaction */
    Object withTransaction(Closure closure)
    
    /** Execute in batch */
    int[] withBatch(String sql, Closure closure)
    
    /** Get underlying Connection */
    Connection getConnection()
    
    /** Close the connection */
    void close()
}

Usage Examples:

import groovy.sql.Sql

// Connect to database
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", 
                         "user", "password", "com.mysql.jdbc.Driver")

// Query with eachRow
sql.eachRow("SELECT * FROM users WHERE age > ?", [18]) { row ->
    println "User: ${row.name}, Age: ${row.age}, Email: ${row.email}"
}

// Query returning all rows
def users = sql.rows("SELECT * FROM users ORDER BY name")
users.each { user ->
    println "${user.name} (${user.age})"
}

// Query with named parameters
def activeUsers = sql.rows("""
    SELECT * FROM users 
    WHERE active = :active AND department = :dept
""", [active: true, dept: 'Engineering'])

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

// Execute updates
def rowsAffected = sql.executeUpdate("""
    UPDATE users SET last_login = ? WHERE id = ?
""", [new Date(), 123])

// Insert data
sql.executeUpdate("""
    INSERT INTO users (name, email, age) VALUES (?, ?, ?)
""", ["John Doe", "john@example.com", 30])

// Transaction handling
sql.withTransaction {
    sql.executeUpdate("UPDATE accounts SET balance = balance - ? WHERE id = ?", [100, 1])
    sql.executeUpdate("UPDATE accounts SET balance = balance + ? WHERE id = ?", [100, 2])
}

// Batch operations
sql.withBatch("INSERT INTO logs (message, timestamp) VALUES (?, ?)") { stmt ->
    logMessages.each { message ->
        stmt.addBatch([message, new Date()])
    }
}

// Always close connection
sql.close()

Enhanced Result Sets

Enhanced result set classes providing convenient access to query results.

/**
 * Row result from SQL queries implementing Map interface
 */
class GroovyRowResult implements Map<String, Object> {
    /** Get column value by index (0-based) */
    Object getAt(int index)
    
    /** Get column value by name (case-insensitive) */
    Object getAt(String columnName)
    
    /** Get column value as specific type */
    Object asType(Class type)
    
    /** Get all column names */
    Set<String> keySet()
    
    /** Get all column values */
    Collection<Object> values()
    
    /** Convert to regular Map */
    Map<String, Object> toRowMap()
}

/**
 * Enhanced ResultSet with Groovy features
 */
class GroovyResultSet {
    /** Get column value by index */
    Object getAt(int index)
    
    /** Get column value by name */
    Object getAt(String columnName)
    
    /** Process each row with closure */
    void eachRow(Closure closure)
    
    /** Get all rows as list */
    List<GroovyRowResult> toRowList()
}

Usage Examples:

// Working with GroovyRowResult
def user = sql.firstRow("SELECT id, name, email, created_at FROM users WHERE id = ?", [123])

// Access by column name
println user.name           // Column name access
println user['email']       // Map-style access
println user.created_at     // Timestamp column

// Access by index
println user[0]             // id (first column)
println user[1]             // name (second column)

// Type conversion
def userId = user.id as Long
def createdDate = user.created_at as java.time.LocalDateTime

// Map operations
user.each { column, value ->
    println "$column: $value"
}

println "User has ${user.size()} columns"
println "Column names: ${user.keySet()}"

DataSet Abstraction

High-level abstraction for working with database tables as datasets.

/**
 * Dataset abstraction for database tables
 */
class DataSet {
    /** Create DataSet for specified table */
    DataSet(Sql sql, String tableName)
    
    /** Iterate over all rows */
    void each(Closure closure)
    
    /** Find rows matching closure condition */
    DataSet findAll(Closure closure)
    
    /** Add new row to the table */
    void add(Map<String, Object> values)
    
    /** Get first row matching condition */
    GroovyRowResult find(Closure closure)
    
    /** Count rows matching condition */
    int count(Closure closure)
    
    /** Remove rows matching condition */
    void remove(Closure closure)
    
    /** Get underlying SQL instance */
    Sql getSql()
    
    /** Get table name */
    String getTableName()
}

Usage Examples:

// Create DataSet for a table
def users = sql.dataSet("users")

// Add new records
users.add([
    name: "Alice Johnson",
    email: "alice@company.com",
    age: 28,
    department: "Engineering"
])

// Find records
def engineers = users.findAll { it.department == "Engineering" }
engineers.each { user ->
    println "${user.name} - ${user.email}"
}

// Count records
def engineerCount = users.count { it.department == "Engineering" }
println "Engineers: $engineerCount"

// Find specific record
def alice = users.find { it.name == "Alice Johnson" }
if (alice) {
    println "Found Alice: ${alice.email}"
}

// Remove records
users.remove { it.age < 18 }  // Remove minors

Parameter Handling

Support for various parameter binding approaches.

/**
 * In parameter for stored procedures
 */
class InParameter {
    InParameter(Object value)
    InParameter(Object value, int sqlType)
}

/**
 * Out parameter for stored procedures
 */
class OutParameter {
    OutParameter(int sqlType)
}

/**
 * In-Out parameter for stored procedures
 */
class InOutParameter {
    InOutParameter(Object value, int sqlType)
}

Usage Examples:

import groovy.sql.*
import java.sql.Types

// Stored procedure with parameters
sql.call("{ call getUserStats(?, ?, ?) }") { stmt ->
    stmt.setInt(1, userId)
    stmt.registerOutParameter(2, Types.INTEGER)  // total_orders
    stmt.registerOutParameter(3, Types.DECIMAL)  // total_spent
    
    stmt.execute()
    
    def totalOrders = stmt.getInt(2)
    def totalSpent = stmt.getBigDecimal(3)
    
    println "User has $totalOrders orders totaling $totalSpent"
}

// Using parameter objects
def params = [
    new InParameter(userId),
    new OutParameter(Types.INTEGER),
    new OutParameter(Types.DECIMAL)
]

sql.call("{ call getUserStats(?, ?, ?) }", params) { result ->
    println "Orders: ${result[1]}, Spent: ${result[2]}"
}

Batch Operations

Efficient batch processing for bulk operations.

/**
 * Wrapper for batch prepared statements
 */
class BatchingPreparedStatementWrapper {
    /** Add parameters to batch */
    void addBatch(List params)
    
    /** Execute the batch */
    int[] executeBatch()
    
    /** Clear the batch */
    void clearBatch()
}

/**
 * Wrapper for batch statements
 */
class BatchingStatementWrapper {
    /** Add SQL to batch */
    void addBatch(String sql)
    
    /** Execute the batch */
    int[] executeBatch()
    
    /** Clear the batch */
    void clearBatch()
}

Usage Examples:

// Batch insert with prepared statement
sql.withBatch("INSERT INTO products (name, price, category) VALUES (?, ?, ?)") { stmt ->
    products.each { product ->
        stmt.addBatch([product.name, product.price, product.category])
    }
}

// Batch multiple different statements
sql.withBatch { stmt ->
    stmt.addBatch("UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1")
    stmt.addBatch("INSERT INTO sales_log (product_id, quantity) VALUES (1, 1)")
    stmt.addBatch("UPDATE customer_stats SET total_purchases = total_purchases + 1 WHERE customer_id = 123")
}

// Custom batch processing
sql.withBatch("""
    INSERT INTO order_items (order_id, product_id, quantity, price) 
    VALUES (?, ?, ?, ?)
""") { batchStmt ->
    order.items.each { item ->
        batchStmt.addBatch([
            order.id,
            item.productId,
            item.quantity,
            item.unitPrice
        ])
    }
}

Connection Management

/**
 * Connection utilities and management
 */
class Sql {
    /** Create connection pool */
    static Sql newInstance(String url, Properties props, String driver, int maxConnections)
    
    /** Test connection */
    boolean isConnected()
    
    /** Get connection metadata */
    DatabaseMetaData getMetaData()
    
    /** Set auto-commit mode */
    void setAutoCommit(boolean autoCommit)
    
    /** Commit transaction */
    void commit()
    
    /** Rollback transaction */
    void rollback()
}

Usage Examples:

// Connection management
def sql = Sql.newInstance(jdbcUrl, props, driver)

try {
    // Disable auto-commit for transaction
    sql.setAutoCommit(false)
    
    // Perform operations
    sql.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    sql.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    
    // Commit if all succeeded
    sql.commit()
    
} catch (Exception e) {
    // Rollback on error
    sql.rollback()
    throw e
} finally {
    sql.close()
}

// Check connection status
if (sql.isConnected()) {
    println "Database connection is active"
}

Install with Tessl CLI

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

docs

cli-interface.md

collections-utilities.md

template-processing.md

xml-processing.md

tile.json

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

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

SQL Database Access

Capabilities

Main SQL Class

Enhanced Result Sets

DataSet Abstraction

Parameter Handling

Batch Operations

Connection Management

sql-database.mddocs/