Groovy is the Only Way To Code

Well - any sort of awesome scripting language will do. I had a particularly nasty query that I was going to need to write today to see if there were people who had a value in a column in one table and no backing data in another table. This is an odd bit of legacy data model stuff that doesn't utilize any sort of foreign key constraints, and apparently there was a blip at some point more than 6 mos back that caused a bit of data corruption. Instead of figuring out this bizarre query, I thought, "Gee Kirk, you are always looking for a reason to use Groovy - here it is!"

Here's what I came up with:

import groovy.sql.Sql

sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "mydbuser",
                     "mydbpass", "com.mysql.jdbc.Driver" )

def badIdList = []
sql.eachRow( "select id, weird_col, active from table1 where active = 1 and weird_col is not null" ) {

    myId = it.id

    checkCount =
      sql.firstRow( "select count(*) as count from table2 where table1_id = ${myId} and is_right = 1").count

    if ( checkCount == 0 )
    {
      println( "bad id: ${myId}")
      badIdList.add( myId )
    }

}

println( badIdList )

Now I guess I am just a really big nerd, but I felt oh so satisfied after writing that. For sh*!ts and grins, I wrote the same thing in java, and it was a lot of code, and it was annoying, and you had to do the while rs.hasNext thing, and get the current resultset, and then do the rs.getString() thing, and do the Statement = this that. It's a lot of code to do very little, and it kind of sucks. Now granted most people don't really do a ton of direct to db stuff anymore, with all the ORM solutions like Hibernate for Java and ActiveRecord for Ruby/Rails, but when you need something surgical like this, it just can't be beat. Man oh man it's awesome.

My name is Kirk, and I am a freakin nerd.