Groovy SQL – Connect and Query Databases with 10+ Examples

Groovy SQL with 10+ examples. Connect to H2, MySQL, PostgreSQL using groovy.sql.Sql, run queries with eachRow, rows(), firstRow().

“The best tool for managing data is a well-written query.”

C.J. Date, Database Relational Model

Last Updated: March 2026 | Tested on: Groovy 5.x, Java 17+ | Difficulty: Intermediate | Reading Time: 18 minutes

If you have ever used JDBC in Java, you know how painful it can be. Open a connection, create a statement, execute a query, iterate through a ResultSet, close everything in a finally block, and handle half a dozen checked exceptions along the way. It is verbose, error-prone, and frustrating.

Groovy fixes all of that with groovy.sql.Sql. This single class wraps JDBC into a clean, closure-based API that handles resource management automatically. You write SQL, you get results, and Groovy takes care of the plumbing. No more try-catch-finally chains just to run a simple SELECT.

In this tutorial, we will walk through 10+ practical examples covering everything from connecting to databases like H2, MySQL, and PostgreSQL, to querying data with eachRow(), rows(), and firstRow(), to parameterized queries that prevent SQL injection. If you want to take your Groovy database skills further, check out Groovy SQL CRUD Operations and Groovy SQL Transactions and Connection Pooling next.

What Is groovy.sql.Sql?

The groovy.sql.Sql class is part of the Groovy standard library. It wraps JDBC and provides a high-level, Groovy-idiomatic API for database operations. According to the official Groovy database documentation, it simplifies connection management, query execution, and result processing.

Key Features:

  • Automatic resource management — connections, statements, and result sets are closed for you
  • Closure-based iteration over query results
  • Built-in support for parameterized queries (SQL injection prevention)
  • GString integration — embed parameters directly in SQL strings
  • Works with any JDBC-compliant database
  • Transaction support with withTransaction

Think of groovy.sql.Sql as a lightweight wrapper that sits between raw JDBC and a full ORM like Hibernate. It does not map objects to tables or generate SQL for you, but it removes all the boilerplate that makes JDBC painful.

The following diagram shows how groovy.sql.Sql sits between your application and the database, handling connections, queries, streaming, and transactions:

Sql.newInstance(url,
user, pass, driver)

new Sql(dataSource)

sql.execute()

sql.rows()

sql.firstRow()

sql.eachRow() { }

sql.withTransaction { }

Groovy
Application

groovy.sql.Sql
(facade class)

Data Source

JDBC
DriverManager

Connection Pool
(HikariCP, DBCP)

Database
H2 / MySQL /
PostgreSQL

DDL & DML
CREATE, INSERT,
UPDATE, DELETE

Query
SELECT → List of Maps

Single Row
SELECT → GroovyRowResult

Streaming
row-by-row closure

Transaction
auto-commit off
auto-rollback on error

Groovy SQL – groovy.sql.Sql Architecture

Setting Up Your Database

For all examples in this tutorial, we will use H2 — an embedded, in-memory Java database that requires zero configuration. This lets you follow along without installing MySQL or PostgreSQL. Later, we will show connection strings for other databases too.

Adding the H2 Dependency

If you are using Grape (Groovy’s built-in dependency manager), just add this annotation to your script:

H2 Dependency with Grape

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

// H2 in-memory database -- data lives only while the script runs
def sql = Sql.newInstance(
    'jdbc:h2:mem:testdb',    // JDBC URL
    'sa',                     // username
    '',                       // password
    'org.h2.Driver'           // JDBC driver class
)

println "Connected! Database: ${sql.connection.metaData.databaseProductName}"
println "Version: ${sql.connection.metaData.databaseProductVersion}"

sql.close()

Output

Connected! Database: H2
Version: 2.2.224 (2023-09-17)

That is all you need. No database server to install, no configuration files, no DBA to bother. H2 runs entirely inside your JVM.

10 Practical Groovy SQL Examples

Example 1: Creating Tables and Inserting Data

What we’re doing: Setting up a sample table and populating it with data using execute().

Example 1: Create Table and Insert Data

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Create a table
sql.execute '''
    CREATE TABLE employees (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        department VARCHAR(50),
        salary DECIMAL(10,2),
        hire_date DATE
    )
'''

// Insert rows
sql.execute "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Alice Johnson', 'Engineering', 95000.00, '2022-03-15')"
sql.execute "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Bob Smith', 'Marketing', 72000.00, '2021-07-01')"
sql.execute "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Charlie Brown', 'Engineering', 88000.00, '2023-01-10')"
sql.execute "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Diana Prince', 'HR', 80000.00, '2020-11-20')"
sql.execute "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Eve Wilson', 'Engineering', 102000.00, '2019-06-05')"

println "Table created and 5 rows inserted."

// Quick verification
def count = sql.firstRow("SELECT COUNT(*) AS cnt FROM employees")
println "Total employees: ${count.cnt}"

sql.close()

Output

Table created and 5 rows inserted.
Total employees: 5

