or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

batch-operations.mdcore-operations.mddataset.mdindex.mdparameters.mdresult-processing.mdstored-procedures.mdtransactions.md
tile.json

tessl/maven-org-apache-groovy--groovy-sql

Higher-level abstraction over Java's JDBC technology providing simplified database operations with resource management

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

To install, run

npx @tessl/cli install tessl/maven-org-apache-groovy--groovy-sql@5.0.0

index.mddocs/

Groovy SQL

Groovy SQL provides a higher-level abstraction over Java's JDBC technology for relational database operations. It features a fluent facade that greatly simplifies connection management, statement execution, and result set processing while offering Groovy-specific enhancements like GString parameter binding, closure-based iteration, and treating result sets as collections of maps.

Package Information

  • Package Name: groovy-sql
  • Package Type: Maven (JAR)
  • Language: Java/Groovy
  • Installation: 
    <dependency>
  <groupId>org.apache.groovy</groupId>
  <artifactId>groovy-sql</artifactId>
  <version>5.0.0</version>
</dependency>
  • Gradle: implementation 'org.apache.groovy:groovy-sql:5.0.0'

Core Imports

import groovy.sql.Sql;
import groovy.sql.DataSet;
import groovy.sql.GroovyRowResult;

For parameter types:

import groovy.sql.InParameter;
import groovy.sql.OutParameter;
import groovy.sql.InOutParameter;

Basic Usage

import groovy.sql.Sql

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

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

// Insert data using GString syntax
def name = 'Alice'
def email = 'alice@example.com'
sql.execute "INSERT INTO users (id, name, email) VALUES (1, $name, $email)"

// Query with closure iteration
sql.eachRow('SELECT * FROM users') { row ->
    println "${row.name}: ${row.email}"
}

// Clean up
sql.close()

// Method 2: Automatic resource management (recommended)
Sql.withInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver') { sql ->
    // Create table
    sql.execute '''
        CREATE TABLE products (
            id INTEGER PRIMARY KEY,
            name VARCHAR(50),
            price DECIMAL(10,2)
        )
    '''
    
    // Insert with parameters
    sql.execute 'INSERT INTO products (id, name, price) VALUES (?, ?, ?)', 
                [1, 'Laptop', 999.99]
    
    // Get all rows as collection
    def products = sql.rows('SELECT * FROM products WHERE price > ?', [500])
    products.each { product ->
        println "${product.name}: \$${product.price}"
    }
    // Connection automatically closed
}

Architecture

Groovy SQL is built around several key components:

  • Sql Class: Main facade providing simplified JDBC interface with automatic resource management
  • Connection Management: Factory methods for DataSource, Connection, or JDBC URL-based connections
  • Parameter Binding: Support for positional (?), named (:name), and GString ($variable) parameters
  • Result Processing: GroovyRowResult provides Map-like access to database rows with property-style column access
  • Transaction Support: Automatic transaction management with rollback capabilities
  • Batch Operations: Automatic batching with configurable batch sizes for improved performance
  • DataSet API: POJO-style database table access with filtering and sorting capabilities

Capabilities

Core Database Operations

Primary database interaction functionality including connection management, query execution, data manipulation, and transaction handling. This forms the foundation of all database operations.

// Connection factory methods
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, String user, String password) throws SQLException;
public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException, ClassNotFoundException;
public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException;
public static Sql newInstance(Map<String, Object> args) throws SQLException, ClassNotFoundException;

// Resource management factory methods (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;

// Constructors  
public Sql(DataSource dataSource);
public Sql(Connection connection);
public Sql(Sql parent);

// Query execution
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(GString gstring, Closure closure) throws SQLException;
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(GString gstring) throws SQLException;
public GroovyRowResult firstRow(String sql) throws SQLException;
public GroovyRowResult firstRow(String sql, List<?> params) throws SQLException;
public GroovyRowResult firstRow(GString gstring) throws SQLException;

// Data manipulation
public boolean execute(String sql) throws SQLException;
public boolean execute(String sql, List<?> params) throws SQLException;
public boolean execute(String sql, Map params) throws SQLException;
public boolean execute(GString gstring) throws SQLException;
public int executeUpdate(String sql) throws SQLException;
public int executeUpdate(String sql, List<?> params) throws SQLException;
public int executeUpdate(String sql, Map params) throws SQLException;
public int executeUpdate(GString gstring) throws SQLException;
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, Map params) throws SQLException;
public List<List<Object>> executeInsert(GString gstring) throws SQLException;

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

Core Operations

Result Processing and Row Handling

Advanced result set processing with Groovy-enhanced row access, metadata handling, and collection-style operations on database results.

public void eachRow(String sql, Closure closure) throws SQLException;
public void eachRow(String sql, List<?> params, Closure closure) throws SQLException;
public void eachRow(String sql, int offset, int maxRows, Closure closure) throws SQLException;
public void eachRow(String sql, Closure metaClosure, Closure rowClosure) throws SQLException;

