tessl/maven-org-codehaus-groovy--groovy-all

Apache Groovy - A powerful multi-faceted programming language for the JVM platform with comprehensive module support

—

Pending

Overview

Eval results

Files

Database Access

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

Groovy provides comprehensive database access through the Sql class and DataSet, offering simplified JDBC operations, connection management, and result processing with Groovy's dynamic features.

Database Connections

Sql

Main class for database operations providing simplified JDBC access.

class Sql implements Closeable {
    // Factory methods for creating connections
    static Sql newInstance(String url)
    static Sql newInstance(String url, Properties properties)
    static Sql newInstance(String url, String user, String password)
    static Sql newInstance(String url, String user, String password, String driverClassName)
    static Sql newInstance(String url, String user, String password, Properties properties, String driverClassName)
    static Sql newInstance(Map<String, Object> args)
    static Sql newInstance(DataSource dataSource)
    static Sql newInstance(Connection connection)
    
    // Query methods
    List<GroovyRowResult> rows(String sql)
    List<GroovyRowResult> rows(String sql, List<Object> params)
    List<GroovyRowResult> rows(String sql, Map params)
    List<GroovyRowResult> rows(String sql, Object... params)
    List<GroovyRowResult> rows(Map params, String sql)
    
    GroovyRowResult firstRow(String sql)
    GroovyRowResult firstRow(String sql, List<Object> params)
    GroovyRowResult firstRow(String sql, Map params) 
    GroovyRowResult firstRow(String sql, Object... params)
    
    void eachRow(String sql, Closure closure)
    void eachRow(String sql, List<Object> params, Closure closure)
    void eachRow(String sql, Map params, Closure closure)
    void eachRow(String sql, int offset, int maxRows, Closure closure)
    void eachRow(String sql, List<Object> params, int offset, int maxRows, Closure closure)
    
    // Update methods
    int executeUpdate(String sql)
    int executeUpdate(String sql, List<Object> params)
    int executeUpdate(String sql, Map params)
    int executeUpdate(String sql, Object... params)
    
    boolean execute(String sql)
    boolean execute(String sql, List<Object> params)
    boolean execute(String sql, Map params)
    List<List<Object>> executeInsert(String sql)
    List<List<Object>> executeInsert(String sql, List<Object> params)
    
    // Prepared statement support
    void withStatement(Closure closure)
    Object call(String sql, List<Object> params, Closure closure)
    Object call(String sql, Closure closure)
    
    // Transaction support
    void withTransaction(Closure closure)
    void commit()
    void rollback()
    void setAutoCommit(boolean autoCommit)
    boolean getAutoCommit()
    
    // Batch operations
    void withBatch(String sql, Closure closure)
    void withBatch(int batchSize, String sql, Closure closure)
    int[] executeBatch(String sql)
    
    // Connection management
    Connection getConnection()
    DataSource getDataSource()
    void close()
    boolean isClosed()
}

Usage examples:

import groovy.sql.Sql

// Create connection
def sql = Sql.newInstance(
    'jdbc:h2:mem:testdb',
    'sa', 
    '', 
    'org.h2.Driver'
)

// Create table
sql.execute '''
    CREATE TABLE person (
        id INTEGER PRIMARY KEY,
        name VARCHAR(50),
        age INTEGER,
        email VARCHAR(100)
    )
'''

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

// Insert with named parameters
sql.executeUpdate '''
    INSERT INTO person (id, name, age, email) 
    VALUES (:id, :name, :age, :email)
''', [id: 2, name: 'Jane Smith', age: 25, email: 'jane@example.com']

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

// Query with parameters
def adults = sql.rows('SELECT * FROM person WHERE age >= ?', [21])
def john = sql.firstRow('SELECT * FROM person WHERE name = ?', ['John Doe'])

// Iterate through results
sql.eachRow('SELECT * FROM person') { row ->
    println "ID: ${row.id}, Name: ${row.name}, Email: ${row.email}"
}

// Update records
def updated = sql.executeUpdate(
    'UPDATE person SET age = ? WHERE name = ?', 
    [31, 'John Doe']
)
println "Updated $updated records"

// Close connection
sql.close()

GroovyRowResult

Enhanced result row that provides map-like and object-like access to column values.

class GroovyRowResult implements Map<String, Object> {
    Object getAt(int index)
    Object getAt(String columnName)
    void putAt(String columnName, Object value)
    void putAt(int index, Object value)
    
    Object getProperty(String property)
    void setProperty(String property, Object value)
    
    // Map interface methods
    int size()
    boolean isEmpty()
    boolean containsKey(Object key)
    boolean containsValue(Object value)
    Object get(Object key)
    Object put(String key, Object value)
    Object remove(Object key)
    void putAll(Map<? extends String, ?> m)
    void clear()
    Set<String> keySet()
    Collection<Object> values()
    Set<Map.Entry<String, Object>> entrySet()
    
