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….

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection("jdbc:mysql://localhost/message_board", "root", "");

statement = connection.prepareStatement("select * from messages where id = ?");
statement.setLong(1,1L);

resultSet = statement.executeQuery();

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…


long id = 1L;
String sql = "select * from messages where id = ?"
logger.info(sql + "; ?1=" + id);

statement = connection.prepareStatement(sql);
statement.setLong(1,id);

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…


select message0_.id as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where message0_.id=?

This sql was generated from the following hql

from Message where id = ?

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…

# mysql Connector/J driver
realdriver=com.mysql.jdbc.Driver

(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…

Class.forName("com.p6spy.engine.spy.P6SpyDriver");

connection = DriverManager.getConnection("jdbc:mysql://localhost/message_board", "root", "");

statement = connection.prepareStatement("select * from messages where id = ?");
statement.setLong(1,1L);

resultSet = statement.executeQuery();

And lo 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…

1217374504582|6|0|statement|select * from messages where id = ?|select * from messages where id = 1
1217374504595|-1||resultset|select * from messages where id = 1|email_address = myemail@nospam.com, id = 1, inserted_at = 2008-07-29 00:00:00.0, remote_host = localhost, text = Thank you for showing me the wonders of p6spy

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

from Message where id = ?

is output as:


1217374913350|5|2|statement|select message0_.id as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where message0_.id=?|select message0_.id as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where message0_.id=1
1217374913369|-1||resultset|select message0_.id as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where message0_.id=1|email2_2_ = myemail@nospam.com, remote4_2_ = localhost

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: , , , , ,,,

Advertisements

3 Replies to “How to Print Out Bind Variables in Java Prepared Statements.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s