or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

batch-transactions.mdcore-operations.mddataset-operations.mdindex.mdparameters-procedures.mdresult-handling.md
tile.json

tessl/maven-org-codehaus-groovy--groovy-sql

A facade over Java's JDBC APIs providing greatly simplified resource management and result set handling with Groovy-specific enhancements.

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
mavenpkg:maven/org.codehaus.groovy/groovy-sql@3.0.x

To install, run

npx @tessl/cli install tessl/maven-org-codehaus-groovy--groovy-sql@3.0.0

index.mddocs/

Groovy SQL

Groovy SQL is a facade over Java's JDBC APIs that provides greatly simplified resource management and result set handling. It features closures for result set iteration, GString syntax for prepared statements, and treats result sets like collections of maps with normal Groovy collection methods available.

Package Information

  • Package Name: groovy-sql
  • Package Type: maven
  • Language: Java/Groovy
  • Installation: implementation 'org.codehaus.groovy:groovy-sql:3.0.25' (Gradle) or <dependency><groupId>org.codehaus.groovy</groupId><artifactId>groovy-sql</artifactId><version>3.0.25</version></dependency> (Maven)
  • Group ID: org.codehaus.groovy
  • Artifact ID: groovy-sql

Core Imports

import groovy.sql.Sql
import groovy.sql.DataSet
import groovy.sql.GroovyRowResult
import static org.apache.groovy.sql.extensions.SqlExtensions.*

For Java usage:

import groovy.sql.Sql;
import groovy.sql.DataSet;
import groovy.sql.GroovyRowResult;
import static org.apache.groovy.sql.extensions.SqlExtensions.*;

Basic Usage

// 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
    )
'''

// Insert data
sql.execute "INSERT INTO person (id, name, age) VALUES (?, ?, ?)", [1, "Alice", 30]

// Query with closure iteration
sql.eachRow("SELECT * FROM person WHERE age > ?", [25]) { row ->
    println "Found: ${row.name}, age ${row.age}"
}

// Get all rows as list
def people = sql.rows("SELECT * FROM person")
people.each { person ->
    println "${person.name} is ${person.age} years old"
}

// Close connection
sql.close()

Architecture

Groovy SQL is built around several key components:

  • Sql Class: Main facade providing simplified database operations with automatic resource management
  • DataSet: Enhanced query interface supporting POGO field-based filtering and sorting
  • GroovyRowResult: Map-like wrapper for result rows with case-insensitive column access
  • Parameter Types: Typed parameter system for stored procedures (InParameter, OutParameter, InOutParameter)
  • Batch Processing: Automatic statement batching with configurable batch sizes
  • Transaction Support: Declarative transaction management using closures

Capabilities

Core Database Operations

Primary database operations including queries, updates, inserts, and stored procedure calls. Provides multiple execution patterns from simple SQL strings to parameterized GString queries.

class Sql implements AutoCloseable {
    // Factory methods
    static Sql newInstance(String url, String user, String password, String driverClassName)
    static void withInstance(String url, String user, String password, String driverClassName, Closure c)
    
    // Core query methods
    void eachRow(String sql, Closure closure)
    void eachRow(String sql, List<Object> params, Closure closure)
    List<GroovyRowResult> rows(String sql)
    List<GroovyRowResult> rows(String sql, List<Object> params)
    GroovyRowResult firstRow(String sql)
    
    // Execution methods
    boolean execute(String sql)
    boolean execute(String sql, List<Object> params)
    int executeUpdate(String sql, List<Object> params)
    List<List<Object>> executeInsert(String sql, List<Object> params)
}

Core Database Operations

DataSet Operations

Enhanced database operations using POGO (Plain Old Groovy Object) field-based queries. Provides fluent API for filtering, sorting, and data manipulation with closure-based syntax.

class DataSet extends Sql {
    DataSet(Sql sql, String table)
    DataSet(Sql sql, Class type)
    
    void add(Map<String, Object> map)
    DataSet findAll(Closure where)
    DataSet sort(Closure sort)
    void each(Closure closure)
    List rows()
    Object firstRow()
}

DataSet Operations

Result Handling

Specialized result handling classes that provide Groovy-friendly access to database results. Includes map-like row access and enhanced ResultSet functionality.

class GroovyRowResult implements Map<String, Object> {
    Object getProperty(String property)
    Object getAt(int index)
    // Standard Map interface methods
}

interface GroovyResultSet extends ResultSet, GroovyObject {
    Object getAt(int index)
    Object getAt(String columnName)
    void putAt(int index, Object newValue)
    void eachRow(Closure closure)
}

Result Handling

Parameter Types and Stored Procedures

Type-safe parameter system for stored procedure calls supporting input, output, and input/output parameters with proper JDBC type mapping.

// Static parameter factory methods
static InParameter in(int type, Object value)
static OutParameter out(int type)
static InOutParameter inout(InParameter in)
static ResultSetOutParameter resultSet(int type)

// Typed parameter constants
static final OutParameter INTEGER, VARCHAR, TIMESTAMP, BLOB // ... and others

// Stored procedure calls
int call(String sql, List<Object> params)
void call(String sql, List<Object> params, Closure closure)
List<GroovyRowResult> callWithRows(String sql, List<Object> params, Closure closure)

Parameter Types and Stored Procedures

Batch Processing and Transactions

Advanced features for batch processing multiple statements and declarative transaction management with automatic rollback on exceptions.

// Batch processing
int[] withBatch(Closure closure)
int[] withBatch(int batchSize, Closure closure)
int[] withBatch(String sql, Closure closure)

// Transaction management
void withTransaction(Closure closure)
void commit()
void rollback()

// Connection caching
void cacheConnection(Closure closure)
void cacheStatements(Closure closure)

Batch Processing and Transactions

Types

interface InParameter {
    int getType()
    Object getValue()
}

interface OutParameter {
    int getType()
}

interface InOutParameter extends InParameter, OutParameter {
}

interface ResultSetOutParameter extends OutParameter {
}

interface ExpandedVariable {
    Object getObject()
}

class SqlWithParams {
    SqlWithParams(String sql, List<Object> params)
    String getSql()
    List<Object> getParams()
}

class BatchingStatementWrapper implements AutoCloseable {
    void addBatch(String sql)
    void clearBatch()
    int[] executeBatch()
    void close()
}

class BatchingPreparedStatementWrapper extends BatchingStatementWrapper {
    void addBatch(Object[] parameters)
    void addBatch(List<Object> parameters)
}

// Extension methods from org.apache.groovy.sql.extensions.SqlExtensions
static GroovyRowResult toRowResult(ResultSet rs) throws SQLException
static Timestamp toTimestamp(Date d)
static boolean asBoolean(GroovyResultSet grs)
static Iterator<ResultSetMetaDataWrapper> iterator(ResultSetMetaData resultSetMetaData)