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

parameters.mddocs/

Parameter Binding and Types

Advanced parameter binding with typed parameters for stored procedures, GString expression support, and named parameter processing.

Capabilities

Parameter Interfaces

Define typed parameters for stored procedure input/output operations.

// Input parameter interface
public interface InParameter {
    int getType();    // JDBC type constant
    Object getValue(); // Parameter value
}

// Output parameter interface
public interface OutParameter {
    int getType();    // JDBC type constant
}

// Bidirectional parameter interface
public interface InOutParameter extends InParameter, OutParameter {}

// ResultSet output parameter interface
public interface ResultSetOutParameter extends OutParameter {}

Parameter Factory Methods

Create typed parameters using static factory methods for all JDBC types.

// Generic parameter factories
public static InParameter in(int type, Object value);
public static OutParameter out(int type);
public static InOutParameter inout(InParameter in);
public static ResultSetOutParameter resultSet(int type);

// Type-specific InParameter factories
public static InParameter ARRAY(Object value);
public static InParameter BIGINT(Object value);
public static InParameter BINARY(Object value);
public static InParameter BIT(Object value);
public static InParameter BLOB(Object value);
public static InParameter BOOLEAN(Object value);
public static InParameter CHAR(Object value);
public static InParameter CLOB(Object value);
public static InParameter DATE(Object value);
public static InParameter DECIMAL(Object value);
public static InParameter DOUBLE(Object value);
public static InParameter FLOAT(Object value);
public static InParameter INTEGER(Object value);
public static InParameter LONGVARBINARY(Object value);
public static InParameter LONGVARCHAR(Object value);
public static InParameter NUMERIC(Object value);
public static InParameter OTHER(Object value);
public static InParameter REAL(Object value);
public static InParameter SMALLINT(Object value);
public static InParameter TIME(Object value);
public static InParameter TIMESTAMP(Object value);
public static InParameter TINYINT(Object value);
public static InParameter VARBINARY(Object value);
public static InParameter VARCHAR(Object value);

Parameter Type Constants

Pre-defined OutParameter constants for all JDBC types.

// OutParameter constants
public static final OutParameter ARRAY;
public static final OutParameter BIGINT;
public static final OutParameter BINARY;
public static final OutParameter BIT;
public static final OutParameter BLOB;
public static final OutParameter BOOLEAN;
public static final OutParameter CHAR;
public static final OutParameter CLOB;
public static final OutParameter DATALINK;
public static final OutParameter DATE;
public static final OutParameter DECIMAL;
public static final OutParameter DISTINCT;
public static final OutParameter DOUBLE;
public static final OutParameter FLOAT;
public static final OutParameter INTEGER;
public static final OutParameter JAVA_OBJECT;
public static final OutParameter LONGVARBINARY;
public static final OutParameter LONGVARCHAR;
public static final OutParameter NULL;
public static final OutParameter NUMERIC;
public static final OutParameter OTHER;
public static final OutParameter REAL;
public static final OutParameter REF;
public static final OutParameter SMALLINT;
public static final OutParameter STRUCT;
public static final OutParameter TIME;
public static final OutParameter TIMESTAMP;
public static final OutParameter TINYINT;
public static final OutParameter VARBINARY;
public static final OutParameter VARCHAR;

Example:

import static groovy.sql.Sql.*

// Using type constants for output parameters
def result = sql.call('CALL get_user_stats(?, ?)', [userId, INTEGER])
println "User count: ${result[0][1]}"

// Using factory methods for input parameters
def params = [
    VARCHAR('John'),
    INTEGER(25),
    TIMESTAMP(new Date()),
    BOOLEAN(true)
]
sql.call('CALL create_user(?, ?, ?, ?)', params)

// Mixed input/output parameters
def mixedParams = [
    VARCHAR('admin'),           // input
    out(INTEGER),              // output
    inout(VARCHAR('pending'))  // input/output
]
def results = sql.call('CALL process_request(?, ?, ?)', mixedParams)

GString Parameter Binding

Use Groovy GString expressions for dynamic parameter binding with automatic type inference.

Example:

// Simple GString binding
def userId = 123
def status = 'active'
sql.eachRow("SELECT * FROM users WHERE id = $userId AND status = $status") { row ->
    println row.name
}

// Complex expressions
def minDate = new Date() - 30
def categories = ['electronics', 'books', 'clothing']
sql.eachRow("SELECT * FROM products WHERE created_date > $minDate AND category IN (${categories.join(',')})") { row ->
    println "${row.name}: ${row.category}"
}

// Null handling
def optionalFilter = condition ? someValue : null
sql.eachRow("SELECT * FROM items WHERE ${optionalFilter ? "category = $optionalFilter" : '1=1'}") { row ->
    println row
}

Named Parameter Processing

Use named parameters with :name syntax for improved readability and maintainability.

