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

dataset.mddocs/

DataSet API

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

Capabilities

DataSet Creation

Create DataSet instances for table-based operations using either table names or POJO classes.

public class DataSet extends Sql {
    // Constructors
    public DataSet(Sql sql, String table);
    public DataSet(Sql sql, Class type);
    
    // Factory methods in Sql class
    public DataSet dataSet(String table);
    public DataSet dataSet(Class<?> type);
}

Example:

// Create DataSet from table name
def usersDataSet = sql.dataSet('users')

// Create DataSet from POJO class
class User {
    Long id
    String name
    String email
    Date created
}

def userClassDataSet = sql.dataSet(User)

// Using factory methods
def productsDs = sql.dataSet('products')
def ordersDs = sql.dataSet(Order)

Data Insertion

Add new records to the database table using Map-based data insertion.

// Data manipulation
public void add(Map<String, Object> map) throws SQLException;

Example:

def usersDs = sql.dataSet('users')

// Add single record
usersDs.add([
    name: 'Alice Johnson',
    email: 'alice@example.com',
    department: 'Engineering',
    salary: 75000
])

// Add multiple records
def newUsers = [
    [name: 'Bob Smith', email: 'bob@example.com', department: 'Sales', salary: 65000],
    [name: 'Carol Davis', email: 'carol@example.com', department: 'Marketing', salary: 70000],
    [name: 'David Wilson', email: 'david@example.com', department: 'Engineering', salary: 80000]
]

newUsers.each { userData ->
    usersDs.add(userData)
}

// Add with automatic timestamp
usersDs.add([
    name: 'Eve Brown',
    email: 'eve@example.com',
    department: 'HR',
    salary: 60000,
    created_date: new Date()
])

Filtering and Querying

Filter table data using closure-based criteria for flexible query construction.

// Query methods
public DataSet findAll(Closure where);
public DataSet createView(Closure criteria);

Example:

def usersDs = sql.dataSet('users')

// Filter by single condition
def engineeringUsers = usersDs.findAll { it.department == 'Engineering' }

// Filter by multiple conditions
def highEarners = usersDs.findAll { 
    it.salary > 70000 && it.department in ['Engineering', 'Sales'] 
}

// Filter with date conditions
def recentUsers = usersDs.findAll { 
    it.created_date > (new Date() - 30) 
}

// Complex filtering with null checks
def activeUsers = usersDs.findAll {
    it.active != false && it.email?.contains('@') && it.salary != null
}

// Create reusable filtered view
def seniorEngineers = usersDs.createView { 
    it.department == 'Engineering' && it.salary > 90000 
}

Sorting and Ordering

Sort DataSet results using closure-based sorting criteria.

// Sorting methods
public DataSet sort(Closure sort);
public DataSet reverse();

Example:

def usersDs = sql.dataSet('users')

// Sort by single field
def usersByName = usersDs.sort { it.name }

// Sort by multiple fields
def usersBySalaryThenName = usersDs.sort { a, b ->
    def salaryCompare = b.salary <=> a.salary  // Descending salary
    salaryCompare != 0 ? salaryCompare : a.name <=> b.name  // Then ascending name
}

// Sort with null handling
def usersSorted = usersDs.sort { a, b ->
    if (a.salary == null && b.salary == null) return 0
    if (a.salary == null) return 1
    if (b.salary == null) return -1
    return a.salary <=> b.salary
}

// Reverse sort order
def usersReversed = usersDs.sort { it.name }.reverse()

// Complex sorting example
def prioritizedUsers = usersDs.sort { a, b ->
    // Priority: active status, then department, then salary (desc), then name
    def activeCompare = (b.active ? 1 : 0) <=> (a.active ? 1 : 0)
    if (activeCompare != 0) return activeCompare
    
    def deptCompare = a.department <=> b.department
    if (deptCompare != 0) return deptCompare
    
    def salaryCompare = (b.salary ?: 0) <=> (a.salary ?: 0)
    if (salaryCompare != 0) return salaryCompare
    
    return a.name <=> b.name
}

Result Processing

Process DataSet results using various iteration and collection methods.

// Row processing methods
public void each(Closure closure) throws SQLException;
public void each(int offset, int maxRows, Closure closure) throws SQLException;

// Collection methods
public List<GroovyRowResult> rows() throws SQLException;
public List<GroovyRowResult> rows(int offset, int maxRows) throws SQLException;
public GroovyRowResult firstRow() throws SQLException;

Example:

def usersDs = sql.dataSet('users')

// Process all rows
usersDs.findAll { it.active }.each { user ->
    println "Active user: ${user.name} (${user.email})"
}

// Process with paging
usersDs.sort { it.name }.each(10, 5) { user ->
    // Skip first 10, process next 5
    println "User: ${user.name}"
}

// Get results as collection
def allUsers = usersDs.rows()
def userNames = allUsers.collect { it.name }
def totalSalary = allUsers.sum { it.salary ?: 0 }

