Groovy Guide — SQL: Database Access Made Simple

In this tutorial, you will learn about Groovy Guide. We cover key concepts, practical examples, and best practices to help you master this topic.

Groovy SQL wraps JDBC with a concise API featuring closure-based result iteration, named parameters, GString SQL queries, and automatic connection management.

What You'll Learn

• Connecting to databases
• Querying with eachRow, rows, firstRow
• Named and positional parameters
• Inserts, updates, and deletes
• DataSet operations

Why It Matters

Groovy SQL makes database access concise and readable while preventing SQL injection through parameterization. Durga Antivirus Pro uses Groovy SQL for rule database access.

Real-World Use

Data access in web applications, ETL processes, reporting tools, and database administration scripts.

flowchart LR
    A["SQL"] --> B["Connection"]
    B --> C["Queries"]
    C --> D["Updates"]
    D --> E["DataSet"]
    A:::current --> B
    style A fill:#2563eb,stroke:#2563eb,color:#fff
    style B fill:#dbeafe,stroke:#2563eb,color:#1e40af
    style C fill:#dbeafe,stroke:#2563eb,color:#1e40af
    style D fill:#dbeafe,stroke:#2563eb,color:#1e40af
    style E fill:#f1f5f9,stroke:#94a3b8,color:#64748b

Connecting

@Grab('org.postgresql:postgresql:42.5.0')
import groovy.sql.Sql

// Connection
def sql = Sql.newInstance(
    'jdbc:postgresql://localhost:5432/mydb',
    'user', 'password', 'org.postgresql.Driver'
)

// H2 in-memory (no driver needed)
def sql = Sql.newInstance(
    'jdbc:h2:mem:test', 'sa', '', 'org.h2.Driver'
)

Querying

sql.eachRow("SELECT * FROM users") { row ->
    println "${row.id}: ${row.name} (${row.email})"
}

// Get all rows
def rows = sql.rows("SELECT * FROM users WHERE active = ?", [true])

// Get first row
def first = sql.firstRow("SELECT * FROM users WHERE id = ?", [1])

// Get column values
def names = sql.rows("SELECT name FROM users").collect { it.name }

Parameterized Queries

// Positional parameters
def users = sql.rows(
    "SELECT * FROM users WHERE age > ? AND active = ?",
    [21, true]
)

// Named parameters
def user = sql.firstRow(
    "SELECT * FROM users WHERE name = :name",
    [name: "Alice"]
)

// GString (Groovy converts safely)
def minAge = 21
def results = sql.rows(
    "SELECT * FROM users WHERE age > $minAge"
)

Updates

// Insert
def count = sql.executeInsert(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ["Alice", "alice@example.com"]
)

// Update
sql.execute(
    "UPDATE users SET active = ? WHERE id = ?",
    [false, 1]
)

// Delete
sql.execute(
    "DELETE FROM users WHERE last_login < ?",
    [new Date() - 365]
)

// Batch insert
sql.withBatch("INSERT INTO log (msg) VALUES (?)") { stmt ->
    (1..1000).each { stmt.addBatch("Message $it") }
}

DataSet

// DataSet provides OOP-style queries
def users = sql.dataSet("users")

// Chained operations
def activeUsers = users.findAll { it.active == true }
def youngUsers = activeUsers.findAll { it.age < 30 }

// Iterate
youngUsers.each { println it.name }

// Pagination
def page = users.findAll().paginate(10, 0)  // offset 0, limit 10

Transactions

sql.withTransaction {
    def userId = sql.executeInsert(
        "INSERT INTO users (name) VALUES (?)", ["Alice"]
    )[0][0]

    sql.execute(
        "INSERT INTO profiles (user_id, bio) VALUES (?, ?)",
        [userId, "Bio here"]
    )

    // Auto-commit on closure success
    // Auto-rollback on exception
}

Common Mistakes

1. SQL injection with string interpolation

Use parameterized queries (?, $) not string concatenation. GString parameters are safe.

2. Connection leaks

Use Sql.newInstance in try-with-resources or close explicitly.

3. ResultSet size

.rows() loads all results. Use eachRow for large result sets.

4. Batch performance

Use withBatch for multiple similar operations. Much faster than individual executes.

5. Transaction boundaries

Keep transactions short. Don't include slow operations inside withTransaction.

Practice Questions

1. How do you prevent SQL injection in Groovy? Use parameterized queries with ? positional or :named parameters. GString SQL converts parameters safely.

2. What is the benefit of eachRow over rows? eachRow processes rows one at a time without loading the entire result set into memory.

3. How do you handle transactions? Use sql.withTransaction { ... } for automatic commit on success and rollback on exception.

Challenge: Write a Groovy script that creates a table, inserts 100 rows, and queries them back.

FAQ

{{< faq question="Does Groovy SQL support connection pooling?" >} Not directly. Use HikariCP or your app server's DataSource with Sql.newInstance(dataSource). {{< /faq >}}

{{< faq question="Can I use stored procedures?" >} Yes. Use sql.call("{call proc_name(?)}", [param]) for stored procedure calls. {{< /faq >}}

{{< faq question="What databases are supported?" >} Any JDBC-compatible database: PostgreSQL, MySQL, Oracle, SQL Server, H2, SQLite. {{< /faq >}}

{{< faq question="How do I handle BLOBs and CLOBs?" >} Groovy SQL handles BLOBs and CLOBs through standard JDBC. Use getBytes() and getString() on result sets. {{< /faq >}}

{{< faq question="Can I use Pagination with DataSet?" >} Yes. DataSet supports paginate(limit, offset) for result set pagination. {{< /faq >}}

Mini Project

Build a database Migration script:

@Grab('org.h2:h2:2.1.214')
import groovy.sql.Sql

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

def migrations = [
    "001_create_users.sql": """
        CREATE TABLE users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(200) UNIQUE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """,
    "002_add_age.sql": """
        ALTER TABLE users ADD COLUMN age INT DEFAULT 0
    """,
]

migrations.each { name, script ->
    try {
        sql.execute(script)
        println "Applied: $name"
    } catch (Exception e) {
        println "Failed: $name - ${e.message}"
    }
}

What's Next

Now that you understand SQL access, explore Groovy's template engine.