JDBC generation of SQL in PreparedStatement

Question

I had a really huge problem recently which took me a lot of time to debug. I have an update statement which updates 32 columns in table. I did that with PreparedStatement. Accidentaly I deleted one setParameter() call so update could not be finished successfully.

I got exception from JDBC (Apache Derby) telling: "At leas one parameter is not initialized" and was not able to figure out which parameter is not set since driver would not tell you nothing about name or ordinal number of at least first parameter which is not set...

I was googleing unsuccessfully for some utility which will produce plain old SQL out of (nearly-finished) prepared statement. It would help a lot in situations like this one, since I will be able to see what is not set.

Have anyone faced this problem? Got any solution?

Answer 1

Have a look at P6Spy. It can intercept all your JDBC calls and log them before forwarding them onto your database.

Alternatively, think about using Springs JDBCTemplate which can take out alot of your boilerplate JDBC coding and help avoid these kind of mistakes. You don't need the rest of the Spring framework to use this bit.

Answer 2

Since the parameters in a prepared statement are just a List or Map in the PreparedStatement Object you should be able to inspect the values.

Also you could write a very simple Wrapper around you jdbc driver that creates wrapped PreparedStatements and logs all parameters and there settings before actually executing the statement.