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!

An Accident Waiting to Happen

My client is in the process of rolling out a multi-tier J2EE application which has been in development for a couple of years. A couple of weeks ago, it started to run into mysterious 'hanging' states in production. Many crisis meetings took place, DBAs and app server experts were engaged, actions happened and everybody had a generally stressful time without quite getting to the bottom of the issue.

In a quieter moment, going over emails from the DBAs, thread dumps and source code, I spotted a problem. A Java class was querying Oracle for the next value of a sequence, putting it in an instance variable and then using it later to do an insert or an update. Nothing too scary on the face of it... until you realise that an instance of this class was being held in an instance variable in another class, and that class was a WebLogic web service skeleton.

Now the WebLogic manual is very clear that you must write thread-safe code for your skeleton classes because the server will use a single instance of the skeleton to service all client requests. End result: a single instance of our class is running in multiple threads and the database keys are getting mixed up between threads, resulting in multiple threads trying to lock the same database row and causing the app to hang.

So should we blame the developers of this class?, well maybe, but bear in mind that the class in question is not itself a web service skeleton - it just happens to be used by one. Maybe we should blame the developer of the skeleton?, well that may be closer to the mark, but I have another option - maybe we should blame the developer of the web service framework!

My question is: is this rule reasonable? The expectation nowadays is that developers will quickly be up to speed with new technologies and that businesses will want to take advantage of them quickly. We cant expect development shops to be peopled entirely by seasoned professionals, so surely the burden should fall on the developers of frameworks to implement default behaviours which are safe.

Having hit this issue, I cross-checked what Axis does in the same situation. At first, I couldn't find any definitive statement in the manual on the subject, so I ran a service in the debugger and found that it created lots of instances of my skeleton as calls arrived. I eventually found that the bahaviour is configurable via the 'scope' property in the web service deployment descriptor (WSDD). The default seems to be 'session' - i.e. an instance of the skeleton is created for each client that connects. Not 100% safe, not 100% optimal performance-wise, but probably safe enough for most situations.

My client now needs to either check all web-service related code for thread safety or switch to another web service framework that at least makes this behaviour configurable... and then roll out the change into production and retro-fit the two releases which are still in the pipeline. This is not going to be a painless process.

I will just be thankful that I wasn't around when the framework was chosen or when the code in question was written - would I have spotted this accident before it happened?