What happened here: The execute() method runs any SQL statement — DDL or DML. We created a table and inserted five employees. The firstRow() method returns a single row as a GroovyRowResult, which behaves like a map. Notice how we access the count using count.cnt — Groovy maps column names to properties automatically.

Example 2: Querying with eachRow()

What we’re doing: Iterating over query results using the eachRow() method, which processes rows one at a time with a closure.

Example 2: eachRow() Iteration

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Setup table
sql.execute '''CREATE TABLE employees (id INT AUTO_INCREMENT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 'Engineering', 95000.00)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 'Marketing', 72000.00)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 'Engineering', 88000.00)"
sql.execute "INSERT INTO employees VALUES (4, 'Diana Prince', 'HR', 80000.00)"
sql.execute "INSERT INTO employees VALUES (5, 'Eve Wilson', 'Engineering', 102000.00)"

// eachRow iterates through results one by one
println "=== All Employees ==="
sql.eachRow('SELECT * FROM employees ORDER BY name') { row ->
    println "${row.id}. ${row.name} - ${row.department} (\$${row.salary})"
}

println "\n=== Engineering Only ==="
sql.eachRow("SELECT name, salary FROM employees WHERE department = 'Engineering' ORDER BY salary DESC") { row ->
    println "${row.name}: \$${row.salary}"
}

// eachRow with metadata closure
println "\n=== With Column Info ==="
sql.eachRow('SELECT name, department FROM employees') { meta ->
    println "Columns: ${(1..meta.columnCount).collect { meta.getColumnName(it) }.join(', ')}"
} { row ->
    println "  ${row.name} | ${row.department}"
}

sql.close()

Output

=== All Employees ===
1. Alice Johnson - Engineering ($95000.00)
2. Bob Smith - Marketing ($72000.00)
3. Charlie Brown - Engineering ($88000.00)
4. Diana Prince - HR ($80000.00)
5. Eve Wilson - Engineering ($102000.00)

=== Engineering Only ===
Eve Wilson: $102000.00
Alice Johnson: $95000.00
Charlie Brown: $88000.00

=== With Column Info ===
Columns: NAME, DEPARTMENT
  Alice Johnson | Engineering
  Bob Smith | Marketing
  Charlie Brown | Engineering
  Diana Prince | HR
  Eve Wilson | Engineering

What happened here: eachRow() is the workhorse for iterating over results. Each row is passed to the closure as a GroovyRowResult, and you access columns by name using dot notation. The two-closure variant gives you access to ResultSetMetaData in the first closure — useful for dynamic column discovery.

Example 3: Getting All Rows with rows()

What we’re doing: Fetching all results into a list using rows(), which lets you work with query results as a standard Groovy collection.

Example 3: rows() – Fetch All Results

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Setup
sql.execute '''CREATE TABLE employees (id INT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 'Engineering', 95000.00)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 'Marketing', 72000.00)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 'Engineering', 88000.00)"
sql.execute "INSERT INTO employees VALUES (4, 'Diana Prince', 'HR', 80000.00)"
sql.execute "INSERT INTO employees VALUES (5, 'Eve Wilson', 'Engineering', 102000.00)"

// rows() returns a List<GroovyRowResult>
def allEmployees = sql.rows('SELECT * FROM employees ORDER BY salary DESC')
println "Type: ${allEmployees.getClass().name}"
println "Count: ${allEmployees.size()}"
println "Highest paid: ${allEmployees[0].name} (\$${allEmployees[0].salary})"
println "Lowest paid: ${allEmployees[-1].name} (\$${allEmployees[-1].salary})"

// Use Groovy collection methods on the results
println "\n=== Average Salary by Department ==="
allEmployees.groupBy { it.department }.each { dept, emps ->
    def avg = emps.collect { it.salary }.sum() / emps.size()
    println "${dept}: \$${String.format('%.2f', avg)} (${emps.size()} employees)"
}

// Filter and transform with Groovy methods
def highEarners = allEmployees.findAll { it.salary > 85000 }
println "\n=== Earning > \$85K ==="
highEarners.each { println "  ${it.name}: \$${it.salary}" }

// Collect specific column as a list
def names = allEmployees.collect { it.name }
println "\nAll names: ${names}"

sql.close()

Output

Type: java.util.ArrayList
Count: 5
Highest paid: Eve Wilson ($102000.00)
Lowest paid: Bob Smith ($72000.00)

=== Average Salary by Department ===
Engineering: $95000.00 (3 employees)
Marketing: $72000.00 (1 employees)
HR: $80000.00 (1 employees)

=== Earning > $85K ===
  Eve Wilson: $102000.00
  Alice Johnson: $95000.00
  Charlie Brown: $88000.00

All names: [Eve Wilson, Alice Johnson, Charlie Brown, Diana Prince, Bob Smith]

What happened here: The rows() method loads all results into memory as a List<GroovyRowResult>. This is fantastic because you can use every Groovy collection method on the results — groupBy, findAll, collect, sort, and more. Just be careful with large result sets — rows() loads everything into memory at once.

Example 4: Single Row with firstRow()

