How to Print Out Bind Variables in Java Prepared Statements.

Ever been frustrated by the fact that when you use (as you should do!) bind variables within a java prepared statement you cannot see the actual values that get put into those bind variables? I know I have. For example if you had a class that had some JDBC calls that did the following.... There is no nice out the box way to find out what the ?'s will be resolved to. I've always wanted a method on PreparedStatement that would address this, but alas there still is none. Traditionally I would have done something like the following... Or write your own convoluted method for replacing each ? with the value which is prone to bugs and not being database agnostic.

When using an old jdbc based in-house framework this really wasn't an issue for me, as we had such a method that was prone to bugs but for the most part it worked ok. Since then the work I have been doing has been much more hibernate based. This is where the issue came for me, you can turn on sql logging either by configuring your favourite logging framework to do so, or by turning on the hibernate.show_sql property, which will give you the following output... This sql was generated from the following hql Now apart from this sql being particularly hideous to look at, the question mark is still there, we have no way of possibly knowing what those bind variables were without printing them out at the time the hql is executed.

Enter p6spy.

I did a bit of googling about and found other people had the same issue. And there is an answer to this problem, and its called p6spy. P6spy is a jdbc driver that is a proxy for your real driver. I've only just started to use it, I believe it is capable of many cool things, but the one that I am interested in allows you to print out sql from prepared statements with - yep, you guessed it - the bind variables substituted for the real values.

You can download it from http://www.p6spy.com, it must be fairly stable as the last release was in 2003 (I'm so annoyed that I didn't know about this earlier). There seems to be a little problem with their website as when you click download you get an error while trying to fill in or ignore their survey. Don't panic, once you've ignored the survey go back to the main page and click download again and the files will be there waiting for you to download.

So how do we configure this little beauty? Well the download is p6spy-install.zip, unzip this somewhere safe (and keep in mind that all the files are in the root of the zip file, so you might want to create a directory to unzip it to first). The files we're interested in are p6spy.jar and spy.properties. Copy the jar file into the lib folder of your project and copy the spy.properties file into somewhere that will be on the classpath.

If you look inside spy.properties you will see that it's really very well documented and tells you all you really need to know on how to configure it. Although one thing to note in there is that they have said that the real driver you configure in your application should be set to com.p6spy.engine.P6SpyDriver which is actually incorrect, it should be com.p6spy.engine.spy.P6SpyDriver, the first one doesn't exist and you will get a ClassNotFoundException.

So what I did... In my spy.properties I set the realdriver... (note by default the open source mysql driver is uncommented which was a gotcha for me at first, so i commented it out, and uncommented the above)

Next I altered my code in the first example to the following... And low and behold in the root of my eclipse project on the file system a file called spy.log has appeared. and in it the following... There is lots of other stuff in the log file as well but as you can see its very handy, as it shows you the statement with question marks and with the real values substituted, and also shows you the results set which is passed back :-) Beautiful lol.

And the hql of is output as: Still ugly but at least I can now see what the bind variables are going to be :-)

You can also configure it so it comes out in your normal logging file, the spy.properties file details how you do that.

Oh how I wish I'd have known this 4 years ago.

Technorati Tags: , , , , ,,,

Labels: , , , , , ,