# Interacting with a SQL database

version 3.0.3

Groovy’s `groovy-sql` module provides a higher-level abstraction over Java’s JDBC technology. JDBC itself provides a lower-level but fairly comprehensive API which provides uniform access to a whole variety of supported relational database systems. We’ll use HSQLDB in our examples here but you can alternatively use Oracle, SQL Server, MySQL and a host of others. The most frequently used class within the `groovy-sql` module is the `groovy.sql.Sql` class which raises the JDBC abstractions up one level. We’ll cover that first.

## 1. Connecting to the database

Connecting to a database with Groovy’s `Sql` class requires four pieces of information:

- The database uniform resource locator (URL)

- Username

- Password

- The driver class name (which can be derived automatically in some situations)

For our HSQLDB database, the values will be something like that shown in the following table:

Property | Value
--- | ---
url | `jdbc:hsqldb:mem:yourdb`
user | sa (or your username)
password | yourPassword
driver | `org.hsqldb.jdbcDriver`

Consult the documentation for the JDBC driver that you plan to use to determine the correct values for your situation.

The `Sql` class has a `newInstance` factory method which takes these parameters. You would typically use it as follows:

Connecting to HSQLDB

    import groovy.sql.Sql

    def url = 'jdbc:hsqldb:mem:yourDB'
    def user = 'sa'
    def password = ''
    def driver = 'org.hsqldb.jdbcDriver'
    def sql = Sql.newInstance(url, user, password, driver)

    // use 'sql' instance ...

    sql.close()

If you don’t want to have to handle resource handling yourself (i.e. call `close()` manually) then you can use the `withInstance` variation as shown here:

Connecting to HSQLDB (`withInstance` variation)

    Sql.withInstance(url, user, password, driver) { sql ->
      // use 'sql' instance ...
    }

### 1.1. Connecting with a DataSource

It is often preferred to use a DataSource. You may have one available to you from a connection pool. Here we’ll use the one provided as part of the HSQLDB driver jar as shown here:

Connecting to HSQLDB with a DataSource

    import groovy.sql.Sql
    import org.hsqldb.jdbc.JDBCDataSource

    def dataSource = new JDBCDataSource(
        database: 'jdbc:hsqldb:mem:yourDB', user: 'sa', password: '')
    def sql = new Sql(dataSource)

    // use then close 'sql' instance ...

If you have your own connection pooling, the details will be different, e.g. for Apache Commons DBCP:

Connecting to HSQLDB with a DataSource using Apache Commons DBCP

    @Grab('commons-dbcp:commons-dbcp:1.4')
    import groovy.sql.Sql
    import org.apache.commons.dbcp.BasicDataSource

    def ds = new BasicDataSource(driverClassName: "org.hsqldb.jdbcDriver",
        url: 'jdbc:hsqldb:mem:yourDB', username: 'sa', password: '')
    def sql = new Sql(ds)
    // use then close 'sql' instance ...

### 1.2. Connecting using @Grab

The previous examples assume that the necessary database driver jar is already on your classpath. For a self-contained script you can add `@Grab` statements to the top of the script to automatically download the necessary jar as shown here:

Connecting to HSQLDB using @Grab

    @Grab('org.hsqldb:hsqldb:2.3.3')
    @GrabConfig(systemClassLoader=true)
    // create, use, and then close sql instance ...

The `@GrabConfig` statement is necessary to make sure the system classloader is used. This ensures that the driver classes and system classes like `java.sql.DriverManager` are in the same classloader.

## 2. Executing SQL

You can execute arbitrary SQL commands using the `execute()` method. Let’s have a look at using it to create a table.

### 2.1. Creating tables

The simplest way to execute SQL is to call the `execute()` method passing the SQL you wish to execute as a String as shown here:

Creating a table

    // ... create 'sql' instance
    sql.execute '''
      CREATE TABLE Author (
        id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
        firstname   VARCHAR(64),
        lastname    VARCHAR(64)
      );
    '''
    // close 'sql' instance ...

There is a variant of this method which takes a GString and another with a list of parameters. There are also other variants with similar names: `executeInsert` and `executeUpdate`. We’ll see examples of these variants in other examples in this section.

## 3. Basic CRUD operations

The basic operations on a database are Create, Read, Update and Delete (the so-called CRUD operations). We’ll examine each of these in turn.