What we’re doing: Fetching a single row using firstRow(), which is perfect for lookups and aggregate queries.

Example 4: firstRow() – Single Row Queries

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Setup
sql.execute '''CREATE TABLE employees (id INT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 'Engineering', 95000.00)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 'Marketing', 72000.00)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 'Engineering', 88000.00)"
sql.execute "INSERT INTO employees VALUES (4, 'Diana Prince', 'HR', 80000.00)"
sql.execute "INSERT INTO employees VALUES (5, 'Eve Wilson', 'Engineering', 102000.00)"

// Fetch a single row by ID
def employee = sql.firstRow("SELECT * FROM employees WHERE id = 1")
println "Employee: ${employee.name}, Dept: ${employee.department}"

// Aggregate queries
def stats = sql.firstRow('''
    SELECT
        COUNT(*) AS total,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary,
        MIN(salary) AS min_salary
    FROM employees
''')
println "\nTotal employees: ${stats.total}"
println "Average salary: \$${String.format('%.2f', stats.avg_salary)}"
println "Highest salary: \$${stats.max_salary}"
println "Lowest salary: \$${stats.min_salary}"

// Department count
def engCount = sql.firstRow("SELECT COUNT(*) AS cnt FROM employees WHERE department = 'Engineering'")
println "\nEngineering team size: ${engCount.cnt}"

// firstRow returns null if no match
def nobody = sql.firstRow("SELECT * FROM employees WHERE name = 'Nobody'")
println "Non-existent: ${nobody}"

sql.close()

Output

Employee: Alice Johnson, Dept: Engineering

Total employees: 5
Average salary: $87400.00
Highest salary: 102000.00
Lowest salary: 72000.00

Engineering team size: 3
Non-existent: null

What happened here: firstRow() returns a single GroovyRowResult or null if no rows match. It is ideal for aggregate queries (COUNT, AVG, MAX, MIN) and lookups by primary key. Unlike rows(), it only fetches one row, making it more efficient for single-result queries.

Example 5: Parameterized Queries with GString

What we’re doing: Using Groovy’s GString interpolation for safe, parameterized queries that prevent SQL injection automatically.

Example 5: GString Parameterized Queries

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Setup
sql.execute '''CREATE TABLE employees (id INT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 'Engineering', 95000.00)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 'Marketing', 72000.00)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 'Engineering', 88000.00)"
sql.execute "INSERT INTO employees VALUES (4, 'Diana Prince', 'HR', 80000.00)"
sql.execute "INSERT INTO employees VALUES (5, 'Eve Wilson', 'Engineering', 102000.00)"

// GString parameters -- Groovy converts these to prepared statements!
def dept = 'Engineering'
def minSalary = 90000

// This looks like string interpolation, but Groovy is SMART about it
// It creates a PreparedStatement with ? placeholders behind the scenes
def results = sql.rows("SELECT name, salary FROM employees WHERE department = ${dept} AND salary >= ${minSalary}")
println "Engineers earning >= \$${minSalary}:"
results.each { println "  ${it.name}: \$${it.salary}" }

// Multiple parameters
def searchName = '%son%'
def matches = sql.rows("SELECT name, department FROM employees WHERE LOWER(name) LIKE ${searchName}")
println "\nName contains 'son':"
matches.each { println "  ${it.name} (${it.department})" }

// Using a list for IN clause with parameterized query
def departments = ['Engineering', 'HR']
def deptList = departments.collect { "'${it}'" }.join(',')
def inResults = sql.rows("SELECT name, department FROM employees WHERE department IN (${departments[0]}, ${departments[1]})")
println "\nIn Engineering or HR:"
inResults.each { println "  ${it.name} - ${it.department}" }

sql.close()

Output

Engineers earning >= $90000:
  Alice Johnson: $95000.00
  Eve Wilson: $102000.00

Name contains 'son':
  Alice Johnson (Engineering)
  Eve Wilson (Engineering)

In Engineering or HR:
  Alice Johnson - Engineering
  Charlie Brown - Engineering
  Diana Prince - HR
  Eve Wilson - Engineering

What happened here: This is one of Groovy SQL’s most brilliant features. When you use a GString (double-quoted string with ${variables}), Groovy does NOT do naive string concatenation. Instead, it extracts the variables and creates a PreparedStatement with proper ? placeholders. This means you get SQL injection protection for free, without changing your coding style.

Example 6: Traditional Parameterized Queries

What we’re doing: Using the traditional ? placeholder style for parameterized queries, passing parameters as a list.