    // Utility methods
    String toString()
}

Usage example:

def person = sql.firstRow('SELECT * FROM person WHERE id = ?', [1])

// Access columns by name
assert person.name == 'John Doe'
assert person['email'] == 'john@example.com'

// Access columns by index
assert person[1] == 'John Doe'  // Assuming name is second column

// Use as Map
person.each { key, value ->
    println "$key: $value"
}

// Modify values (for update operations)
person.age = 32
sql.executeUpdate(
    'UPDATE person SET age = ? WHERE id = ?',
    [person.age, person.id]
)

Dataset Operations

DataSet

Higher-level abstraction for table operations with automatic SQL generation.

class DataSet {
    DataSet(Sql sql, String table)
    DataSet(DataSet parent, String table)
    DataSet(DataSet parent, Closure where)
    
    // Query operations
    void each(Closure closure)
    void eachWithIndex(Closure closure) 
    List findAll(Closure where)
    Object find(Closure where)
    GroovyRowResult firstRow()
    List<GroovyRowResult> rows()
    int size()
    
    // Data modification
    void add(Map<String, Object> values)
    DataSet findAll(Closure where)
    int update(Map<String, Object> values)
    int update(Closure where, Map<String, Object> values)
    
    // Dataset operations
    DataSet createView(Closure where)
    DataSet reverse()
    DataSet sort(Closure sort)
    
    String getTable()
    Sql getSql()
}

Usage examples:

import groovy.sql.DataSet

def personTable = sql.dataSet('person')

// Add records
personTable.add(name: 'Alice Brown', age: 28, email: 'alice@example.com')
personTable.add(name: 'Bob Wilson', age: 35, email: 'bob@example.com')

// Query with closures
def adults = personTable.findAll { it.age >= 21 }
def alice = personTable.find { it.name == 'Alice Brown' }

// Iterate through records
personTable.each { person ->
    println "${person.name}: ${person.email}"
}

// Update records
personTable.update(age: 29) { it.name == 'Alice Brown' }

// Create filtered views
def seniors = personTable.createView { it.age >= 65 }
def youngAdults = personTable.createView { it.age >= 18 && it.age < 30 }

// Sort data
def sortedByAge = personTable.sort { it.age }
def sortedByName = personTable.sort { it.name }

Stored Procedures

Parameter Types

Support for stored procedure input, output, and input/output parameters.

class OutParameter {
    OutParameter(int sqlType)
    OutParameter(int sqlType, int scale)
    OutParameter(int sqlType, String typeName)
    
    int getType()
    int getScale()
    String getTypeName()
}

class InOutParameter extends OutParameter {
    InOutParameter(Object value, int sqlType)
    InOutParameter(Object value, int sqlType, int scale)
    InOutParameter(Object value, int sqlType, String typeName)
    
    Object getValue()
}

class ResultSetOutParameter extends OutParameter {
    ResultSetOutParameter(int sqlType)
}

Usage examples:

import groovy.sql.OutParameter
import groovy.sql.InOutParameter
import java.sql.Types

// Call stored procedure with output parameters
def outParam = new OutParameter(Types.VARCHAR)
def inOutParam = new InOutParameter('initial', Types.VARCHAR)

def result = sql.call(
    '{call GetPersonInfo(?, ?, ?)}',
    [1, outParam, inOutParam]
)

println "Output parameter: ${result[1]}"
println "InOut parameter: ${result[2]}"

// Call function that returns result set
def resultSetParam = new ResultSetOutParameter(Types.REF_CURSOR)
sql.call('{? = call GetAllPersons()}', [resultSetParam]) { cursor ->
    cursor.eachRow { row ->
        println "${row.name}: ${row.email}"
    }
}

Transaction Management

Transaction Control

// Automatic transaction management
sql.withTransaction { connection ->
    sql.executeUpdate('UPDATE person SET age = age + 1 WHERE id = ?', [1])
    sql.executeUpdate('INSERT INTO audit_log (action, timestamp) VALUES (?, ?)', 
                     ['age_increment', new Date()])
    // Transaction automatically committed on success, rolled back on exception
}

// Manual transaction control
try {
    sql.autoCommit = false
    
    sql.executeUpdate('DELETE FROM person WHERE age < ?', [18])
    def deletedCount = sql.updateCount
    
    if (deletedCount > 10) {
        throw new RuntimeException('Too many records would be deleted')
    }
    
    sql.commit()
    println "Successfully deleted $deletedCount records"
    
} catch (Exception e) {
    sql.rollback()
    println "Transaction rolled back: ${e.message}"
} finally {
    sql.autoCommit = true
}

