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:
- This is how to insert data into a table without (explicitly) issuing an SQL 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) - This is how to fetch the data from the database using Dataset:
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.