### 3.1. Creating/Inserting data

You can use the same `execute()` statement we saw earlier but to insert a row by using a SQL insert statement as follows:

Inserting a row

    sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"

You can use a special `executeInsert` method instead of `execute`. This will return a list of all keys generated. Both the `execute` and `executeInsert` methods allow you to place '?' placeholders into your SQL string and supply a list of parameters. In this case a PreparedStatement is used which avoids any risk of SQL injection. The following example illustrates `executeInsert` using placeholders and parameters:

Inserting a row using executeInsert with placeholders and parameters

    def insertSql = 'INSERT INTO Author (firstname, lastname) VALUES (?,?)'
    def params = ['Jon', 'Skeet']
    def keys = sql.executeInsert insertSql, params
    assert keys[0] == [1]

In addition, both the `execute` and `executeInsert` methods allow you to use GStrings. Any '$' placeholders within the SQL are assumed to be placeholders. An escaping mechanism exists if you want to supply part of the GString with a variable in a position which isn’t where normal placeholders go within SQL. See the GroovyDoc for more details. Also, `executeInsert` allows you to supply a list of key column names, when multiple keys are returned and you are only interested in some of them. Here is a fragment illustrating key name specification and GStrings:

Inserting a row using executeInsert with a GString and specifying key names

    def first = 'Guillaume'
    def last = 'Laforge'
    def myKeyNames = ['ID']
    def myKeys = sql.executeInsert """
      INSERT INTO Author (firstname, lastname)
      VALUES (${first}, ${last})
    """, myKeyNames
    assert myKeys[0] == [ID: 2]

### 3.2. Reading rows

Reading rows of data from the database is accomplished using one of several available methods: `query`, `eachRow`, `firstRow` and `rows`.

Use the `query` method if you want to iterate through the `ResultSet` returned by the underlying JDBC API as shown here:

Reading data using `query`

    def expected = ['Dierk Koenig', 'Jon Skeet', 'Guillaume Laforge']

    def rowNum = 0
    sql.query('SELECT firstname, lastname FROM Author') { resultSet ->
      while (resultSet.next()) {
        def first = resultSet.getString(1)
        def last = resultSet.getString('lastname')
        assert expected[rowNum++] == "$first $last"
      }
    }

Use the `eachRow` method if you want a slightly higher-level abstraction which provides a Groovy friendly map-like abstraction for the `ResultSet` as shown here:

Reading data using `eachRow`

    rowNum = 0
    sql.eachRow('SELECT firstname, lastname FROM Author') { row ->
      def first = row[0]
      def last = row.lastname
      assert expected[rowNum++] == "$first $last"
    }

Note that you can use Groovy list-style and map-style notations when accessing the row of data.

Use the `firstRow` method if you for similar functionality as `eachRow` but returning only one row of data as shown here:

Reading data using `firstRow`

    def first = sql.firstRow('SELECT lastname, firstname FROM Author')
    assert first.values().sort().join(',') == 'Dierk,Koenig'

Use the `rows` method if you want to process a list of map-like data structures as shown here:

Reading data using `rows`

    List authors = sql.rows('SELECT firstname, lastname FROM Author')
    assert authors.size() == 3
    assert authors.collect { "$it.FIRSTNAME ${it[-1]}" } == expected

Note that the map-like abstraction has case-insensitive keys (hence we can use 'FIRSTNAME' or 'firstname' as the key) and also that -ve indices (a standard Groovy feature) works when using an index value (to count column numbers from the right).

You can also use any of the above methods to return scalar values, though typically `firstRow` is all that is required in such cases. An example returning the count of rows is shown here:

Reading scalar values

    assert sql.firstRow('SELECT COUNT(*) AS num FROM Author').num == 3

### 3.3. Updating rows

Updating rows can again be done using the `execute()` method. Just use a SQL update statement as the argument to the method. You can insert an author with just a lastname and then update the row to also have a firstname as follows:

Updating a row

    sql.execute "INSERT INTO Author (lastname) VALUES ('Thorvaldsson')"
    sql.execute "UPDATE Author SET firstname='Erik' where lastname='Thorvaldsson'"

There is also a special `executeUpdate` variant which returns the number of rows updated as a result of executing the SQL. For example, you can change the lastname of an author as follows:

Using executeUpdate

    def updateSql = "UPDATE Author SET lastname='Pragt' where lastname='Thorvaldsson'"
    def updateCount = sql.executeUpdate updateSql
    assert updateCount == 1

    def row = sql.firstRow "SELECT * FROM Author where firstname = 'Erik'"
    assert "${row.firstname} ${row.lastname}" == 'Erik Pragt'

### 3.4. Deleting rows

The `execute` method is also used for deleting rows as this example shows:

Deleting rows

    assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 3
    sql.execute "DELETE FROM Author WHERE lastname = 'Skeet'"
    assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 2

## 4. Advanced SQL operations

### 4.1. Working with transactions

The easiest way to perform database operations within a transaction is to include the database operation within a `withTransaction` closure as shown in the following example:

A successful transaction

    assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 0
    sql.withTransaction {
      sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
      sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')"
    }
    assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 2

Here the database starts empty and has two rows after successful completion of the operation. Outside the scope of the transaction, the database is never seen as having just one row.

If something goes wrong, any earlier operations within the `withTransaction` block are rolled back. We can see that in operation in the following example where we use database metadata (more details coming up shortly) to find the maximum allowable size of the `firstname` column and then attempt to enter a firstname one larger than that maximum value as shown here:

A failed transaction will cause a rollback

    def maxFirstnameLength
    def metaClosure = { meta -> maxFirstnameLength = meta.getPrecision(1) }
    def rowClosure = {}
    def rowCountBefore = sql.firstRow('SELECT COUNT(*) as num FROM Author').num
    try {
      sql.withTransaction {
        sql.execute "INSERT INTO Author (firstname) VALUES ('Dierk')"
        sql.eachRow "SELECT firstname FROM Author WHERE firstname = 'Dierk'", metaClosure, rowClosure
        sql.execute "INSERT INTO Author (firstname) VALUES (?)", 'X' * (maxFirstnameLength + 1)
      }
    } catch(ignore) { println ignore.message }
    def rowCountAfter = sql.firstRow('SELECT COUNT(*) as num FROM Author').num
    assert rowCountBefore == rowCountAfter

Even though the first sql execute succeeds initially, it will be rolled back and the number of rows will remain the same.

### 4.2. Using batches

When dealing with large volumes of data, particularly when inserting such data, it can be more efficient to chunk the data into batches. This is done using the `withBatch` statement as shown in the following example:

Batching SQL statements

    sql.withBatch(3) { stmt ->
      stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
      stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Paul', 'King')"
      stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Guillaume', 'Laforge')"
      stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Hamlet', 'D''Arcy')"
      stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Cedric', 'Champeau')"
      stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Erik', 'Pragt')"
      stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')"
    }

After executing these statements, there will be 7 new rows in the database. In fact, they will have been added in batches even though you can’t easily tell that after that fact. If you want to confirm what is going on under the covers, you can add a little bit of extra logging into your program. Add the following lines before the `withBatch` statement:

Logging additional SQL information

    import java.util.logging.*

    // next line will add fine logging
    Logger.getLogger('groovy.sql').level = Level.FINE
    // also adjust logging.properties file in JRE_HOME/lib to have:
    // java.util.logging.ConsoleHandler.level = FINE

With this extra logging turned on, and the changes made as per the above comment for the logging.properties file, you should see output such as:

SQL logging output with batching enable

    FINE: Successfully executed batch with 3 command(s)
    Apr 19, 2015 8:38:42 PM groovy.sql.BatchingStatementWrapper processResult

    FINE: Successfully executed batch with 3 command(s)
    Apr 19, 2015 8:38:42 PM groovy.sql.BatchingStatementWrapper processResult

    FINE: Successfully executed batch with 1 command(s)
    Apr 19, 2015 8:38:42 PM groovy.sql.Sql getStatement

We should also note, that any combination of SQL statements can be added to the batch. They don’t all have to be inserting a new row to the same table.

We noted earlier that to avoid SQL injection, we encourage you to use prepared statements, this is achieved using the variants of methods which take GStrings or a list of extra parameters. Prepared statements can be used in combination with batches as shown in the following example:

