Leveraging propertyMissing and toRowResult() on resultSet

NOTE: Instead of using propertyMissing below, use @Delegate as described in this later post: http://reumann.blogspot.com/2009/04/using-groovy-delegate.html

The groovy documentation is good in some areas, and stinks in others. I wish they covered sql and jdbc stuff a bit more. This is probably basic for many of you, but I figure I'll post it anyway.

First off, when returning sql back using Sql.eachRow, I wanted to build a collection of objects I could use directly. You can't use the ResultSet items returned directly, but I did find you can generate a GroovyRowResult object by calling toRowResult on each object. So your eachRow could look like:

def results = []
sql.eachRow(query) { 
    results <<  it.toRowResult()  
}

Of course the above assumes you want to work the column names as property names directly as they are returned from your sql. This was fine for me, but when dealing with the results, I wanted to call some other properties on each result that would be a composite or calculated from the resulting GroovyRowResult. As an example, I might be getting back firstName and lastName, but I also wanted a "fullName" property comprised of firstName + lastName.

There are probably a lot of ways to do this, but what seemed clean to me was to create a groovy object that I'd stuff each GroovyRowResult into and rely on overriding "propertyMissing" to help me out. My new object acts as an Adapter wrapping the GroovyRowResult. Here's an example:

//Employee.groovy

class Employee {
   
  def row
  
  Employee( row ) {
    this.row = row
  }
  
  //look up missing property in GroovyRowResult: 
  def propertyMissing(String prop) {
    def result =  row."${prop}"
  }
  
  String getFullName() {
    return this.firstName+" "+this.lastName
  }
}

//Usage

def results = []
sql.eachRow("SELECT * FROM Employee") {
  results << new Employee( it.toRowResult() )
}

//see it works:
println "first: ${results[0].firstName}"
println "full: ${results[0].fullName}"