Using Grails to Store Generic Data Sets

TASK: Build a generic data structure to represent grid data, then use the Grails framework to implement it.

At a high level, what is needed is a set of tables that can hold sets of varying numbered rows and columns. This means we have collections of grids representing the individual data sets. Each grid has a collection of rows (we’ll call them results for this discussion) and data columns. Each result has a collection of data points. And finally, each datapoint is associated with a data column.

I’ve been using Grails now for about a month and I’ve found the documentation to be lacking in comparison with other frameworks that I’ve used. So hopefully this example of representing result sets will be usefull to others who, like myself, learn best from checking out example code and applying it to the task at hand. For the purposes of this example, I am using Grails 1.0.3, MySQL 5.0, NetBeans 6.5, and Navicat 8 Lite for MySQL (very helpful for setting up MySQL for those who aren't keen on command-line stuff). I won't get into the whole setup of the Grails plugin for NetBeans and I'll assume that you already have your MySQL database created. The tables will be automagically generated by Grails so all you need is an empty database.

First, the domain classes:

DataSet:
class DataRow {
    int Id
    int DataSetId
    static hasMany = [results:DataPoint]
}

DataPoint:
class DataPoint {
  int Id
  DataPointColumn Column
  String Val

}

DataPointColumn 
class DataPointColumn {
  int Id
  String ColumnName
}

And now the code to save your data. For this example, you will have some java structure that stores results rows and columns. I won't muddy the waters here with my implementation of that since this is supposed to be about Grails. Suffice it to say that you will need to have some mechanism of retrieving a collection of column names, and each data item should be associated with a column.

          
IResults res = <execute some query>
String[] column_names = res.getColumnNames() 

for (Result r : res.getResults())
{
    def savedResults = new DataRow()
    savedResults.DataSetId = res.getId()

    for (int i=0; i< column_names.length; i++)
    {
        def col = DataPointColumn.find("from DataPointColumn as dp where dp.columnName=?",[column_names[i]])                                            
        if (col==null){
        col = new DataPointColumn()
        col.ColumnName = column_names[i]
        col.save()
    }
    String val = r.getDatapointValue(column_names[i]);

    def dp = new DataPoint()
    dp.Val = val
    dp.Column = col

    savedResults.addToResults(dp)

}

savedResults.save() // Save the row 

This is actually a slight modification to the actual code that I implemented so that my business-specific names and code are left out. The most interesting piece of the code (IMHO) is the addTo* function. There's actually a nice explanation of how that works on the Grails site (Grails: addTo*). If you absolutely must use java (I'm trying to hide my .NET bias here, really) Grails is a good MVC framework to use. Any changes to the domain model are automatically implemented in the database. One thing you will find usefull in tracking down errors is this code snippet:
(b is some domain object)

if( !b.save() ) {
   b.errors.each {
        println it
   }
}

As of right now, Grails does not have any built-in debugging support, which means that you are limmited to spitting out error descriptions. Hopefully someday it will be possible to step through a Grails app, but for the same price as this short example, you get a pretty slick development environment and framework.