Building a simple ORM framework

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.

Getting ready

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...