CtrlK
BlogDocsLog inGet started
Tessl Logo

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.

—

Pending
Overview
Eval results
Files

core-operations.mddocs/

Core Database Operations

The Sql class provides the primary interface for database operations with automatic resource management and support for multiple query patterns including String SQL, parameterized queries, and GString syntax.

Connection Creation

Factory Methods

Create Sql instances with automatic connection management:

// Basic connection
static Sql newInstance(String url) throws SQLException

// With authentication
static Sql newInstance(String url, String user, String password) throws SQLException

// With driver specification
static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException

// With properties
static Sql newInstance(String url, Properties properties) throws SQLException
static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException

// With named parameters
static Sql newInstance(Map<String,Object> args) throws SQLException

Closure-based Factory Methods

Automatically handle connection cleanup:

static void withInstance(String url, Closure c) throws SQLException
static void withInstance(String url, String user, String password, Closure c) throws SQLException
static void withInstance(String url, String user, String password, String driverClassName, Closure c) throws SQLException
static void withInstance(String url, Properties properties, Closure c) throws SQLException
static void withInstance(Map<String,Object> args, Closure c) throws SQLException

Constructors

Create from existing connections or data sources:

Sql(DataSource dataSource)
Sql(Connection connection)
Sql(Sql parent)

Query Execution

Result Set Iteration

Execute queries and iterate over results using closures:

// Basic iteration with GroovyResultSet
void eachRow(String sql, Closure closure) throws SQLException

// With parameters
void eachRow(String sql, List<Object> params, Closure closure) throws SQLException
void eachRow(String sql, Map params, Closure closure) throws SQLException

// With pagination
void eachRow(String sql, int offset, int maxRows, Closure closure) throws SQLException
void eachRow(String sql, List<Object> params, int offset, int maxRows, Closure closure) throws SQLException

// With metadata handling
void eachRow(String sql, Closure metaClosure, Closure rowClosure) throws SQLException
void eachRow(String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException

// GString support
void eachRow(GString gstring, Closure closure) throws SQLException
void eachRow(GString gstring, int offset, int maxRows, Closure closure) throws SQLException

Raw ResultSet Access

Execute queries with direct ResultSet access:

// Direct ResultSet handling
void query(String sql, Closure closure) throws SQLException
void query(String sql, List<Object> params, Closure closure) throws SQLException
void query(String sql, Map map, Closure closure) throws SQLException
void query(GString gstring, Closure closure) throws SQLException

Row Collection Methods

All Rows

Retrieve complete result sets as lists:

// Get all rows as List<GroovyRowResult>
List<GroovyRowResult> rows(String sql) throws SQLException

// With parameters
List<GroovyRowResult> rows(String sql, List<Object> params) throws SQLException
List<GroovyRowResult> rows(String sql, Map params, Closure closure) throws SQLException
List<GroovyRowResult> rows(String sql, Object[] params) throws SQLException

// With pagination
List<GroovyRowResult> rows(String sql, int offset, int maxRows) throws SQLException
List<GroovyRowResult> rows(String sql, List<Object> params, int offset, int maxRows) throws SQLException

// With metadata handling
List<GroovyRowResult> rows(String sql, Closure metaClosure) throws SQLException
List<GroovyRowResult> rows(String sql, int offset, int maxRows, Closure metaClosure) throws SQLException

// GString support  
List<GroovyRowResult> rows(GString gstring) throws SQLException
List<GroovyRowResult> rows(GString gstring, int offset, int maxRows) throws SQLException

Single Row

Retrieve the first row only:

// Get first row as GroovyRowResult
GroovyRowResult firstRow(String sql) throws SQLException
GroovyRowResult firstRow(String sql, List<Object> params) throws SQLException
GroovyRowResult firstRow(String sql, Map params) throws SQLException
GroovyRowResult firstRow(String sql, Object[] params) throws SQLException
GroovyRowResult firstRow(GString gstring) throws SQLException

Statement Execution

Execute Statements

Execute SQL statements with optional result processing:

// Execute statement, returns true if ResultSet available
boolean execute(String sql) throws SQLException

// With result processing closure
void execute(String sql, Closure processResults) throws SQLException

// With parameters
boolean execute(String sql, List<Object> params) throws SQLException
boolean execute(String sql, Map params) throws SQLException
boolean execute(String sql, Object[] params) throws SQLException

// With parameters and result processing  
void execute(String sql, List<Object> params, Closure processResults) throws SQLException
void execute(String sql, Map params, Closure processResults) throws SQLException

// GString support
boolean execute(GString gstring) throws SQLException
void execute(GString gstring, Closure processResults) throws SQLException

Update Operations

Execute UPDATE, DELETE statements:

// Returns number of affected rows
int executeUpdate(String sql) throws SQLException
int executeUpdate(String sql, List<Object> params) throws SQLException
int executeUpdate(String sql, Map params) throws SQLException
int executeUpdate(String sql, Object[] params) throws SQLException
int executeUpdate(GString gstring) throws SQLException

Insert Operations

Execute INSERT statements with generated key support:

// Insert with generated keys
List<List<Object>> executeInsert(String sql) throws SQLException
List<List<Object>> executeInsert(String sql, List<Object> params) throws SQLException
List<List<Object>> executeInsert(String sql, Map params) throws SQLException
List<List<Object>> executeInsert(String sql, Object[] params) throws SQLException

// Insert with specific key columns
List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames) throws SQLException
List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames, Object[] params) throws SQLException
List<GroovyRowResult> executeInsert(String sql, List<Object> params, List<String> keyColumnNames) throws SQLException

