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

DataSet Operations

Name: tessl/maven-org-codehaus-groovy--groovy-sql
Author: tessl

DataSet provides an enhanced interface for database operations using POGO (Plain Old Groovy Object) field-based queries. It extends the Sql class with fluent API methods for filtering, sorting, and data manipulation using closure-based syntax.

DataSet Creation

From Table Name

Create a DataSet for a specific database table:

DataSet(Sql sql, String table)

From POGO Class

Create a DataSet based on a Groovy class structure:

DataSet(Sql sql, Class type)

Factory Methods from Sql

Create DataSet instances from existing Sql connections:

// From Sql instance
DataSet dataSet(String table)
DataSet dataSet(Class<?> type)

Data Manipulation

Adding Records

Insert new records using map-based syntax:

void add(Map<String, Object> map) throws SQLException

Query Operations

Filtering

Create filtered views using closure-based WHERE conditions:

DataSet findAll(Closure where)

Sorting

Sort results using closure-based ORDER BY conditions:

DataSet sort(Closure sort)

Reverse Order

Reverse the order of results (only valid after sort):

DataSet reverse()

Create Views

Create filtered views with closure criteria:

DataSet createView(Closure criteria)

Result Iteration

Row Iteration

Iterate over all rows in the DataSet:

void each(Closure closure) throws SQLException
void each(int offset, int maxRows, Closure closure) throws SQLException

Collect All Rows

Retrieve all rows as a list:

List rows() throws SQLException
List rows(int offset, int maxRows) throws SQLException

First Row

Get the first row only:

Object firstRow() throws SQLException

Query Information

Generated SQL

Access the underlying SQL query:

String getSql()

Query Parameters

Access the parameters for the generated query:

List<Object> getParameters()

Inherited Methods

DataSet inherits all methods from the Sql class, including:

Batch Operations

int[] withBatch(Closure closure) throws SQLException
int[] withBatch(int batchSize, Closure closure) throws SQLException

Transaction Management

void withTransaction(Closure closure) throws SQLException
void commit() throws SQLException
void rollback() throws SQLException

Connection Management

void cacheConnection(Closure closure) throws SQLException
void close()

Usage Examples

Basic DataSet Operations

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

// Create DataSet for users table
def users = sql.dataSet("users")

// Add new user
users.add(name: "John Doe", email: "john@example.com", age: 30)
users.add(name: "Jane Smith", email: "jane@example.com", age: 25)

// Query all users
users.each { user ->
    println "User: ${user.name}, Age: ${user.age}"
}

Filtering and Sorting

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

// Filter users by age
def adults = users.findAll { it.age >= 18 }
adults.each { user ->
    println "Adult: ${user.name}"
}

// Sort users by name
def sortedUsers = users.sort { it.name }
sortedUsers.each { user ->
    println "User: ${user.name}"
}

// Reverse sort by age
def youngestFirst = users.sort { it.age }.reverse()
youngestFirst.each { user ->
    println "${user.name} is ${user.age}"
}

POGO-based DataSet

// Define a POGO class
class Person {
    String name
    String email
    int age
    boolean active
}

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

// Create DataSet based on POGO
def people = sql.dataSet(Person)

// Add using map syntax
people.add(name: "Alice", email: "alice@example.com", age: 28, active: true)

// Filter active people over 25
def activeSeniors = people.findAll { it.active && it.age > 25 }
activeSeniors.each { person ->
    println "Active senior: ${person.name}"
}

Complex Filtering

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

// Complex filter with multiple conditions
def recentLargeOrders = orders.findAll { 
    it.amount > 1000 && it.status == 'completed' && it.order_date > new Date() - 30
}

// Get count of matching records
def count = recentLargeOrders.rows().size()
println "Found ${count} recent large orders"

// Get first matching record
def firstOrder = recentLargeOrders.firstRow()
if (firstOrder) {
    println "Largest recent order: ${firstOrder.amount}"
}

Pagination with DataSet

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

// Get paginated results
def pageSize = 10
def offset = 0

products.sort { it.name }.each(offset, pageSize) { product ->
    println "Product: ${product.name} - \$${product.price}"
}

// Or get as list
def page1 = products.sort { it.name }.rows(0, 10)
def page2 = products.sort { it.name }.rows(10, 10)

View Creation

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

// Create a reusable view
def managers = employees.createView { it.role == 'manager' && it.active == true }

// Use the view
managers.each { manager ->
    println "Manager: ${manager.name} in ${manager.department}"
}

// Views can be further filtered
def seniorManagers = managers.findAll { it.years_experience > 5 }

Query Introspection

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

// Create a filtered dataset
def activeUsers = users.findAll { it.active == true && it.age > 21 }

// Inspect the generated SQL
println "Generated SQL: ${activeUsers.getSql()}"
println "Parameters: ${activeUsers.getParameters()}"

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

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