Usually data is more often read than it is written, and performing data queries is the most important task done by database-driven applications. The standard JDBC API is rather verbose and adds a lot of boilerplate code around actual queries. Groovy provides more elegance to the querying code by simplifying access and mapping logic.
This recipe contains a highlight of some of the methods you can use to query a database using Groovy.
As for the previous recipes from this chapter, we will use the DBUtil
class defined in the Creating a database table recipe as well as the same database structure.
Considering that this recipe is about querying data, we need to populate our database first. The database population code is quite verbose, so we leave it out of this recipe and we ask the reader to refer to the accompanying code for this chapter. The DBUtil.groovy
available in the download section has been refactored with two new methods:
The following steps will let us dive into SQL queries with the help of Groovy.
queryDb.groovy
) where we import the DBUtil
module and we initialize the database:import static DBUtil.* import groovy.sql.Sql def server = startServer() createSchema() populate()
Sql
class instance and initialize it with the default database connection settings:def sql = Sql.newInstance(dbSettings)
sql.eachRow('SELECT * FROM COOKBOOK') { cookbook -> printf '%-20s%s ', cookbook.id, cookbook[1] }
sql.close() server.stop()
> groovy -cp /path/to/script/folder queryDb.groovy schema created successfully Database populated: ok 1 30-minute-meals 2 Ministry of food
println ': using named arguments' sql.eachRow('SELECT * FROM COOKBOOK ' + 'WHERE id = :id ', [id:2]) { cookbook -> printf '%s|%s|%s ', cookbook.id, cookbook.author, cookbook.title }
: using named arguments 2|Jamie Oliver|Ministry of food
println ': using offset' sql.eachRow( 'SELECT * FROM COOKBOOK', 1, 5) { cookbook -> printf '%s|%s|%s ', cookbook.id, cookbook.author, cookbook.title }
query
method grants you direct access to the java.sql ResultSet
object, should you need to interact with it:println ': using ResultSet' sql.query('SELECT * FROM COOKBOOK') { rs -> while (rs.next()) { println rs.getString('title') } }
rows
method on the Sql
instance. It returns an instance of the ArrayList
class containing the result data, as shown here:println ': using rows method' List allCookbooks = sql.rows('SELECT * FROM COOKBOOK') assert allCookbooks.size() == 2 println allCookbooks[0]?.title
firstRow
method:def firstCookbook = sql.firstRow('SELECT * FROM COOKBOOK') println firstCookbook?.title
The groovy.sql.Sql
class has three main methods to query the database:
eachRow
rows
query
Each method has several overloaded versions (eachRow
, for instance, has 20 overloaded methods) that can be used to tweak the way the results are fetched and presented. eachRow
, in its simplest form, requires an SQL statement and a closure to process each row of data. In step 3, we can see an example: eachRow
executes SQL's SELECT
query on the COOKBOOK
table and processes all its rows. We then iterate (as the name each
indicates) over each row and print the result. But what is the actual object we are iterating on. Let's look at the code once again:
sql.eachRow('SELECT * FROM COOKBOOK') { cookbook -> printf '%-20s%s ', cookbook.id, cookbook[1] }
The cookbook
variable is used as if it was an object: note how we call the method id
on it. At the same time, we treat it as a List
, by referencing a column by index. So what is it? It's an object of type groovy.sql.GroovyResultSet
which wraps the underlying java.sql.ResultSet
. The property invocation (id
) is intercepted and mapped to the column name, if the two match. Similarly, referencing the column name by index achieves the same result (starting from zero, allowing negative indexes that count from the end).
The various rows
method available in the Sql
class have all the same outcome: they return the result set as a List, allowing a developer to leverage the rich Groovy Collection API, with methods such as find
, findAll
, grep
, any
, and every
.
The query
method (step 9) trades some convenience for a higher degree of customization in the iteration process. The method produces a java.sql.ResultSet
and has the usual quirks associated with this rather old Java class. The method next
must be called to move the cursor to the next row, you need to call type-specific getters (getString
, getDate
, and so on) and indexes start at one instead of zero. The query
method still hides some of the ceremoniousness related to resource management by automatically opening/closing the connection and the statement.
If you are doing many similar queries within the same code block you can cache prepared SQL statement objects with the help of the cacheStatements
method, which accepts a Closure
as its only parameter. The Closure
may contain other groovy.sql.Sql
calls:
sql.cacheStatements { sql.eachRow('SELECT * FROM RECIPE ' + ' WHERE COOKBOOK_ID = 1') { recipe -> sql.eachRow('SELECT COUNT(*) FROM INGREDIENTS ' + 'WHERE recipe_id = ?', [recipe.id]) { ... } } }
As you can notice we do a nested SQL query, which will benefit from being prepared and cached for performance reasons.