// Get paged results
def page2Users = usersDs.sort { it.name }.rows(20, 10)  // Skip 20, take 10

// Get first matching record
def firstAdmin = usersDs.findAll { it.role == 'admin' }.firstRow()
if (firstAdmin) {
    println "First admin: ${firstAdmin.name}"
}

// Combine filtering, sorting, and collection operations
def summary = usersDs
    .findAll { it.department == 'Engineering' }
    .sort { it.salary }
    .rows()
    .groupBy { it.level }
    .collectEntries { level, engineers ->
        [level, [
            count: engineers.size(),
            avgSalary: engineers.sum { it.salary } / engineers.size(),
            names: engineers.collect { it.name }
        ]]
    }

SQL Generation and Inspection

Access the generated SQL and parameters for debugging and optimization purposes.

// SQL inspection methods
public String getSql();
public List<Object> getParameters();

Example:

def usersDs = sql.dataSet('users')

// Create a filtered dataset
def filteredDs = usersDs
    .findAll { it.department == 'Engineering' && it.salary > 70000 }
    .sort { it.name }

// Inspect generated SQL
println "Generated SQL: ${filteredDs.sql}"
println "Parameters: ${filteredDs.parameters}"

// This helps with debugging and query optimization
def complexFilter = usersDs.findAll { 
    it.active && 
    it.department in ['Engineering', 'Sales'] && 
    it.created_date > (new Date() - 90) &&
    it.salary > 50000 
}

println "Complex query SQL:"
println complexFilter.sql
println "Parameters: ${complexFilter.parameters}"

// Use for query performance analysis
def measureQueryPerformance(dataSet) {
    println "Executing query: ${dataSet.sql}"
    def start = System.currentTimeMillis()
    
    def results = dataSet.rows()
    
    def end = System.currentTimeMillis()
    println "Query executed in ${end - start}ms, returned ${results.size()} rows"
    return results
}

measureQueryPerformance(filteredDs)

Batch Operations with DataSet

Combine DataSet functionality with batch operations for improved performance.

// Batch operations
public Object withBatch(Closure closure) throws SQLException;
public Object withBatch(int batchSize, Closure closure) throws SQLException;

Example:

def usersDs = sql.dataSet('users')
def logsDs = sql.dataSet('user_logs')

// Batch insert multiple users
def newUsers = [
    [name: 'User1', email: 'user1@example.com', department: 'IT'],
    [name: 'User2', email: 'user2@example.com', department: 'Finance'],
    [name: 'User3', email: 'user3@example.com', department: 'HR']
]

usersDs.withBatch(10) {
    newUsers.each { userData ->
        usersDs.add(userData)
    }
}

// Batch operations across multiple DataSets
sql.withTransaction {
    usersDs.withBatch(50) {
        csvData.each { row ->
            // Add user
            usersDs.add([
                name: row.name,
                email: row.email,
                department: row.department
            ])
            
            // Add corresponding log entry
            logsDs.add([
                user_email: row.email,
                action: 'user_created',
                timestamp: new Date()
            ])
        }
    }
}

Advanced DataSet Patterns

Implement advanced patterns using DataSet for complex database operations.

Example:

// DataSet inheritance for domain-specific operations
class UserDataSet extends DataSet {
    UserDataSet(Sql sql) {
        super(sql, 'users')
    }
    
    def getActiveUsers() {
        return findAll { it.active == true }
    }
    
    def getUsersByDepartment(department) {
        return findAll { it.department == department }
    }
    
    def getHighEarners(threshold = 100000) {
        return findAll { it.salary > threshold }
    }
}

def userDs = new UserDataSet(sql)

// Use domain-specific methods
def activeEngineers = userDs.getActiveUsers()
    .findAll { it.department == 'Engineering' }

def topEarners = userDs.getHighEarners(120000)
    .sort { -it.salary }  // Descending order

// DataSet composition for complex queries
def buildUserReport(department, minSalary, includeInactive = false) {
    def baseQuery = sql.dataSet('users')
    
    if (!includeInactive) {
        baseQuery = baseQuery.findAll { it.active }
    }
    
    if (department) {
        baseQuery = baseQuery.findAll { it.department == department }
    }
    
    if (minSalary) {
        baseQuery = baseQuery.findAll { it.salary >= minSalary }
    }
    
    return baseQuery.sort { it.name }
}

// Generate different reports
def engineeringReport = buildUserReport('Engineering', 70000)
def allHighEarnersReport = buildUserReport(null, 100000, true)

// DataSet with joins (simulated through multiple queries)
def getUsersWithOrderCounts() {
    def users = sql.dataSet('users').rows()
    def orders = sql.dataSet('orders').rows()
    
    def orderCounts = orders.groupBy { it.user_id }
        .collectEntries { userId, userOrders -> 
            [userId, userOrders.size()] 
        }
    
    return users.collect { user ->
        user + [order_count: orderCounts[user.id] ?: 0]
    }
}

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