Groovy SQL to the resque

October 24th, 2009

The case is an sql table with about 30 columns that I have to query and export some (or all) data. In this case plain csv format should be just fine!


The problem here is that I really don't want to query the database and start exporting the fields manually from the result set. In this case the possibility of a plain typo mistake is really high and really hard to find (ok! Im lazy!)

The solution

A solution to this problem (not lazyness) is to get the table metadata and use them to get all the fields of the selected rows. Ofcourse you have to build that or use something ready. Here comes the groovy language...

Sql sql = new Sql(ConnectionUtil.getDataSource());
def keyset = null; //A place to keep the column names
sql.eachRow("select * from theTableName") { row ->
  //If the keyset is null create one and add the column names
  if (keyset == null) {
    //We want all the column names exept the one named 'id
    keyset = row.toRowResult().keySet().findAll { it != 'id' }

    //create a nice header for our output
    keyset.each() { print "${it}," }
    print '\n'

  keyset.each() { k ->
    print """row."$k","""
  print '\n'

This is it! All we've done is to use the groovy's build in groovy.sql.GroovyRowResult (that is row.toRowResult()) to get all the metadata we'll need. Knowing the column names we again use groovy's row.columnName to get the value of the columnName of the row

Now combine that with a groovlet and you will really save time! (did I find some kind of solution for laziness?) v3.6.1 © Γιώργος Βαλοτάσιος - CSS inspired by Adam Wathan's blog
The greek name "Γιώργος" is also know as Yoryos, Georgios or just George which seems to be easier to most english speaking people. If you are trying to find out what Βαλοτασιος means, just think of it as Valotasios and you should be fine.