Batch Operations

Batch Processing

// Simple batch execution
def batchData = [
    [3, 'Charlie Brown', 22, 'charlie@example.com'],
    [4, 'Diana Prince', 27, 'diana@example.com'],
    [5, 'Edward Norton', 35, 'edward@example.com']
]

sql.withBatch('INSERT INTO person (id, name, age, email) VALUES (?, ?, ?, ?)') { stmt ->
    batchData.each { person ->
        stmt.addBatch(person)
    }
}

// Batch with size limit
sql.withBatch(100, 'INSERT INTO large_table (data) VALUES (?)') { stmt ->
    (1..1000).each { i ->
        stmt.addBatch(["Data item $i"])
    }
}

// Manual batch control
def stmt = sql.connection.prepareStatement('UPDATE person SET last_login = ? WHERE id = ?')
try {
    [1, 2, 3, 4, 5].each { id ->
        stmt.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()))
        stmt.setInt(2, id)
        stmt.addBatch()
    }
    int[] results = stmt.executeBatch()
    println "Batch update results: $results"
} finally {
    stmt.close()
}

Connection Pooling

DataSource Integration

import org.apache.commons.dbcp2.BasicDataSource

// Create connection pool
def dataSource = new BasicDataSource()
dataSource.driverClassName = 'org.h2.Driver'
dataSource.url = 'jdbc:h2:mem:testdb'
dataSource.username = 'sa'
dataSource.password = ''
dataSource.initialSize = 5
dataSource.maxTotal = 20

// Use with Groovy SQL
def sql = new Sql(dataSource)

// Perform operations
sql.eachRow('SELECT * FROM person') { row ->
    println row.name
}

// Connection automatically returned to pool when Sql instance is closed
sql.close()

// Shutdown pool when application ends
dataSource.close()

Error Handling

Exception Handling

import java.sql.SQLException
import java.sql.SQLIntegrityConstraintViolationException

try {
    sql.executeUpdate('INSERT INTO person (id, name) VALUES (?, ?)', [1, 'Duplicate'])
} catch (SQLIntegrityConstraintViolationException e) {
    println "Constraint violation: ${e.message}"
} catch (SQLException e) {
    println "SQL error (${e.SQLState}): ${e.message}"
} catch (Exception e) {
    println "General error: ${e.message}"
}

// Check for warnings
def warnings = sql.connection.warnings
while (warnings) {
    println "Warning: ${warnings.message}"
    warnings = warnings.nextWarning
}

Advanced Features

Metadata Access

// Database metadata
def metaData = sql.connection.metaData
println "Database: ${metaData.databaseProductName} ${metaData.databaseProductVersion}"
println "Driver: ${metaData.driverName} ${metaData.driverVersion}"

// Table information
def tables = sql.rows("""
    SELECT table_name, table_type 
    FROM information_schema.tables 
    WHERE table_schema = 'PUBLIC'
""")

tables.each { table ->
    println "Table: ${table.table_name} (${table.table_type})"
}

// Column information
def columns = sql.rows("""
    SELECT column_name, data_type, is_nullable
    FROM information_schema.columns
    WHERE table_name = 'PERSON'
""")

columns.each { col ->
    println "Column: ${col.column_name} - ${col.data_type} (nullable: ${col.is_nullable})"
}

Custom Type Handling

import java.sql.Types

// Custom type conversion
sql.resultSetConcurrency = java.sql.ResultSet.CONCUR_UPDATABLE

// Handle special data types
sql.eachRow('SELECT id, data, created_date FROM special_table') { row ->
    def id = row.id
    def jsonData = row.data // Assuming CLOB containing JSON
    def timestamp = row.created_date
    
    // Process custom types
    if (jsonData) {
        def parsed = new groovy.json.JsonSlurper().parseText(jsonData.toString())
        println "JSON data: $parsed"
    }
}

// Insert custom types
def jsonData = groovy.json.JsonOutput.toJson([key: 'value', number: 42])
sql.executeUpdate(
    'INSERT INTO special_table (data, created_date) VALUES (?, ?)',
    [jsonData, new java.sql.Timestamp(System.currentTimeMillis())]
)

Install with Tessl CLI

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

docs

tessl/maven-org-codehaus-groovy--groovy-all

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

Database Access

Database Connections

Sql

GroovyRowResult

Dataset Operations

DataSet

Stored Procedures

Parameter Types

Transaction Management

Transaction Control

Batch Operations

Batch Processing

Connection Pooling

DataSource Integration

Error Handling

Exception Handling

Advanced Features

Metadata Access

Custom Type Handling

sql.mddocs/