JDBC with Groovy

1. Introduction

In this article, we’re going to look at how to query relational databases with JDBC, using idiomatic Groovy.

JDBC, while relatively low-level, is the foundation of most ORMs and other high-level data access libraries on the JVM. And we can use JDBC directly in Groovy, of course; however, it has a rather cumbersome API.

Fortunately for us, the Groovy standard library builds upon JDBC to present an interface that is clean, simple, yet powerful. So, we’ll be exploring the Groovy SQL module.

We’re going to look at JDBC in plain Groovy, not considering any framework such as Spring, for which we have other guides.

2. JDBC and Groovy Setup

We have to include the groovy-sql module among our dependencies:

<dependency>
    <groupId>org.apache.groovy</groupId>
    <artifactId>groovy</artifactId>
    <version>4.0.21</version>
</dependency>
<dependency>
    <groupId>org.apache.groovy</groupId>
    <artifactId>groovy-sql</artifactId>
    <version>4.0.21</version>
</dependency>

It’s not necessary to list it explicitly if we’re using groovy-all:

<dependency>
    <groupId>org.apache.groovy</groupId>
    <artifactId>groovy-all</artifactId>
    <version>4.0.21</version>
</dependency>

We can find the latest version of groovy, groovy-sql and groovy-all on Maven Central.

3. Connecting to the Database

The first thing we have to do in order to work with the database is connecting to it.

Let’s introduce the groovy.sql.Sql class, which we’ll use for all operations on the database with the Groovy SQL module.

An instance of Sql represents a database on which we want to operate.

However, an instance of Sql isn’t a single database connection. We’ll talk about connections later, let’s not worry about them now; let’s just assume everything magically works.

3.1. Specifying Connection Parameters

Throughout this article, we’re going to use an HSQL Database, which is a lightweight relational DB that is mostly used in tests.

A database connection needs a URL, a driver, and access credentials:

Map dbConnParams = [
  url: 'jdbc:hsqldb:mem:testDB',
  user: 'sa',
  password: '',
  driver: 'org.hsqldb.jdbc.JDBCDriver']

Here, we’ve chosen to specify those using a Map, although it’s not the only possible choice.

We can then obtain a connection from the Sql class:

def sql = Sql.newInstance(dbConnParams)

We’ll see how to use it in the following sections.

When we’re finished, we should always release any associated resources:

sql.close()

3.2. Using a DataSource

It is common, especially in programs running inside an application server, to use a datasource to connect to the database.

Also, when we want to pool connections or to use JNDI, a datasource is the most natural option.

Groovy’s Sql class accepts datasources just fine:

def sql = Sql.newInstance(datasource)

3.3. Automatic Resource Management

Remembering to call close() when we’re done with an Sql instance is tedious; machines remember stuff much better than we do, after all.

With Sql we can wrap our code in a closure and have Groovy call close() automatically when control leaves it, even in case of exceptions:

Sql.withInstance(dbConnParams) {
    Sql sql -> haveFunWith(sql)
}

4. Issuing Statements Against the Database

Now, we can go on to the interesting stuff.

The most simple and unspecialized way to issue a statement against the database is the execute method:

sql.execute "CREATE TABLE PROJECT (id integer not null, name varchar(50), url varchar(100))"

In theory it works both for DDL/DML statements and for queries; however, the simple form above does not offer a way to get back query results. We’ll leave queries for later.

The execute method has several overloaded versions, but, again, we’ll look at the more advanced use cases of this and other methods in later sections.

4.1. Inserting Data

For inserting data in small amounts and in simple scenarios, the execute method discussed earlier is perfectly fine.

However, for cases when we have generated columns (e.g., with sequences or auto-increment) and we want to know the generated values, a dedicated method exists: executeInsert.

As for execute, we’ll now look at the most simple method overload available, leaving more complex variants for a later section.

So, suppose we have a table with an auto-increment primary key (identity in HSQLDB parlance):

sql.execute "CREATE TABLE PROJECT (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))"

Let’s insert a row in the table and save the result in a variable:

def ids = sql.executeInsert """
  INSERT INTO PROJECT (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')
"""

executeInsert behaves exactly like execute, but what does it return?

It turns out that the return value is a matrix: its rows are the inserted rows (remember that a single statement can cause multiple rows to be inserted) and its columns are the generated values.

It sounds complicated, but in our case, which is by far the most common one, there is a single row and a single generated value:

assertEquals(0, ids[0][0])

A subsequent insertion would return a generated value of 1:

ids = sql.executeInsert """
  INSERT INTO PROJECT (NAME, URL)
  VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
"""

assertEquals(1, ids[0][0])

4.2. Updating and Deleting Data

Similarly, a dedicated method for data modification and deletion exists: executeUpdate.

Again, this differs from execute only in its return value, and we’ll only look at its simplest form.

The return value, in this case, is an integer, the number of affected rows:

def count = sql.executeUpdate("UPDATE PROJECT SET URL = 'https://' + URL")

assertEquals(2, count)