// GString support
List<List<Object>> executeInsert(GString gstring) throws SQLException
List<GroovyRowResult> executeInsert(GString gstring, List<String> keyColumnNames) throws SQLException

Usage Examples

Basic Query Execution

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

// Simple query
sql.eachRow("SELECT * FROM users") { row ->
    println "User: ${row.name}, Email: ${row.email}"
}

// Parameterized query
sql.eachRow("SELECT * FROM users WHERE age > ?", [21]) { row ->
    println "Adult user: ${row.name}"
}

// GString query
def minAge = 18
sql.eachRow("SELECT * FROM users WHERE age > ${minAge}") { row ->
    println "User: ${row.name}"
}

Insert with Generated Keys

// Insert and get generated keys
def keys = sql.executeInsert("INSERT INTO users (name, email) VALUES (?, ?)", 
                            ["John Doe", "john@example.com"])
println "Generated ID: ${keys[0][0]}"

// Insert with specific key columns
def result = sql.executeInsert("INSERT INTO users (name, email) VALUES (?, ?)", 
                              ["Jane Doe", "jane@example.com"], 
                              ["id"])
println "New user ID: ${result[0].id}"

Closure-based Resource Management

// Automatic cleanup
Sql.withInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver") { sql ->
    sql.eachRow("SELECT COUNT(*) as total FROM users") { row ->
        println "Total users: ${row.total}"
    }
    // Connection automatically closed
}

Result Processing

// Process different result types
sql.execute("CALL complex_procedure()") { isResultSet, result ->
    if (isResultSet) {
        result.each { row ->
            println "Result row: ${row}"
        }
    } else {
        println "Update count: ${result}"
    }
}

Connection and Resource Management

Connection Access

Access the underlying connection and data source:

// Get the underlying Connection
Connection getConnection()

// Get the underlying DataSource (if available)
DataSource getDataSource()

// Get the update count from the last operation
int getUpdateCount()

Driver Management

Utility methods for JDBC driver management:

// Load a JDBC driver class
static void loadDriver(String driverClassName) throws ClassNotFoundException

ResultSet Configuration

Configure ResultSet behavior for queries:

// ResultSet type configuration
int getResultSetType()
void setResultSetType(int resultSetType)

// ResultSet concurrency configuration  
int getResultSetConcurrency()
void setResultSetConcurrency(int resultSetConcurrency)

// ResultSet holdability configuration
int getResultSetHoldability()
void setResultSetHoldability(int resultSetHoldability)

Statement Configuration

Configure statement behavior before execution:

// Configure statement settings like timeout, fetch size, etc.
void withStatement(Closure configureStatement)

Query Configuration

Configure query caching and named query support:

// Named query caching configuration
boolean isCacheNamedQueries()
void setCacheNamedQueries(boolean cacheNamedQueries)

// Named query support configuration
boolean isEnableNamedQueries()
void setEnableNamedQueries(boolean enableNamedQueries)

Usage Example: Connection and Configuration

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

// Configure ResultSet behavior
sql.setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)
sql.setResultSetConcurrency(ResultSet.CONCUR_READ_ONLY)

// Configure statement behavior
sql.withStatement { stmt ->
    stmt.setQueryTimeout(30)
    stmt.setFetchSize(100)
}

// Access underlying connection
Connection conn = sql.getConnection()
DatabaseMetaData metadata = conn.getMetaData()
println "Database: ${metadata.getDatabaseProductName()}"

// Load additional drivers if needed
Sql.loadDriver("com.mysql.cj.jdbc.Driver")

Install with Tessl CLI

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

docs

batch-transactions.md

core-operations.md

dataset-operations.md

index.md

parameters-procedures.md

result-handling.md

tile.json