Example 6: ? Placeholder Parameters

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Setup
sql.execute '''CREATE TABLE employees (id INT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 'Engineering', 95000.00)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 'Marketing', 72000.00)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 'Engineering', 88000.00)"

// Traditional ? placeholders with parameter list
sql.eachRow('SELECT * FROM employees WHERE department = ? AND salary > ?', ['Engineering', 85000]) { row ->
    println "${row.name}: \$${row.salary}"
}

// Single parameter
println "\n=== By ID ==="
def emp = sql.firstRow('SELECT name, department FROM employees WHERE id = ?', [2])
println "Employee #2: ${emp.name} in ${emp.department}"

// Multiple rows with ? parameters
println "\n=== Salary Range ==="
def rangeResults = sql.rows('SELECT name, salary FROM employees WHERE salary BETWEEN ? AND ? ORDER BY salary',
    [70000, 96000])
rangeResults.each { println "  ${it.name}: \$${it.salary}" }

// Named parameters with a map
println "\n=== Named Parameters ==="
def params = [dept: 'Engineering', minPay: 80000]
sql.eachRow('SELECT name, salary FROM employees WHERE department = :dept AND salary >= :minPay', params) { row ->
    println "  ${row.name}: \$${row.salary}"
}

sql.close()

Output

Alice Johnson: $95000.00
Charlie Brown: $88000.00

=== By ID ===
Employee #2: Bob Smith in Marketing

=== Salary Range ===
  Bob Smith: $72000.00
  Charlie Brown: $88000.00
  Alice Johnson: $95000.00

=== Named Parameters ===
  Alice Johnson: $95000.00
  Charlie Brown: $88000.00

What happened here: Groovy SQL supports three parameter styles: GString interpolation (Example 5), traditional ? placeholders with a list, and named parameters with :paramName and a map. All three use prepared statements under the hood, keeping your queries safe from SQL injection.

Example 7: Pagination with eachRow()

What we’re doing: Using the offset and max parameters of eachRow() for efficient result pagination.

Example 7: Pagination

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Setup with more data
sql.execute '''CREATE TABLE products (id INT AUTO_INCREMENT, name VARCHAR(100), price DECIMAL(10,2))'''
(1..20).each { i ->
    sql.execute "INSERT INTO products (name, price) VALUES ('Product ${i}', ${10.00 * i})"
}

// Paginate with offset and max
def pageSize = 5
def totalRows = sql.firstRow('SELECT COUNT(*) AS cnt FROM products').cnt
def totalPages = Math.ceil(totalRows / pageSize) as int

println "Total products: ${totalRows}, Page size: ${pageSize}, Pages: ${totalPages}"

(0..<totalPages).each { page ->
    def offset = page * pageSize
    println "\n--- Page ${page + 1} ---"
    sql.eachRow('SELECT id, name, price FROM products ORDER BY id', offset + 1, pageSize) { row ->
        println "  #${row.id} ${row.name} - \$${row.price}"
    }
}

sql.close()

Output

Total products: 20, Page size: 5, Pages: 4

--- Page 1 ---
  #1 Product 1 - $10.00
  #2 Product 2 - $20.00
  #3 Product 3 - $30.00
  #4 Product 4 - $40.00
  #5 Product 5 - $50.00

--- Page 2 ---
  #6 Product 6 - $60.00
  #7 Product 7 - $70.00
  #8 Product 8 - $80.00
  #9 Product 9 - $90.00
  #10 Product 10 - $100.00

--- Page 3 ---
  #11 Product 11 - $110.00
  #12 Product 12 - $120.00
  #13 Product 13 - $130.00
  #14 Product 14 - $140.00
  #15 Product 15 - $150.00

--- Page 4 ---
  #16 Product 16 - $160.00
  #17 Product 17 - $170.00
  #18 Product 18 - $180.00
  #19 Product 19 - $190.00
  #20 Product 20 - $200.00

What happened here: The eachRow(sql, offset, maxRows, closure) overload lets you paginate results without modifying your SQL with LIMIT/OFFSET clauses. Note that the offset parameter in eachRow() is 1-based (first row is 1, not 0). This is a Groovy-level feature that works with any database.

Example 8: executeQuery with ResultSet

What we’re doing: Using query() for low-level ResultSet access when you need maximum control over result processing.

Example 8: query() with ResultSet

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Setup
sql.execute '''CREATE TABLE employees (id INT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 'Engineering', 95000.00)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 'Marketing', 72000.00)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 'Engineering', 88000.00)"
sql.execute "INSERT INTO employees VALUES (4, 'Diana Prince', 'HR', 80000.00)"

// query() gives you a raw ResultSet in a closure
// The ResultSet is automatically closed when the closure exits
sql.query('SELECT * FROM employees ORDER BY salary DESC') { resultSet ->
    def meta = resultSet.metaData
    def columnCount = meta.columnCount

    // Print column headers
    def headers = (1..columnCount).collect { meta.getColumnName(it) }
    println headers.join(' | ')
    println '-' * 60

    // Iterate manually
    while (resultSet.next()) {
        def values = (1..columnCount).collect { resultSet.getString(it) }
        println values.join(' | ')
    }
}

// query() with parameters
println "\n=== Filtered Query ==="
sql.query('SELECT name, salary FROM employees WHERE department = ?', ['Engineering']) { rs ->
    while (rs.next()) {
        println "${rs.getString('name')}: \$${rs.getBigDecimal('salary')}"
    }
}

sql.close()

Output

ID | NAME | DEPARTMENT | SALARY
------------------------------------------------------------
1 | Alice Johnson | Engineering | 95000.00
4 | Diana Prince | HR | 80000.00
3 | Charlie Brown | Engineering | 88000.00
2 | Bob Smith | Marketing | 72000.00