// Named parameter processing (internal)
public SqlWithParams checkForNamedParams(String sql, List<?> params);
protected SqlWithParams buildSqlWithIndexedProps(String sql);

// Configuration
public boolean isEnableNamedQueries();
public void setEnableNamedQueries(boolean enableNamedQueries);

Example:

// Enable named parameters
sql.enableNamedQueries = true

// Named parameter syntax
def params = [
    userId: 123,
    status: 'active',
    minAge: 18,
    department: 'Engineering'
]

// Use named parameters in queries
sql.eachRow('SELECT * FROM users WHERE id = :userId AND status = :status', params) { row ->
    println row.name
}

// Complex named query
def searchCriteria = [
    firstName: 'John',
    lastName: 'Smith',
    minSalary: 50000,
    departments: ['IT', 'Engineering', 'Sales']
]

sql.eachRow('''
    SELECT * FROM employees 
    WHERE first_name LIKE :firstName 
    AND last_name LIKE :lastName
    AND salary >= :minSalary
    AND department IN (:departments)
''', searchCriteria) { row ->
    println "${row.first_name} ${row.last_name} - ${row.department}"
}

// Property-based named parameters (e.g., :user.id, :user.name)
def user = [id: 123, name: 'Alice', email: 'alice@example.com']
sql.executeUpdate('INSERT INTO users (id, name, email) VALUES (:user.id, :user.name, :user.email)', [user: user])

Variable Expansion

Control how variables are expanded into SQL strings versus parameter placeholders.

// Variable expansion interface
public interface ExpandedVariable {
    Object getObject();
}

// Factory method
public static ExpandedVariable expand(Object object);

Example:

import static groovy.sql.Sql.expand

// Normal parameter binding (safe from SQL injection)
def tableName = 'users'
def userId = 123
// This won't work - table names can't be parameterized
// sql.rows('SELECT * FROM ? WHERE id = ?', [tableName, userId])

// Use expand() for dynamic SQL parts (be careful with user input!)
def safeSql = "SELECT * FROM ${expand(tableName)} WHERE id = ?"
sql.eachRow(safeSql, [userId]) { row ->
    println row.name
}

// Dynamic ORDER BY clause
def sortColumn = 'name'  // validated against allowed columns
def sortOrder = 'ASC'    // validated against ASC/DESC
def orderBy = "${expand(sortColumn)} ${expand(sortOrder)}"
sql.eachRow("SELECT * FROM users ORDER BY $orderBy") { row ->
    println row.name
}

// Building dynamic WHERE clauses
def buildWhereClause(filters) {
    def conditions = []
    def params = []
    
    if (filters.name) {
        conditions << 'name LIKE ?'
        params << "%${filters.name}%"
    }
    if (filters.minAge) {
        conditions << 'age >= ?'
        params << filters.minAge
    }
    if (filters.department) {
        conditions << 'department = ?'
        params << filters.department
    }
    
    def whereClause = conditions ? "WHERE ${conditions.join(' AND ')}" : ''
    return [whereClause: expand(whereClause), params: params]
}

def filters = [name: 'John', minAge: 25]
def query = buildWhereClause(filters)
sql.eachRow("SELECT * FROM users ${query.whereClause}", query.params) { row ->
    println row
}

Type Conversion and Validation

Handle type conversion and parameter validation for database operations.

Example:

import java.sql.Types
import java.sql.Timestamp
import java.text.SimpleDateFormat

// Automatic type conversion examples
def params = [
    // String to appropriate SQL type
    stringParam: 'Hello World',
    
    // Number conversion
    intParam: 42,
    longParam: 123456789L,
    doubleParam: 3.14159,
    bigDecimalParam: new BigDecimal('999.99'),
    
    // Date/time conversion
    dateParam: new Date(),
    timestampParam: new Timestamp(System.currentTimeMillis()),
    
    // Boolean conversion
    boolParam: true,
    
    // Null handling
    nullParam: null,
    
    // Binary data
    blobParam: 'binary data'.bytes
]

sql.executeUpdate('''
    INSERT INTO test_table (
        string_col, int_col, long_col, double_col, decimal_col,
        date_col, timestamp_col, bool_col, null_col, blob_col
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
''', params.values() as List)

// Custom type handling
def customDateFormat = new SimpleDateFormat('yyyy-MM-dd')
def dateString = '2024-01-15'
def parsedDate = customDateFormat.parse(dateString)
sql.executeUpdate('INSERT INTO events (event_date) VALUES (?)', [TIMESTAMP(parsedDate)])

// Validate parameters before use
def validateAndExecute(sql, query, params) {
    params.each { param ->
        if (param instanceof String && param.contains(';')) {
            throw new IllegalArgumentException('Potentially unsafe parameter detected')
        }
    }
    return sql.executeUpdate(query, params)
}

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