5. Querying the Database

Things start getting Groovy when we query the database.

Dealing with the JDBC ResultSet class is not exactly fun. Luckily for us, Groovy offers a nice abstraction over all of that.

5.1. Iterating Over Query Results

While loops are so old style… we’re all into closures nowadays.

And Groovy is here to suit our tastes:

sql.eachRow("SELECT * FROM PROJECT") { GroovyResultSet rs ->
    haveFunWith(rs)
}

The eachRow method issues our query against the database and calls a closure over each row.

As we can see, a row is represented by an instance of GroovyResultSet, which is an extension of plain old ResultSet with a few added goodies. Read on to find more about it.

5.2. Accessing Result Sets

In addition to all of the ResultSet methods, GroovyResultSet offers a few convenient utilities.

Mainly, it exposes named properties matching column names:

sql.eachRow("SELECT * FROM PROJECT") { rs ->
    assertNotNull(rs.name)
    assertNotNull(rs.URL)
}

Note how property names are case-insensitive.

GroovyResultSet also offers access to columns using a zero-based index:

sql.eachRow("SELECT * FROM PROJECT") { rs ->
    assertNotNull(rs[0])
    assertNotNull(rs[1])
    assertNotNull(rs[2])
}

5.3. Pagination

We can easily page the results, i.e., load only a subset starting from some offset up to some maximum number of rows. This is a common concern in web applications, for example.

eachRow and related methods have overloads accepting an offset and a maximum number of returned rows:

def offset = 1
def maxResults = 1
def rows = sql.rows('SELECT * FROM PROJECT ORDER BY NAME', offset, maxResults)

assertEquals(1, rows.size())
assertEquals('REST with Spring', rows[0].name)

Here, the rows method returns a list of rows rather than iterating over them like eachRow.

6. Parameterized Queries and Statements

More often than not, queries and statements are not fully fixed at compile time; they usually have a static part and a dynamic part, in the form of parameters.

If you’re thinking about string concatenation, stop now and go read about SQL injection!

We mentioned earlier that the methods that we’ve seen in previous sections have many overloads for various scenarios.

Let’s introduce those overloads that deal with parameters in SQL queries and statements.

6.1. Strings With Placeholders

In style similar to plain JDBC, we can use positional parameters:

sql.execute(
    'INSERT INTO PROJECT (NAME, URL) VALUES (?, ?)',
    'tutorials', 'github.com/eugenp/tutorials')

or we can use named parameters with a map:

sql.execute(
    'INSERT INTO PROJECT (NAME, URL) VALUES (:name, :url)',
    [name: 'REST with Spring', url: 'github.com/eugenp/REST-With-Spring'])

This works for execute, executeUpdate, rows and eachRow. executeInsert supports parameters, too, but its signature is a little bit different and trickier.

6.2. Groovy Strings

We can also opt for a Groovier style using GStrings with placeholders.

All the methods we’ve seen don’t substitute placeholders in GStrings the usual way; rather, they insert them as JDBC parameters, ensuring the SQL syntax is correctly preserved, with no need to quote or escape anything and thus no risk of injection.

This is perfectly fine, safe and Groovy:

def name = 'REST with Spring'
def url = 'github.com/eugenp/REST-With-Spring'
sql.execute "INSERT INTO PROJECT (NAME, URL) VALUES (${name}, ${url})"

7. Transactions and Connections

So far we’ve skipped over a very important concern: transactions.

In fact, we haven’t talked at all about how Groovy’s Sql manages connections, either.

7.1. Short-Lived Connections

In the examples presented so far, each and every query or statement was sent to the database using a new, dedicated connection. Sql closes the connection as soon as the operation terminates.

Of course, if we’re using a connection pool, the impact on performance might be small.

Still, if we want to issue multiple DML statements and queries as a single, atomic operation, we need a transaction.

Also, for a transaction to be possible in the first place, we need a connection that spans multiple statements and queries.

7.2. Transactions With a Cached Connection

Groovy SQL does not allow us to create or access transactions explicitly.

Instead, we use the withTransaction method with a closure:

sql.withTransaction {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
    """
}

Inside the closure, a single database connection is used for all queries and statements.

Furthermore, the transaction is automatically committed when the closure terminates, unless it exits early due to an exception.

However, we can also manually commit or rollback the current transaction with methods in the Sql class:

sql.withTransaction {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    sql.commit()
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
    """
    sql.rollback()
}

7.3. Cached Connections Without a Transaction

Finally, to reuse a database connection without the transaction semantics described above, we use cacheConnection:

sql.cacheConnection {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    throw new Exception('This does not roll back')
}

8. Conclusions and Further Reading

In this article, we’ve looked at the Groovy SQL module and how it enhances and simplifies JDBC with closures and Groovy strings.

We can then safely conclude that plain old JDBC looks a bit more modern with a sprinkle of Groovy!

We haven’t talked about every single feature of Groovy SQL; for example, we’ve left out batch processing, stored procedures, metadata, and other things.

For further information, see the Groovy documentation.