Batching prepared statements

    def qry = 'INSERT INTO Author (firstname, lastname) VALUES (?,?)'
    sql.withBatch(3, qry) { ps ->
      ps.addBatch('Dierk', 'Koenig')
      ps.addBatch('Paul', 'King')
      ps.addBatch('Guillaume', 'Laforge')
      ps.addBatch('Hamlet', "D'Arcy")
      ps.addBatch('Cedric', 'Champeau')
      ps.addBatch('Erik', 'Pragt')
      ps.addBatch('Jon', 'Skeet')
    }

This provides a much safer option if the data could come from a user such as via a script or a web form. Of course, given that a prepared statement is being used, you are limited to a batch of the same SQL operation (insert in our example) to the one table.

### 4.3. Performing pagination

When presenting large tables of data to a user, it is often convenient to present information a page at a time. Many of Groovy’s SQL retrieval methods have extra parameters which can be used to select a particular page of interest. The starting position and page size are specified as integers as shown in the following example using `rows`:

Retrieving pages of data

    def qry = 'SELECT * FROM Author'
    assert sql.rows(qry, 1, 3)*.firstname == ['Dierk', 'Paul', 'Guillaume']
    assert sql.rows(qry, 4, 3)*.firstname == ['Hamlet', 'Cedric', 'Erik']
    assert sql.rows(qry, 7, 3)*.firstname == ['Jon']

### 4.4. Fetching metadata

JDBC metadata can be retrieved in numerous ways. Perhaps the most basic approach is to extract the metadata from any row as shown in the following example which examines the tablename, column names and column type names:

Using row metadata

    sql.eachRow("SELECT * FROM Author WHERE firstname = 'Dierk'") { row ->
      def md = row.getMetaData()
      assert md.getTableName(1) == 'AUTHOR'
      assert (1..md.columnCount).collect{ md.getColumnName(it) } == ['ID', 'FIRSTNAME', 'LASTNAME']
      assert (1..md.columnCount).collect{ md.getColumnTypeName(it) } == ['INTEGER', 'VARCHAR', 'VARCHAR']
    }

And another slight variant to the previous example, this time also looking at the column label:

Also using row metadata

    sql.eachRow("SELECT firstname AS first FROM Author WHERE firstname = 'Dierk'") { row ->
      def md = row.getMetaData()
      assert md.getColumnName(1) == 'FIRSTNAME'
      assert md.getColumnLabel(1) == 'FIRST'
    }

Accessing metadata is quite common, so Groovy also provides variants to many of its methods that let you supply a closure that will be called once with the row metadata in addition to the normal row closure which is called for each row. The following example illustrates the two closure variant for `eachRow`:

Using row and metadata closures

    def metaClosure = { meta -> assert meta.getColumnName(1) == 'FIRSTNAME' }
    def rowClosure = { row -> assert row.FIRSTNAME == 'Dierk' }
    sql.eachRow("SELECT firstname FROM Author WHERE firstname = 'Dierk'", metaClosure, rowClosure)

Note that our SQL query will only return one row, so we could have equally used `firstRow` for the previous example.

Finally, JDBC also provides metadata per connection (not just for rows). You can also access such metadata from Groovy as shown in this example:

Using connection metadata

    def md = sql.connection.metaData
    assert md.driverName == 'HSQL Database Engine Driver'
    assert md.databaseProductVersion == '2.3.3'
    assert ['JDBCMajorVersion', 'JDBCMinorVersion'].collect{ md[it] } == [4, 0]
    assert md.stringFunctions.tokenize(',').contains('CONCAT')
    def rs = md.getTables(null, null, 'AUTH%', null)
    assert rs.next()
    assert rs.getString('TABLE_NAME') == 'AUTHOR'

Consult the JavaDoc for your driver to find out what metadata information is available for you to access.

### 4.5. Named and named-ordinal parameters

Groovy supports some additional alternative placeholder syntax variants. The GString variants are typically preferred over these alternatives but the alternatives are useful for Java integration purposes and sometimes in templating scenarios where GStrings might already be in heavy use as part of a template. The named parameter variants are much like the String plus list of parameter variants but instead of having a list of `?` placeholders followed by a list of parameters, you have one or more placeholders having the form `:propName` or `?.propName` and a single map, named arguments or a domain object as the parameter. The map or domain object should have a property named `propName` corresponding to each supplied placeholder.

Here is an example using the colon form:

Named parameters (colon form)

    sql.execute "INSERT INTO Author (firstname, lastname) VALUES (:first, :last)", first: 'Dierk', last: 'Koenig'

And another example using the question mark form:

Named parameters (question mark form)

    sql.execute "INSERT INTO Author (firstname, lastname) VALUES (?.first, ?.last)", first: 'Jon', last: 'Skeet'

If the information you need to supply is spread across multiple maps or domain objects you can use the question mark form with an additional ordinal index as shown here:

Named-ordinal parameters

    class Rockstar { String first, last }
    def pogo = new Rockstar(first: 'Paul', last: 'McCartney')
    def map = [lion: 'King']
    sql.execute "INSERT INTO Author (firstname, lastname) VALUES (?1.first, ?2.lion)", pogo, map

### 4.6. Stored procedures

The exact syntax for creating a stored procedure or function varies slightly between different databases. For the HSQLDB database we are using, we can create a stored function which returns the initials of all authors in a table as follows:

Creating a stored function

    sql.execute """
      CREATE FUNCTION SELECT_AUTHOR_INITIALS()
      RETURNS TABLE (firstInitial VARCHAR(1), lastInitial VARCHAR(1))
      READS SQL DATA
      RETURN TABLE (
        SELECT LEFT(Author.firstname, 1) as firstInitial, LEFT(Author.lastname, 1) as lastInitial
        FROM Author
      )
    """

We can use a SQL `CALL` statement to invoke the function using Groovy’s normal SQL retrieval methods. Here is an example using `eachRow`.

Creating a stored procedure or function

    def result = []
    sql.eachRow('CALL SELECT_AUTHOR_INITIALS()') {
      result << "$it.firstInitial$it.lastInitial"
    }
    assert result == ['DK', 'JS', 'GL']

Here is the code for creating another stored function, this one taking the lastname as a parameter:

Creating a stored function with a parameter

    sql.execute """
      CREATE FUNCTION FULL_NAME (p_lastname VARCHAR(64))
      RETURNS VARCHAR(100)
      READS SQL DATA
      BEGIN ATOMIC
        DECLARE ans VARCHAR(100);
        SELECT CONCAT(firstname, ' ', lastname) INTO ans
        FROM Author WHERE lastname = p_lastname;
        RETURN ans;
      END
    """

We can use the placeholder syntax to specify where the parameter belongs and note the special placeholder position to indicate the result:

Using a stored function with a parameter

    def result = sql.firstRow("{? = call FULL_NAME(?)}", ['Koenig'])
    assert result[0] == 'Dierk Koenig'

Finally, here is a stored procedure with input and output parameters:

Creating a stored procedure with input and output parameters

    sql.execute """
      CREATE PROCEDURE CONCAT_NAME (OUT fullname VARCHAR(100),
        IN first VARCHAR(50), IN last VARCHAR(50))
      BEGIN ATOMIC
        SET fullname = CONCAT(first, ' ', last);
      END
    """

To use the `CONCAT_NAME` stored procedure parameter, we make use of a special `call` method. Any input parameters are simply provided as parameters to the method call. For output parameters, the resulting type must be specified as shown here:

Using a stored procedure with input and output parameters

    sql.call("{call CONCAT_NAME(?, ?, ?)}", [Sql.VARCHAR, 'Dierk', 'Koenig']) {
      fullname -> assert fullname == 'Dierk Koenig'
    }

Creating a stored procedure with an input/output parameter

    sql.execute """
      CREATE PROCEDURE CHECK_ID_POSITIVE_IN_OUT ( INOUT p_err VARCHAR(64), IN pparam INTEGER, OUT re VARCHAR(15))
      BEGIN ATOMIC
        IF pparam > 0 THEN
          set p_err = p_err || '_OK';
          set re = 'RET_OK';
        ELSE
          set p_err = p_err || '_ERROR';
          set re = 'RET_ERROR';
        END IF;
      END;
    """

Using a stored procedure with an input/output parameter

    def scall = "{call CHECK_ID_POSITIVE_IN_OUT(?, ?, ?)}"
    sql.call scall, [Sql.inout(Sql.VARCHAR("MESSAGE")), 1, Sql.VARCHAR], {
      res, p_err -> assert res == 'MESSAGE_OK' && p_err == 'RET_OK'
    }

## 5. Using DataSets

(TBD)