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.
- Let's start by creating a new Groovy script file (
queryDb.groovy) where we import theDBUtilmodule and we initialize the database:import static DBUtil.* import groovy.sql.Sql def server = startServer() createSchema() populate()
- Create the
Sqlclass instance and initialize it with the default database connection settings:def sql = Sql.newInstance(dbSettings)
- Write your first query:
sql.eachRow('SELECT * FROM COOKBOOK') { cookbook -> printf '%-20s%s ', cookbook.id, cookbook[1] } - Clean up and kill the server:
sql.close() server.stop()
- By running this script, the output should look as follows:
> groovy -cp /path/to/script/folder queryDb.groovy schema created successfully Database populated: ok 1 30-minute-meals 2 Ministry of food
- Immediately after the first one, try another query, where we pass arguments to the SQL query:
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 } - Running the script again yields the following output:
: using named arguments 2|Jamie Oliver|Ministry of food
- You can also limit the result set by specifying the maximum number of rows and the number of the "page" (offset) to return:
println ': using offset' sql.eachRow( 'SELECT * FROM COOKBOOK', 1, 5) { cookbook -> printf '%s|%s|%s ', cookbook.id, cookbook.author, cookbook.title } - The
querymethod grants you direct access to thejava.sql ResultSetobject, should you need to interact with it:println ': using ResultSet' sql.query('SELECT * FROM COOKBOOK') { rs -> while (rs.next()) { println rs.getString('title') } } - If we want to process all the rows but don't want to use an iterator, we can use the
rowsmethod on theSqlinstance. It returns an instance of theArrayListclass 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 - If you only need the first record of the result set or your query is meant to return only one result, then you can use the
firstRowmethod:def firstCookbook = sql.firstRow('SELECT * FROM COOKBOOK') println firstCookbook?.title
The groovy.sql.Sql class has three main methods to query the database:
eachRowrowsquery
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.