---

DataSet Object

So far all we've been doing is playing with our database using SQL, albeit using Groovy's very convenient Sql class. But Groovy goes further and offers us the DataSet class which provides a level of abstraction over and above SQL.

To create a DataSet object we can take our Sql object and do the following:

def ds=sql.dataSet('pc')
ds.each {println it.type + ' ' + it.os}

The constructor takes not a piece of SQL querying Derby, but the name of a complete table. What we get back in this instance is the pc table, and we iterate through it to print the type and os fields of each row of data.

In fact each row of data contains a Map of key:value pairs, where the key is the database field name and the value the contents of that field. We can see this with the following couple of lines:

rowdata=ds.rows()
rowdata.each { println it }

Running this produces the following output:

["TYPE":"Desktop", "SERIAL":"01010", "OS":"Linux", "EMP_NO":1, "CODE_NO":1]
["TYPE":"Laptop", "SERIAL":"101010", "OS":"BSD", "EMP_NO":2, "CODE_NO":2]
["TYPE":"Desktop", "SERIAL":"101010", "OS":"XP", "EMP_NO":3, "CODE_NO":12]

There's a lot of boilerplate Java code that we'd have to write to do something so straightforward and useful, yet Groovy does it easily and in a manner that feels very intuitive.

There's more. Now that we've got this data, what if we want to filter it or just grab subsets? Then we can use the findAll method:

def ds=sql.dataSet('pc')
flt = ds.findAll{ it.emp_no > 2}
flt.each { println it.os }

Here we are creating a DataSet of the pc table, then applying a filter so that we only have the rows of data where the os is not equal to 'XP'. Multiple clauses can be included, for example to get those rows where the os is not XP and the machine type is not laptop, we can create the following:

flt = ds.findAll{ it.os != 'XP' && it.type != 'Laptop'}

The icing on the cake for the DataSet is that you can also use it to add data to the underlying table, without the need to explicitly create a SQL INSERT. Let's say we want to add a new user to our user table. The following code does the job:

ds=sql.dataSet('users')
ds.add(first_name:'joe',last_name:'bloggs',emp_no:100)

That's it - data added to the underlying table. Unfortunately there's not a corresponding delete method, but perhaps that's something for a future release.

Conclusion

As we've seen, the combination of iterators, closures and Groovy's Sql and DataSet classes makes for a powerful set of database programming tools. Although we've used Apache Derby, which is a great piece of software, it should be clear that everything we've looked at is independent of the RDBMS, it would work as well using HSQLDB, MySQL, PostgreSQL and anything else with JDBC drivers. We've only touched the surface in this tutorial, but it should be clear that there's a lot of value to be had in using Groovy for database applications.