Groovy for database business logic

My first impressions of Groovy as a way to solve my database-oriented business logic problem are very good.

I started out by re-implementing the stored procedure logic that I'd prototyped as a Groovy class. I got it working and passing the unit test in a couple of hours. My first implementation was fairly horrid, but over the next few hours I figured out some more of the Groovy idioms and removed some Java style code which made the whole thing more expressive and easier to follow.

One thing which took some figuring out was date handling and date arithmetic. I started out using the java Calendar class, which was somewhat verbose. I then found http://groovy.codehaus.org/JN0545-Dates which explained some useful stuff like the Groovy Duration classes. These made my code much more concise, although I'm not too comfortable about the fact that you can do 'duration + java date', but not the other way round. I can see exactly why it is so (because java.util.Date doesnt have a 'plus' method), but it's rather counter-intuitive.

While it was in the state of being a 'like for like' copy, I also took the opportunity to do a couple of comparisons:-

• lines of code (including blanks and comments) - 66 for Groovy, 87 for the stored procedure - the savings were mainly down to the elimination of variable declarations at the start of the procedure and the much simpler looping syntax with Groovy.
• performance - I was only able to test on my local Windows machine, which is not ideal, but with a sensible amount of data, the Groovy version was taking about 7% longer than the stored procedure version.

Since my code has an inner loop with a complex SQL insert/select statement in it, I thought it worth looking at what Groovy was doing with this. Using the (Eclipse) debugger, I found that it was creating a new PreparedStatement for each iteration of the loop, which I thought may be costing some performance. I tried expanding this operation out to explicitly create a PreparedStatement outside the loop and execute it inside the loop. The result was zero improvement in performance, but some pretty ugly code so I undid this change.

Overall, Groovy looks like it is meeting my requirements as a way to implement complex database business logic, so I'm now about to start on turning my prototype logic into something which can meet the rather more complex requirements of the real thing.