Today I realised that I REALLY WANTED Groovy. I was finding Java in the office to be something of a strait jacket. BUT even groovy in a new environment can be tricky. Took me a while to spot that a Proxy was blocking my Grapes (and at my age that can be painful, but it passed).
And maybe I should add a Groovy plugin for the Eclipse that I’m using again after some years. Possibly not the best IDE for Groovy support (but I’ll give it a go again seeing as there had been money thrown at the plugin project), and it’s got to be better than plain groovyConsole. GroovyConsole is actually great for quick tests, but I really wanted some hints on methods available with the POI classes that I was using for the first time. Yep, parsing an Excel spreadsheet, in .xlsx format. Droppings of the devil I say ;-).
Nonetheless I got to iterate over all the rows in my spreadsheet and shoot everything to STDOUT. Then I started looking at the first column, a list of repeated ‘themes’. Repeated in the sense that the spreadsheet is mimicking a database. Picked those out and then it was time for my other job (yep, the family)
Well a database is where the data is going and with a few rules we’ll have it normalised.
I thought I’d play with the data update side here in my home office before calling it a night. Here in my inner sanctum of freelance tranquility ;-)
Ok, I needed a database, and the anointed one is Postgresql (because of PostGIS which adds support for geographic objects to the PostgreSQL object-relational database)
$ sudo apt-get install postgres
and that’ll give me the latest and greatest on my machine, 8.4.4 (on my recently updated Ubuntu 10.04 box).
And set up a database to work with
postgres@lexeNote:~$ psql
psql (8.4.4)
postgres=# create user alex with password ‘alex’;
CREATE ROLE
postgres=# create database alex with owner=alex;
CREATE DATABASE
postgres=# grant all privileges on database alex to alex;
GRANT
postgres=# \q
And then some handy dandy code
import groovy.sql.Sql
import groovy.grape.Grape
@Grapes([
@Grab(group='postgresql', module='postgresql', version='8.4-701.jdbc4')
])
def driver = new org.postgresql.Driver()
def sql = Sql.newInstance("jdbc:postgresql://localhost:5432/alex","alex","alex")
themes = [20:'happy', 40:'nappy', 60:'pappy']
sql.execute("drop table THEMES")
sql.execute("create table THEMES (id integer not null, name varchar(50))")
themes.each { id, name ->
sql.execute("insert into THEMES (id, name) VALUES ($id, $name);")
}
sql.eachRow('SELECT id, name FROM themes WHERE id < 50'){
println "$it.id $it.name"
}
// sql.execute 'DROP TABLE themes;'
"done"
which runs happily producing
20 happy
40 nappy
which are the ones expected from what is now in the database.
$ psql -U alex
alex=> select * from THEMES;id | name
—-+——-
20 | happy
40 | nappy
60 | pappy
(3 rows)
Any gotchas? Well Grapes (Ivy) downloaded first time – it’s just like maven, only the jars went to
$ ls /home/alex/.groovy/grapes/postgresql/postgresql/jars/
postgresql-8.3-603.jdbc4.jar postgresql-8.4-701.jdbc4.jar
Yes two versions, I think I realised that the driver I had used in the office only worked on the older version of postgres. Maven2 actually uses
/home/alex/.m2/repository/postgresql/postgresql/8.3-603.jdbc4/
postgresql-8.3-603.jdbc4.jar
but Groovy + Grapes does it all without an extra POM file to think about. Yep with groovy it’s all in the one place. Nice.
Actually if you are an old Perl programmer like me, you’ll be thinking it’s DBD/DBI all over again, and you’d pretty much be right. And I was doing that in 1996! Perl was cool back then.
Oh yes, GOTCHAS. Well I’m lazy but dropping a table that doesn’t exist will throw an exception, so wrap this bit up if you care to.
As always, Groovy is terse and clean. And a great tool for the kit bag.
Oh boy my littlest one has a fever, so it’s back to work!
