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

core-operations.mddocs/

Core Database Operations

Primary database interaction functionality providing simplified JDBC interface with automatic resource management, connection handling, and basic CRUD operations.

Capabilities

Connection Management

Create and manage database connections through various factory methods supporting different connection sources.

// JDBC URL connections
public static Sql newInstance(String url) throws SQLException;
public static Sql newInstance(String url, Properties properties) throws SQLException;
public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException;
public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException;

// Credential-based connections
public static Sql newInstance(String url, String user, String password) throws SQLException;
public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException, ClassNotFoundException;

// Map-based configuration
public static Sql newInstance(Map<String, Object> args) throws SQLException, ClassNotFoundException;

// Resource management versions (automatic cleanup)
public static void withInstance(String url, Closure c) throws SQLException;
public static void withInstance(String url, Properties properties, Closure c) throws SQLException;
public static void withInstance(String url, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
public static void withInstance(String url, String user, String password, Closure c) throws SQLException;
public static void withInstance(String url, String user, String password, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
public static void withInstance(String url, Properties properties, String driverClassName, Closure c) throws SQLException, ClassNotFoundException;
public static void withInstance(Map<String, Object> args, Closure c) throws SQLException, ClassNotFoundException;

// Direct construction
public Sql(DataSource dataSource);
public Sql(Connection connection);
public Sql(Sql parent);

// Utility methods
public static void loadDriver(String driverClassName) throws ClassNotFoundException;
public void close();

Example:

// Using JDBC URL
def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Using DataSource
def dataSource = new BasicDataSource()
dataSource.url = 'jdbc:mysql://localhost:3306/mydb'
dataSource.username = 'user'
dataSource.password = 'pass'
def sql = new Sql(dataSource)

// Using withInstance for automatic cleanup
Sql.withInstance('jdbc:h2:mem:testdb', 'sa', '') { sql ->
    // Operations here - automatically closed
    sql.execute('CREATE TABLE test (id INT)')
}

Query Execution

Execute SELECT queries with various parameter binding options and result processing approaches.

// Basic query execution with closure processing
public void query(String sql, Closure closure) throws SQLException;
public void query(String sql, List<?> params, Closure closure) throws SQLException;
public void query(String sql, Map params, Closure closure) throws SQLException;
public void query(Map params, String sql, Closure closure) throws SQLException;
public void query(GString gstring, Closure closure) throws SQLException;

// Query with metadata callback
public void query(String sql, Closure metaClosure, Closure rowClosure) throws SQLException;
public void query(String sql, List<?> params, Closure metaClosure, Closure rowClosure) throws SQLException;

Example:

// Basic query with closure
sql.query('SELECT * FROM users') { resultSet ->
    while (resultSet.next()) {
        println resultSet.getString('name')
    }
}

// With parameters
sql.query('SELECT * FROM users WHERE age > ?', [25]) { resultSet ->
    // Process results
}

// With GString
def minAge = 25
sql.query("SELECT * FROM users WHERE age > $minAge") { resultSet ->
    // Process results  
}

// With metadata
sql.query('SELECT * FROM users', 
    { meta -> println "Columns: ${meta.columnCount}" },
    { resultSet -> /* process rows */ }
)

Collection-Style Queries

Retrieve query results as collections for easier processing with Groovy's collection methods.

// Get all rows as List<GroovyRowResult>
public List<GroovyRowResult> rows(String sql) throws SQLException;
public List<GroovyRowResult> rows(String sql, List<?> params) throws SQLException;
public List<GroovyRowResult> rows(String sql, Map params) throws SQLException;
public List<GroovyRowResult> rows(Map params, String sql) throws SQLException;
public List<GroovyRowResult> rows(GString gstring) throws SQLException;

// With paging
public List<GroovyRowResult> rows(String sql, int offset, int maxRows) throws SQLException;
public List<GroovyRowResult> rows(String sql, List<?> params, int offset, int maxRows) throws SQLException;

// With metadata callback
public List<GroovyRowResult> rows(String sql, Closure metaClosure) throws SQLException;
public List<GroovyRowResult> rows(String sql, List<?> params, Closure metaClosure) throws SQLException;

// Get single row
public GroovyRowResult firstRow(String sql) throws SQLException;
public GroovyRowResult firstRow(String sql, List<?> params) throws SQLException;
public GroovyRowResult firstRow(String sql, Map params) throws SQLException;
public GroovyRowResult firstRow(Map params, String sql) throws SQLException;
public GroovyRowResult firstRow(GString gstring) throws SQLException;

Example:

// Get all rows
def users = sql.rows('SELECT * FROM users')
users.each { user ->
    println "${user.name}: ${user.email}"
}

// With parameters
def activeUsers = sql.rows('SELECT * FROM users WHERE active = ?', [true])

// With paging (offset=10, maxRows=5)
def page = sql.rows('SELECT * FROM users ORDER BY name', 10, 5)

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

// Using collection methods
def emails = sql.rows('SELECT * FROM users')
                .findAll { it.active }
                .collect { it.email }

Data Manipulation

Execute INSERT, UPDATE, DELETE operations with various parameter binding options.

// General execution
public boolean execute(String sql) throws SQLException;
public boolean execute(String sql, List<?> params) throws SQLException;
public boolean execute(String sql, Object[] params) throws SQLException;
public boolean execute(String sql, Map params) throws SQLException;
public boolean execute(Map params, String sql) throws SQLException;
public boolean execute(GString gstring) throws SQLException;

// Execute with result processing
public void execute(String sql, Closure resultClosure) throws SQLException;
public void execute(String sql, List<?> params, Closure resultClosure) throws SQLException;
public void execute(String sql, List<?> params, Closure metaClosure, Closure resultClosure) throws SQLException;
public void execute(String sql, Map params, Closure resultClosure) throws SQLException;
public void execute(Map params, String sql, Closure resultClosure) throws SQLException;
public void execute(String sql, Map params, Closure metaClosure, Closure resultClosure) throws SQLException;
public void execute(Map params, String sql, Closure metaClosure, Closure resultClosure) throws SQLException;

// Update operations (returns row count)
public int executeUpdate(String sql) throws SQLException;
public int executeUpdate(String sql, List<?> params) throws SQLException;
public int executeUpdate(String sql, Object[] params) throws SQLException;
public int executeUpdate(String sql, Map params) throws SQLException;
public int executeUpdate(Map params, String sql) throws SQLException;
public int executeUpdate(GString gstring) throws SQLException;

// Insert with generated keys
public List<List<Object>> executeInsert(String sql) throws SQLException;
public List<List<Object>> executeInsert(String sql, List<?> params) throws SQLException;
public List<List<Object>> executeInsert(String sql, Object[] params) throws SQLException;
public List<GroovyRowResult> executeInsert(String sql, List<?> params, List<String> keyColumnNames) throws SQLException;
public List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames) throws SQLException;
public List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames, Object[] params) throws SQLException;
public List<List<Object>> executeInsert(String sql, Map params) throws SQLException;
public List<List<Object>> executeInsert(Map params, String sql) throws SQLException;
public List<GroovyRowResult> executeInsert(Map params, String sql, List<String> keyColumnNames) throws SQLException;
public List<List<Object>> executeInsert(GString gstring) throws SQLException;
public List<GroovyRowResult> executeInsert(GString gstring, List<String> keyColumnNames) throws SQLException;

