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.

Time to be more Groovy

Following a recommendation from a colleague, I've had good intentions to learn and use more Groovy for quite a while now, but somehow havent quite had a problem that really presents itself as the ideal starting point.

A few months back I took the initial plunge and used Groovy to do a real job. The job probably wasnt the best choice as a first Groovy project - it was basically pulling data from a multi-gigabyte binary file (actually WebLogic JMS storage files), but it needed to be done and I got the job done with Groovy. In the end I probably wished I hadn't done it this way because I couldn't then use the resulting script anywhere else without getting Groovy set up there first and the alternative was shifting the huge files to the machine where the script was working. I don't think I really learned much idiomatic Groovy either.

Today I have a new problem to solve - basically I have some complex pricing calculations to do based on data in a relational database. The calculations need to be done both in scheduled batch mode and 'on the fly' within a J2EE app for 'what-if' style illustrations. They also need to be very pluggable so that entirely different pricing structures can be supported.

Thinking about this problem, what I need is:-

• Must be runnable from a J2EE app
• Must have good DB integration
• Must have automated unit tests
• Must have concise, expressive logic and preferably polymorphism
• Must be easy to debug

The initial prototype (with lots of simplifying assumptions) was done as a stored procedure with DBUnit test cases, which nicely meets the first three requirements, but runs out of steam on the third. I think that continuing with this approach is going to result in something pretty cumbersome as I gradually remove the simplifying assumptions.

Native Java didn't seem too attractive in terms of really tight DB integration - both JDBC and Hibernate fall a long way short of the simplicity with which SQL can be used in a stored procedure.

So I took another look at Groovy - it seems to fit the bill pretty well. I suspect that GSQL will be rather less cumbersome than either raw JDBC or Hibernate, although probably not quite as closely integrated as running SQL inside a stored proc. I was also worried (based on past experience of Jython and Javascript) about debugging, but it seems that this angle is also covered with both JSwat and Eclipse being supported. Hopefully it will also get me into more idiomatic Groovy in the process...

We shall see!