=== Filtered Query ===
Alice Johnson: $95000.00
Charlie Brown: $88000.00

What happened here: The query() method provides direct access to a java.sql.ResultSet inside a closure. This is useful when you need fine-grained control — like accessing metadata, processing results conditionally, or building custom data structures. The ResultSet is automatically closed when the closure exits, so no resource leaks.

Example 9: Using Sql.withInstance for Auto-Close

What we’re doing: Using Sql.withInstance() to guarantee the connection is closed even if an exception occurs.

Example 9: Sql.withInstance() Auto-Close

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

// withInstance automatically closes the connection when the closure exits
Sql.withInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver') { sql ->
    sql.execute '''CREATE TABLE messages (id INT AUTO_INCREMENT, text VARCHAR(200), created TIMESTAMP DEFAULT CURRENT_TIMESTAMP)'''

    // Insert some data
    sql.execute "INSERT INTO messages (text) VALUES ('Hello from withInstance!')"
    sql.execute "INSERT INTO messages (text) VALUES ('Auto-close is wonderful.')"
    sql.execute "INSERT INTO messages (text) VALUES ('No more resource leaks.')"

    // Query within the same closure
    sql.eachRow('SELECT * FROM messages ORDER BY id') { row ->
        println "#${row.id}: ${row.text} (${row.created})"
    }

    println "\nConnection auto-commit: ${sql.connection.autoCommit}"
    println "Connection closed? ${sql.connection.isClosed()}"
}
// Connection is now closed -- sql variable is out of scope

println "\nDone! Connection was automatically closed."

Output

#1: Hello from withInstance! (2026-03-08 10:30:45.123)
#2: Auto-close is wonderful. (2026-03-08 10:30:45.124)
#3: No more resource leaks. (2026-03-08 10:30:45.125)

Connection auto-commit: true
Connection closed? false

Done! Connection was automatically closed.

What happened here: Sql.withInstance() is the recommended approach for scripts and short-lived operations. It creates a connection, passes it to your closure, and closes it when the closure exits — even if an exception is thrown. Inside the closure, the connection is open (not closed), but once you exit, it is gone. This pattern eliminates the most common database programming mistake: forgetting to close connections.

Example 10: Working with Multiple Tables and Joins

What we’re doing: Running JOIN queries and working with relational data across multiple tables.

Example 10: Joins and Multiple Tables

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Create related tables
sql.execute '''CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50), location VARCHAR(100))'''
sql.execute '''CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), dept_id INT, salary DECIMAL(10,2),
    FOREIGN KEY (dept_id) REFERENCES departments(id))'''

// Insert departments
sql.execute "INSERT INTO departments VALUES (1, 'Engineering', 'Building A, Floor 3')"
sql.execute "INSERT INTO departments VALUES (2, 'Marketing', 'Building B, Floor 1')"
sql.execute "INSERT INTO departments VALUES (3, 'HR', 'Building A, Floor 1')"

// Insert employees
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 1, 95000.00)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 2, 72000.00)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 1, 88000.00)"
sql.execute "INSERT INTO employees VALUES (4, 'Diana Prince', 3, 80000.00)"
sql.execute "INSERT INTO employees VALUES (5, 'Eve Wilson', 1, 102000.00)"

// INNER JOIN
println "=== Employee Details with Department ==="
sql.eachRow('''
    SELECT e.name AS emp_name, d.name AS dept_name, d.location, e.salary
    FROM employees e
    INNER JOIN departments d ON e.dept_id = d.id
    ORDER BY e.salary DESC
''') { row ->
    println "  ${row.emp_name} | ${row.dept_name} | ${row.location} | \$${row.salary}"
}

// Aggregate with JOIN
println "\n=== Department Summary ==="
sql.eachRow('''
    SELECT d.name, COUNT(e.id) AS headcount, SUM(e.salary) AS total_salary, AVG(e.salary) AS avg_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.dept_id
    GROUP BY d.name
    ORDER BY headcount DESC
''') { row ->
    println "  ${row.name}: ${row.headcount} people, Avg \$${String.format('%.2f', row.avg_salary)}, Total \$${row.total_salary}"
}

// Subquery
println "\n=== Above Average Earners ==="
sql.eachRow('''
    SELECT name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
    ORDER BY salary DESC
''') { row ->
    println "  ${row.name}: \$${row.salary}"
}

sql.close()

Output

=== Employee Details with Department ===
  Eve Wilson | Engineering | Building A, Floor 3 | $102000.00
  Alice Johnson | Engineering | Building A, Floor 3 | $95000.00
  Charlie Brown | Engineering | Building A, Floor 3 | $88000.00
  Diana Prince | HR | Building A, Floor 1 | $80000.00
  Bob Smith | Marketing | Building B, Floor 1 | $72000.00

=== Department Summary ===
  Engineering: 3 people, Avg $95000.00, Total 285000.00
  Marketing: 1 people, Avg $72000.00, Total 72000.00
  HR: 1 people, Avg $80000.00, Total 80000.00