Example:

// DDL execution
sql.execute '''
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name VARCHAR(100),
        price DECIMAL(10,2)
    )
'''

// Insert with parameters
def rowsAffected = sql.executeUpdate(
    'INSERT INTO products (name, price) VALUES (?, ?)', 
    ['Laptop', 999.99]
)

// Insert with GString
def name = 'Phone'
def price = 699.99
sql.executeUpdate "INSERT INTO products (name, price) VALUES ($name, $price)"

// Insert with generated keys
def keys = sql.executeInsert(
    'INSERT INTO products (name, price) VALUES (?, ?)',
    ['Tablet', 499.99]
)
println "Generated key: ${keys[0][0]}"

// Update
def updated = sql.executeUpdate(
    'UPDATE products SET price = ? WHERE name = ?',
    [899.99, 'Laptop']
)
println "Updated $updated rows"

// Delete
def deleted = sql.executeUpdate(
    'DELETE FROM products WHERE price < ?',
    [500.00]
)

Resource Management

Manage database resources with automatic cleanup and connection caching.

// Resource cleanup
public void close() throws SQLException;

// Connection caching
public Object cacheConnection(Closure closure) throws SQLException;

// Statement caching and configuration
public Object cacheStatements(Closure closure) throws SQLException;
public Object withStatement(Closure configureStatement) throws SQLException;
public Object withCleanupStatement(Closure cleanupStatement) throws SQLException;

// Configuration
public boolean isCacheStatements();
public void setCacheStatements(boolean cacheStatements);
public boolean isCacheNamedQueries();
public void setCacheNamedQueries(boolean cacheNamedQueries);
public boolean isEnableNamedQueries();
public void setEnableNamedQueries(boolean enableNamedQueries);

// Access underlying resources
public Connection getConnection() throws SQLException;
public DataSource getDataSource();
public int getUpdateCount();

Example:

// Manual resource management
def sql = Sql.newInstance(url, user, pass, driver)
try {
    // Database operations
    sql.execute('CREATE TABLE temp (id INT)')
} finally {
    sql.close()
}

// Automatic resource management with caching
sql.cacheConnection { 
    // Multiple operations share same connection
    sql.execute('INSERT INTO users VALUES (1, "Alice")')
    sql.execute('INSERT INTO users VALUES (2, "Bob")')
    def users = sql.rows('SELECT * FROM users')
    // Connection automatically closed after block
}

// Statement configuration
sql.withStatement { stmt ->
    stmt.queryTimeout = 30
    stmt.fetchSize = 1000
} {
    // Operations use configured statement
    def results = sql.rows('SELECT * FROM large_table')
}

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