Better JDBC With Groovy Sql

I don't mind using JDBC directly in many of the Java applications I work with, especially when using it in conjunction with Spring's JDBC support or with a simple SQL mapping framework like MyBatis. Groovy makes using JDBC particularly pleasant. I appreciate the combination of powerful flexibility of direct SQL with the ease and convenience of Groovy's treatment of JDBC. In this post, I look at various helpful aspects of Groovy's Sql class.

I looked at a basic and common use of Groovy's Sql class in the post GroovySql: Groovy JDBC. I began that post by looking at using Groovy's basic result set processing closure (Sql.eachRow) as an example of the Read/Retrieve portion of CRUD. I then demonstrated simple examples of CRUD operations Create and Delete using Sql.execute(String) and Sql.execute(String, List). Groovy's Sql class provides significantly more functionality than this and I demonstrate some of those methods here.

For convenience, the following Groovy code listing demonstrates accessing an instance of Sql and using it to query the Oracle hr/hr sample schema and process the result set. The example includes usage of rootLoader to avoid needing to provide the JDBC driver on the script's classpath explicitly.

Beginning of demoGroovySql.groovy Script

#!/usr/bin/env groovy

// Add JDBC driver to classpath as part of this script's bootstrapping.
// See http://marxsoftware.blogspot.com/2011/02/groovy-scripts-master-their-own.html.
// This location needs to be adjusted for specific user environment.
this.class.classLoader.rootLoader.addURL(
   new URL("file:///C:/app/Dustin/product/11.1.0/db_1/jdbc/lib/ojdbc6.jar"))


// Get instance of Groovy's Sql class
// See http://marxsoftware.blogspot.com/2009/05/groovysql-groovy-jdbc.html
import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                          "oracle.jdbc.pool.OracleDataSource")

// iterate over query's result set and "process" each row by printing two names
sql.eachRow("SELECT employee_id, last_name, first_name FROM employees")
{
   println "Employee ${it.first_name} ${it.last_name} has ID of ${it.employee_id}."
}

Although the overloaded Sql.eachRow methods are often exactly what's needed to iterate over a result set and apply a closure to each row of the result set, this is not the only Groovy approach to querying the database. Another approach is to instruct Groovy to simply access the first row of the underlying result set. This is particularly useful when it is known that only one result will be returned, which is case in the frequent situation of querying based on the primary (unique) key. The next example demonstrates using Sql.firstRow(String, Object[]) to get a single row out of a result set.

Using Sql.firstRow(String, Object[])

// when you know there's only one match, firstRow can be particularly useful
def employee140 = sql.firstRow("SELECT employee_id, last_name, first_name FROM employees where employee_id = ?", [140])
println "Employee 140 is ${employee140.first_name} ${employee140.last_name}"

As demonstrated in the above code listing, there is no need to use a closure or its implicit it variable because there is only a single object returned from Sql.firstRow. This object's properties are accessed by the same names as the underlying database columns. The few lines of the above example perform the query, extract the first row in the result set, and provide easy access to that row's values. All too easy.

There may be times when one needs direct access to the result set from the database query. In such cases, use of one of the overloaded Sql.query methods is apropos. In other situations, it might be most convenient to have the rows of the result set as a collection of these rows. This structure is obtained by invoking one of the overloaded Sql.rows methods. This might be preferred in situations where traditional Java iteration is preferred over use of the Sql.eachRow's closures.

The Groovy Sql class provides several approaches for inserting (creating) data (the "c" in CRUD). One such method is the Sql.executeInsert(String) method. This method's documentation states that this method "executes the given SQL statement (typically an INSERT statement)" and adds, "Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field."

The next Groovy code snippet shows use of the Sql.executeInsert(String) method. A screenshot is shown after the code listing which shows the results of running this code. As that output shows, the returned ID in this case is an Oracle ROWID. The ROWID is shown in both of the lines output with a println invocation.

// insert new employee with Sql.executeInsert
def insertStr =
"""insert into employees
   (employee_id, first_name, last_name, email, hire_date, job_id)
  values
   (employees_seq.nextval, 'Dustin', 'Marx', 'dustin@marxiansw.com',
    to_date('2011/02/28', 'yyyy/mm/dd'), 'IT_PROG')"""
def insertedEmployees = sql.executeInsert(insertStr)
println insertedEmployees.dump()
def insertedEmployeeId = insertedEmployees[0][0].toJdbc()
println "Employee ${insertedEmployeeId} added."

The Groovy Sql class supports a more traditional insertion via its Sql.execute(String) method (which can also be used for running DDL and other types of DML statements). The next code listing demonstrates this.

// insert new employee with Sql.execute
def insertStr =
"""insert into employees
   (employee_id, first_name, last_name, email, hire_date, job_id)
  values
   (employees_seq.nextval, 'Dustin', 'Marx', 'dustin@marxiansw.com',
    to_date('2011/02/28', 'yyyy/mm/dd'), 'IT_PROG')"""
def employeeInserted = sql.execute(insertStr)

Note that the same string with SQL insert command was used in both cases and it was simply a matter of which Sql method was desired. It is important to also note that there are overloaded versions of these methods that allow parameters to be passed. This is usually a better approach because it provides the advantages of using a PreparedStatement. I showed use of the Sql.execute method with parameters for deletion and for insertion in my post GroovySql: Groovy JDBC.

The Sql.executeUpdate(String) [and its overloaded versions] are handy to use when one wants to check the returned integer to know how many rows were updated. This is demonstrated in the next Groovy code listing.

// update the previously added employee
def updateStr =
"""update employees set email='dustin@marxblog.com'
   where employees.first_name='Dustin' and employees.last_name='Marx'"""
def numberRowsUpdated = sql.executeUpdate(updateStr)
println "${numberRowsUpdated} row(s) was/were updated."

Stored procedures and stored functions can also be executed using Groovy's Sql class. The next Groovy code listing demonstrates this in calling the Oracle supplied (built-in) function DBMS_UTILITY.GET_TIME().

// invoke PL/SQL stored procedure with Sql.call
def storedProcCall =
   """{? = call dbms_utility.get_time()}"""
sql.call(storedProcCall, [Sql.INTEGER])
{
   println "DBMS_UTILITY.GET_TIME(): ${it}"
}

The above example demonstrates calling an Oracle function and it demonstrates supplying Sql.INTEGER as an OutParameter.

Additional details regarding Groovy's database support can be found in the groovy.sql.Sql documentation (a nice example of how Javadoc documentation can provide examples of how to use the class), in the Database Features section of the Groovy User Guide, and in Practically Groovy: JDBC Programming with Groovy.

I find myself frequently accessing databases with my Groovy scripts. Database access has always been relatively straightforward in Java via JDBC, but Groovy makes this database access even more concise and fluent.