GroovySql: Groovy JDBC

I have long felt that JDBC is one of the most underrated reliable work horses of the Java world. Although JDBC has proven its value many times over and, in my opinion, ranks up there with servlets and JMS in terms of time-proven worth, JDBC does have its drawbacks. I have written before regarding how the Spring Framework addresses many of the most common JDBC disadvantages. In this blog posting, I will address how Groovy similarly addresses drawbacks of JDBC.

As I wrote about in the article Add Some Spring to Your Oracle JDBC, JDBC can be somewhat tedious to work with due to its single primary checked exception (including the necessity to have nested try/catch blocks) and to other required boilerplate code that the Spring Framework largely covers via its JDBC template. Groovy similarly reduces the amount of boilerplate code required to work with JDBC.

Besides dealing with the handling of an SQLException (or one of the child exceptions introduced in Java SE 6 such as SQLRecoverableException), another task one commonly has had to perform when using JDBC to retrieve data is to iterate over the returned ResultSet. Groovy does much of this for us as shown in the next code listing.

getEmployees.groovy

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                      "oracle.jdbc.pool.OracleDataSource")
sql.eachRow("SELECT employee_id, last_name, first_name FROM employees")
{
   println "The employee's name is ${it.first_name} ${it.last_name}."
}

As with all Groovy code samples in this blog post, I am using Groovy more as a scripting language and less as an application development language. In other words, I'm not bothering with making Groovy classes.

The getEmployees.groovy script listed above uses Sql from the groovy.sql package. The Sql.newInstance method is has several overloaded versions and the version accepting a JDBC connection URL, username String, password String, and JDBC driver String is the version used in this example (as well as all other examples in this particular post). In this post's examples, the database is an Oracle database, but any database with a compliant JDBC driver could be used.

When the above code is run, the expected output sentence with each employee's full name is printed.

It is also easy to insert data into the database with GroovySql. The insertion of a new job row in the JOBS table of Oracle's HR schema is shown next.

insertJob.groovy

jobId = "QU_OPER"
jobTitle = "Quarry Operator"
minSalary = 1
maxSalary = 5
import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                      "oracle.jdbc.pool.OracleDataSource")
sql.execute(
   """INSERT INTO jobs(job_id, job_title, min_salary, max_salary)
   VALUES(${jobId}, ${jobTitle}, ${minSalary}, ${maxSalary})""")

The above example inserts the Quarry Operator position into the database. The values to be inserted are included directly within the Groovy strings using the ${} notation. In the next code listing, I again insert data into the database, but this time use a PreparedStatement approach to insert a new employee who happens to have this new job position of Quarry Operator.

insertEmployee.groovy

employeeId = 2000
firstName = "Fred"
lastName = "Flintstone"
eMail = "fred@slaterockngravel.com"
jobId = "QU_OPER"
hireDate = new java.sql.Date(System.currentTimeMillis())
import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                      "oracle.jdbc.pool.OracleDataSource")
sql.execute(
   """INSERT INTO employees(employee_id, first_name, last_name, email, job_id, hire_date)
   VALUES(?, ?, ?, ?, ?, ?)""", [employeeId, firstName, lastName, eMail, jobId, hireDate])

The insertion of the employee looked very similar to the insertion of the new job, but used the often preferred PreparedStatement approach instead.

Deleting a row from the table with Groovy is also easy. The following Groovy script removes both rows (job and employee) just added in this post's examples.

removeJobAndEmployee.groovy

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",
                      "oracle.jdbc.pool.OracleDataSource")
sql.execute("DELETE FROM employees WHERE employee_id = 2000")
sql.execute("DELETE FROM jobs WHERE job_id = ?", ["QU_OPER"])

Because each of the example scripts in this blog post ended with the extension .groovy, they can all be run simply by running "groovy <<scriptName>>". Of course, the particular JDBC driver class for your database of choice (in this case class oracle.jdbc.pool.OracleDataSource in JAR ojdbc6.jar) must also be on the classpath and can be included with the normal -cp option.

Finally, if you have a typo or other database exception, a rather terse message will be printed. To see the full stack trace, using Groovy's -d option. If you are running on Java SE 6, you are likely to see some of the new exceptions that extend SQLException in such exceptional cases.


Conclusion

Groovy can be used within applications that use JDBC, but I especially like it for writing simple tools and scripts that need JDBC. It is also a very rapid method for testing out newly written SQL statements against one's database. Groovy makes an already outstanding technology (JDBC) even easier to use.