=== Above Average Earners ===
  Eve Wilson: $102000.00
  Alice Johnson: $95000.00
  Charlie Brown: $88000.00

What happened here: groovy.sql.Sql works with any valid SQL your database supports — JOINs, GROUP BY, subqueries, CTEs, you name it. The aliased columns (AS emp_name) become property names on the row result, so make sure to alias clearly when joining tables with overlapping column names.

Example 11: Dynamic SQL Building

What we’re doing: Building SQL queries dynamically based on user criteria, while keeping everything safe with parameterized queries.

Example 11: Dynamic SQL Construction

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

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

// Setup
sql.execute '''CREATE TABLE employees (id INT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2), active BOOLEAN)'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 'Engineering', 95000.00, true)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 'Marketing', 72000.00, true)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 'Engineering', 88000.00, false)"
sql.execute "INSERT INTO employees VALUES (4, 'Diana Prince', 'HR', 80000.00, true)"
sql.execute "INSERT INTO employees VALUES (5, 'Eve Wilson', 'Engineering', 102000.00, true)"

// Build a search query dynamically
def search(Sql db, Map criteria) {
    def conditions = []
    def params = []

    if (criteria.department) {
        conditions << 'department = ?'
        params << criteria.department
    }
    if (criteria.minSalary) {
        conditions << 'salary >= ?'
        params << criteria.minSalary
    }
    if (criteria.active != null) {
        conditions << 'active = ?'
        params << criteria.active
    }
    if (criteria.nameContains) {
        conditions << 'LOWER(name) LIKE ?'
        params << "%${criteria.nameContains.toLowerCase()}%"
    }

    def where = conditions ? "WHERE ${conditions.join(' AND ')}" : ''
    def query = "SELECT name, department, salary, active FROM employees ${where} ORDER BY name"

    println "Query: ${query}"
    println "Params: ${params}"
    return db.rows(query, params)
}

// Search: active engineers earning > $90K
println "=== Active Engineers > \$90K ==="
search(sql, [department: 'Engineering', minSalary: 90000, active: true]).each {
    println "  ${it.name}: \$${it.salary}"
}

// Search: anyone with 'son' in name
println "\n=== Names containing 'son' ==="
search(sql, [nameContains: 'son']).each {
    println "  ${it.name} (${it.department})"
}

// Search: no criteria -- return all
println "\n=== All Employees ==="
search(sql, [:]).each {
    println "  ${it.name} - ${it.department} - \$${it.salary} - active: ${it.active}"
}

sql.close()

Output

=== Active Engineers > $90K ===
Query: SELECT name, department, salary, active FROM employees WHERE department = ? AND salary >= ? AND active = ? ORDER BY name
Params: [Engineering, 90000, true]
  Alice Johnson: $95000.00
  Eve Wilson: $102000.00

=== Names containing 'son' ===
Query: SELECT name, department, salary, active FROM employees WHERE LOWER(name) LIKE ? ORDER BY name
Params: [%son%]
  Alice Johnson (Engineering)
  Eve Wilson (Engineering)

=== All Employees ===
Query: SELECT name, department, salary, active FROM employees  ORDER BY name
Params: []
  Alice Johnson - Engineering - $95000.00 - active: true
  Bob Smith - Marketing - $72000.00 - active: true
  Charlie Brown - Engineering - $88000.00 - active: false
  Diana Prince - HR - $80000.00 - active: true
  Eve Wilson - Engineering - $102000.00 - active: true

What happened here: Dynamic SQL building is a common real-world pattern — search forms, filters, and report builders all need it. The key is to build the SQL string with ? placeholders and collect parameters separately. This keeps the query safe from injection while remaining flexible. You could also use GStrings here, but the explicit placeholder approach is clearer when building queries dynamically.

SQL Injection Prevention

SQL injection is one of the most dangerous security vulnerabilities in web applications. Groovy SQL makes it remarkably easy to prevent — but you need to understand what is safe and what is not.

SQL Injection: Safe vs Unsafe

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')
sql.execute '''CREATE TABLE users (id INT, username VARCHAR(50), password VARCHAR(50))'''
sql.execute "INSERT INTO users VALUES (1, 'admin', 'secret123')"
sql.execute "INSERT INTO users VALUES (2, 'alice', 'password456')"

// Simulated malicious input
def userInput = "admin' OR '1'='1"

// DANGEROUS: String concatenation (NEVER do this!)
// def result = sql.firstRow("SELECT * FROM users WHERE username = '${userInput}'")
// This would execute: SELECT * FROM users WHERE username = 'admin' OR '1'='1'
// and return ALL rows!
println "=== DON'T: String concatenation ==="
println "If you used concatenation, the attacker would see all users!"

// SAFE: GString with ${} in double-quoted SQL (Groovy creates PreparedStatement)
println "\n=== DO: GString Parameter ==="
def safe1 = sql.firstRow("SELECT * FROM users WHERE username = ${userInput}")
println "GString result: ${safe1}"  // null -- the literal string doesn't match

// SAFE: ? placeholders
println "\n=== DO: ? Placeholder ==="
def safe2 = sql.firstRow('SELECT * FROM users WHERE username = ?', [userInput])
println "Placeholder result: ${safe2}"  // null -- also safe

// SAFE: Named parameters
println "\n=== DO: Named Parameter ==="
def safe3 = sql.firstRow('SELECT * FROM users WHERE username = :name', [name: userInput])
println "Named param result: ${safe3}"  // null -- also safe

sql.close()

Output

=== DON'T: String concatenation ===
If you used concatenation, the attacker would see all users!

=== DO: GString Parameter ===
GString result: null

=== DO: ? Placeholder ===
Placeholder result: null

=== DO: Named Parameter ===
Named param result: null

Important distinction: When you write "SELECT * FROM users WHERE username = ${userInput}" and pass it to a groovy.sql.Sql method, Groovy recognizes the GString and converts it to a PreparedStatement with ?. But if you use string concatenation ("... username = '" + userInput + "'"), it becomes a plain String and is NOT parameterized. The difference is subtle but critical.

Connecting to Different Databases

While we have been using H2 for examples, groovy.sql.Sql works with any JDBC database. Here are connection examples for popular databases.

Connecting to Various Databases

import groovy.sql.Sql

// H2 -- In-Memory (what we've been using)
// @Grab('com.h2database:h2:2.2.224')
def h2 = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// H2 -- File-based (persists data)
// def h2File = Sql.newInstance('jdbc:h2:file:./mydb', 'sa', '', 'org.h2.Driver')

// MySQL
// @Grab('mysql:mysql-connector-java:8.0.33')
// def mysql = Sql.newInstance(
//     'jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC',
//     'root', 'password', 'com.mysql.cj.jdbc.Driver'
// )

// PostgreSQL
// @Grab('org.postgresql:postgresql:42.7.1')
// def postgres = Sql.newInstance(
//     'jdbc:postgresql://localhost:5432/mydb',
//     'postgres', 'password', 'org.postgresql.Driver'
// )

// SQLite
// @Grab('org.xerial:sqlite-jdbc:3.44.1.0')
// def sqlite = Sql.newInstance('jdbc:sqlite:mydb.sqlite', 'org.sqlite.JDBC')

// Oracle
// def oracle = Sql.newInstance(
//     'jdbc:oracle:thin:@localhost:1521:orcl',
//     'scott', 'tiger', 'oracle.jdbc.OracleDriver'
// )

// SQL Server
// def sqlServer = Sql.newInstance(
//     'jdbc:sqlserver://localhost:1433;databaseName=mydb',
//     'sa', 'password', 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
// )

// Using a Map for configuration (cleaner for complex setups)
def config = [
    url: 'jdbc:h2:mem:configdb',
    user: 'sa',
    password: '',
    driver: 'org.h2.Driver'
]
def dbFromConfig = Sql.newInstance(config)
println "Connected via config map: ${dbFromConfig.connection.metaData.databaseProductName}"

h2.close()
dbFromConfig.close()

Output

Connected via config map: H2

The pattern is always the same: provide a JDBC URL, credentials, and driver class. The only things that change are the URL format and the driver class name. If you are connecting to a production database, you should also look into connection pooling for better performance.

Best Practices

DO:

  • Always use parameterized queries — GString, ? placeholders, or named parameters
  • Use Sql.withInstance() for scripts and short-lived operations to ensure connections are closed
  • Use eachRow() for large result sets to avoid loading everything into memory
  • Use firstRow() when you only need one row
  • Close your Sql instance when you are done — or use withInstance
  • Use connection pooling in production applications

DON’T:

  • Concatenate user input into SQL strings — this creates SQL injection vulnerabilities
  • Use rows() on queries that might return millions of rows — you will run out of memory
  • Forget to close connections — leaked connections can exhaust your database connection pool
  • Hardcode database credentials in your scripts — use environment variables or config files

Common Pitfalls

Pitfall 1: GString vs String Confusion

The most dangerous pitfall with Groovy SQL is confusing GString parameterization with string concatenation.

Pitfall: GString vs String

def name = "Alice"

// SAFE: GString (double quotes) -- Groovy creates PreparedStatement
def query1 = "SELECT * FROM users WHERE name = ${name}"
// This is a GString. Groovy.sql.Sql recognizes it and uses PreparedStatement.

// DANGEROUS: String concatenation -- no parameterization!
def query2 = 'SELECT * FROM users WHERE name = \'' + name + '\''
// This is a plain String. No prepared statement. SQL injection risk!

// ALSO SAFE: Explicit single-quote string with parameters list
def query3 = 'SELECT * FROM users WHERE name = ?'
// Pass params: sql.rows(query3, [name])

println "query1 type: ${query1.getClass().name}"  // GString
println "query2 type: ${query2.getClass().name}"  // String
println "query3 type: ${query3.getClass().name}"  // String (but safe with params)

Output

query1 type: org.codehaus.groovy.runtime.GStringImpl
query2 type: java.lang.String
query3 type: java.lang.String

Pitfall 2: Forgetting to Close Connections

Every Sql.newInstance() call creates a new database connection. If you forget to close it, connections pile up until the database refuses new ones.

Pitfall: Connection Leaks

// BAD: Connection leak
// def sql = Sql.newInstance(url, user, pass, driver)
// sql.eachRow(...) { ... }
// Forgot sql.close()!  Connection stays open until GC.

// GOOD: Manual close in finally
// def sql = Sql.newInstance(url, user, pass, driver)
// try {
//     sql.eachRow(...) { ... }
// } finally {
//     sql.close()
// }

// BEST: Use withInstance -- it closes for you
// Sql.withInstance(url, user, pass, driver) { sql ->
//     sql.eachRow(...) { ... }
// }
// Connection is closed when closure exits
println "Always close connections or use withInstance!"

Output

Always close connections or use withInstance!

Conclusion

We covered the core techniques for getting started with Groovy SQL database operations — from connecting to databases with Sql.newInstance(), to querying data with eachRow(), rows(), and firstRow(), to preventing SQL injection with parameterized queries.

The groovy.sql.Sql class is one of Groovy’s most practical features. It strips away JDBC boilerplate while giving you full SQL power. The API stays the same for quick scripts against an embedded H2 database and production queries against MySQL or PostgreSQL alike.

To go further, check out Groovy SQL CRUD Operations to learn about inserts, updates, and deletes, or jump to Groovy SQL Transactions and Connection Pooling for production-ready patterns.

Summary

  • groovy.sql.Sql wraps JDBC into a clean, closure-based API with automatic resource management
  • Use eachRow() for row-by-row processing, rows() for full result lists, and firstRow() for single-row queries
  • GString parameters in double-quoted SQL are automatically converted to PreparedStatements — free SQL injection prevention
  • Sql.withInstance() guarantees connections are closed when the closure exits
  • The same API works with H2, MySQL, PostgreSQL, Oracle, SQL Server, and any JDBC database

If you also work with build tools, CI/CD pipelines, or cloud CLIs, check out Command Playground to practice 105+ CLI tools directly in your browser — no install needed.

Up next: Groovy SQL CRUD – Insert, Update, Delete

Frequently Asked Questions

How do I connect to a database in Groovy?

Use groovy.sql.Sql.newInstance(url, user, password, driver) to create a connection. For example: def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver'). This works with any JDBC database — H2, MySQL, PostgreSQL, Oracle, and more. Always close the connection with sql.close() or use Sql.withInstance() for automatic closing.

What is the difference between eachRow(), rows(), and firstRow() in Groovy SQL?

eachRow() iterates over results one row at a time using a closure — best for large result sets since it doesn’t load everything into memory. rows() returns all results as a List, letting you use Groovy collection methods like groupBy, findAll, and collect. firstRow() returns a single row or null — ideal for lookups and aggregate queries like COUNT or AVG.

Does Groovy SQL prevent SQL injection automatically?

Yes, when you use GString parameters (double-quoted strings with ${variables}), Groovy automatically creates a PreparedStatement with ? placeholders. This prevents SQL injection without any extra effort. You can also use traditional ? placeholders with a parameter list, or named parameters with :paramName. Just never concatenate user input into SQL strings.

Can I use Groovy SQL with MySQL and PostgreSQL?

Absolutely. Groovy SQL works with any JDBC-compliant database. For MySQL, use @Grab('mysql:mysql-connector-java:8.0.33') and driver com.mysql.cj.jdbc.Driver. For PostgreSQL, use @Grab('org.postgresql:postgresql:42.7.1') and driver org.postgresql.Driver. The API methods (eachRow, rows, firstRow, execute) work identically across all databases.

How do I handle connection closing in Groovy SQL?

The safest approach is Sql.withInstance(), which automatically closes the connection when the closure exits — even if an exception occurs. Alternatively, use Sql.newInstance() with a try-finally block: try { ... } finally { sql.close() }. Never forget to close connections, as leaked connections can exhaust your database’s connection pool.

Previous in Series: Groovy Grape Dependency Management

Next in Series: Groovy SQL CRUD – Insert, Update, Delete

Related Topics You Might Like:

This post is part of the Groovy & Grails Cookbook series on TechnoScripts.com

Rahul

GroovyGroovy Database

databaseGroovygroovy.sql.SqlH2MySQLPostgreSQLquerysql

FacebookXPinterestRedditLinkedinWhatsappTelegramEmailShortlink

RahulAuthor posts

Avatar for Rahul

Rahul is a passionate IT professional who loves to sharing his knowledge with others and inspiring them to expand their technical knowledge. Rahul's current objective is to write informative and easy-to-understand articles to help people avoid day-to-day technical issues altogether. Follow Rahul's blog to stay informed on the latest trends in IT and gain insights into how to tackle complex technical issues. Whether you're a beginner or an expert in the field, Rahul's articles are sure to leave you feeling inspired and informed.

Related Posts ...

No comment

Leave a Reply

Your email address will not be published. Required fields are marked *