JDBC Debugging

PreparedStatements in Java are very helpful and allow pre-compiling of queries making calls to the database run much faster. On some operating systems like Himalaya, NSK, pre-compiling queries takes more time than running. Caching preparedstatements and running queries on them makes perfect sense on such platforms! But, these helpful objects have rendered us helpless in a couple of aspects. Well, I can think of two for now :).

1) If you have any problems running them, debugging them is a nightmare. You could accomplish it by constructing the query yourself by adding all string, non-string attributes to the "?"s in PreparedStatements and looking at the log statement. This, I would say is very very cumbersome, especially for queries with many attributes. And a developers mindset wouldn't put that statement in unless a problem is encountered !!

2) Table schema changes make cached preparedstatements useless. Well, I guess you lose something to gain something :). Maybe, there is a better way of managing the caches that I don't know of..

Good news is here! There are a couple of ways that you could tackle the first problem. There is some open source software that can take care of headaches like this. There is a debugging driver that you can take a look at.
This driver works by intercepting your calls to the database (Interceptor pattern) and logging your calls based on whether you turn this particular logging on in your log4j properties.
The only configuration changes on your side would be changes to the "driver URL" and the changes to log4j.xml to include logging properties for the driver. I am sure folks getting weird un-debbugable errors like 'Comma not found' and folks using tools like Hibernate where there is very little control on logging final SQL would find this to be a boon. There is one more tool that could do it too. It's called P6Spy and does just about the same thing as the logging driver!
So, for now folks, go ahead and use PreparedStatements with Confidence :)