The groovy.sql.Sql
class meets the needs of querying and modifying data stored in a relational database. Still, as the name implies, this class requires knowledge of the SQL language and has a strong relationship with the verbosity of the Java's JDBC API.
Wouldn't be great if we could access and insert data into a database table without writing a single line of SQL? The groovy.sql.DataSet
class can make that happen.
In this recipe, we are going to cover a simple approach to building a database mapping solution using Groovy facilities.
For this recipe, we are going to create a new table, named EMPLOYEE
. Create a new script, named orm.groovy
and add the following code:
import static DBUtil.* import groovy.sql.Sql import groovy.sql.DataSet class Person { Integer id String name String lastName Integer age Integer department } def server = startServer() def sql = Sql.newInstance(dbSettings) sql.execute('''CREATE TABLE EMPLOYEE ( ID INTEGER PRIMARY KEY, NAME VARCHAR(100), LASTNAME VARCHAR(100), AGE INTEGER, DEPARTMENT INTEGER)''')
Similarly to the other recipes in this chapter, we make use of the DBUtil
class. Therefore, the script must be invoked by specifying the location of DBUtil.groovy
in the classpath, as follows:
groovy -cp /path/to/script/folder orm.groovy
The script also contains the definition of the class Person
that we will map to the EMPLOYEE
table.
Let's start adding some code after the table creation statement:
def persons = new DataSet(sql, 'EMPLOYEE') person.add(name: 'John', lastname: 'Willis', id: 1, age: 40, DEPARTMENT: 100) person.add(name: 'Alfred', lastname: 'Morris', id: 2, age: 50, DEPARTMENT: 101) person.add(name: 'Mickey', lastname: 'Rourke', id: 3, age: 30, DEPARTMENT: 101) person.add(name: 'Santo', lastname: 'Mongo', id: 4, age: 45, DEPARTMENT: 101)
persons.each { println "employee ${it.id} - ${it.name} ${it.lastname}" }
This will yield something similar to the following:
console employee 1 - John Willis employee 2 - Alfred Morris
The DataSet
represents a list, in which each element is a map of key/value pairs, matching the corresponding table row. Row data can be easily accessed through property names. The name of the property is case-insensitive, but must match the original column name.
The DataSet
class issues the relevant SQL statements only when the each
method is invoked.
Here is an example of using the DataSet
class with some WHERE
conditions:
persons.findAll { it.age > 45 }.each { println it }
The output may be as follows:
>[ID:2, NAME:Alfred, LASTNAME:Morris, AGE:50, DEPARTMENT:101] >[ID:4, NAME:Santo, LASTNAME:Mongo, AGE:45, DEPARTMENT:101]
In this case, the findAll
produces an SQL statement that reflects the expression within the closure. This generated statement is encapsulated in the returned persons
data set.
Filters inside the Dataset
can be combined to create more powerful query conditions:
persons.findAll { it.department == 101 && it.age < 50 } .sort { it.lastname } .each { println it }
>[ID:4, NAME:Santo, LASTNAME:Mongo, AGE:45, DEPARTMENT:101] >[ID:3, NAME:Mickey, LASTNAME:Rourke, AGE:30, DEPARTMENT:101]
Did you spot the sort
? As for any other collection in Groovy you can indeed sort the result.