// GroovyRowResult - represents a database row
public class GroovyRowResult extends GroovyObjectSupport implements Map<String, Object> {
    public Object getProperty(String property);
    public Object getAt(int index);
    public Object get(Object property);
    public int size();
    public Set<String> keySet();
    public Collection<Object> values();
}

Result Processing

Transaction Management

Comprehensive transaction support with automatic resource management, rollback capabilities, and connection caching for improved performance.

public void withTransaction(Closure closure) throws SQLException;
public void commit() throws SQLException;
public void rollback() throws SQLException;
public void cacheConnection(Closure closure) throws SQLException;

// Transaction properties
public int getResultSetType();
public void setResultSetType(int resultSetType);
public int getResultSetConcurrency();
public void setResultSetConcurrency(int resultSetConcurrency);
public int getResultSetHoldability();
public void setResultSetHoldability(int resultSetHoldability);

Transaction Management

Parameter Binding and Types

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

// Parameter interfaces
public interface InParameter {
    int getType();
    Object getValue();
}

public interface OutParameter {
    int getType();
}

public interface InOutParameter extends InParameter, OutParameter {}

// Parameter factory methods
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 constants
public static final OutParameter INTEGER;
public static final OutParameter VARCHAR;
public static final OutParameter TIMESTAMP;
// ... all JDBC types available

Parameters and Types

Stored Procedures

Complete stored procedure support with input/output parameters, result set processing, and CallableStatement wrapper functionality.

public int call(String sql) throws SQLException;
public int call(String sql, List<?> params) throws SQLException;
public int call(GString gstring) throws SQLException;
public void call(String sql, List<?> params, Closure closure) throws SQLException;
public void call(GString gstring, Closure closure) throws SQLException;
public List<GroovyRowResult> callWithRows(String sql, List<?> params, Closure closure) throws SQLException;
public List<GroovyRowResult> callWithRows(GString gstring, Closure closure) throws SQLException;
public List<List<GroovyRowResult>> callWithAllRows(String sql, List<?> params, Closure closure) throws SQLException;
public List<List<GroovyRowResult>> callWithAllRows(GString gstring, Closure closure) throws SQLException;

Stored Procedures

Batch Operations

High-performance batch processing with automatic batching, configurable batch sizes, and support for both Statement and PreparedStatement batching.

public int[] withBatch(Closure closure) throws SQLException;
public int[] withBatch(int batchSize, Closure closure) throws SQLException;
public int[] withBatch(String sql, Closure closure) throws SQLException;
public int[] withBatch(int batchSize, String sql, Closure closure) throws SQLException;
public boolean isWithinBatch();

// Batch wrapper classes
public class BatchingStatementWrapper extends GroovyObjectSupport implements AutoCloseable {
    public void addBatch(String sql) throws SQLException;
    public int[] executeBatch() throws SQLException;
    public void clearBatch() throws SQLException;
}

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

Batch Operations

DataSet API

Object-oriented table access providing POJO-style database operations with filtering, sorting, and view creation capabilities.

public class DataSet extends Sql {
    public DataSet(Sql sql, String table);
    public DataSet(Sql sql, Class type);
    
    public void add(Map<String, Object> map) throws SQLException;
    public DataSet findAll(Closure where);
    public DataSet sort(Closure sort);
    public DataSet reverse();
    public DataSet createView(Closure criteria);
    
    public void each(Closure closure) throws SQLException;
    public void each(int offset, int maxRows, Closure closure) throws SQLException;
    public List<GroovyRowResult> rows() throws SQLException;
    public List<GroovyRowResult> rows(int offset, int maxRows) throws SQLException;
    public GroovyRowResult firstRow() throws SQLException;
}

// DataSet factory methods in Sql class
public DataSet dataSet(String table);
public DataSet dataSet(Class<?> type);

DataSet API

Types

// Core result type
public class GroovyRowResult extends GroovyObjectSupport implements Map<String, Object> {
    public Object getProperty(String property);
    public Object getAt(int index);
    public Object get(Object property);
    public Object put(String key, Object value);
    public boolean containsKey(Object key);
    public boolean containsValue(Object value);
    public int size();
    public boolean isEmpty();
    public Set<String> keySet();
    public Collection<Object> values();
    public Set<Map.Entry<String, Object>> entrySet();
}

// Enhanced ResultSet interface
public interface GroovyResultSet extends GroovyObject, ResultSet {
    Object getAt(int index);
    Object getAt(String columnName);
    void putAt(int index, Object newValue);
    void putAt(String columnName, Object newValue);
    void add(Map values);
    void eachRow(Closure closure);
}

// Internal parameter holder
public class SqlWithParams {
    public String getSql();
    public List<Object> getParams();
}

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

// Additional important properties and configuration methods
public DataSource getDataSource();
public Connection getConnection();
public int getUpdateCount();
public boolean isCacheStatements();
public void setCacheStatements(boolean cacheStatements);
public boolean isCacheNamedQueries();
public void setCacheNamedQueries(boolean cacheNamedQueries);
public boolean isEnableNamedQueries();
public void setEnableNamedQueries(boolean enableNamedQueries);

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

// Static parameter factory methods
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);
public static ExpandedVariable expand(Object object);