How can I get the SQL of a PreparedStatement?

Question

I have a general Java method with the following method signature:

private static ResultSet runSQLResultSet(String sql, Object... queryParams)

It opens a connection, builds a PreparedStatement using the SQL statement and the parameters in the queryParams variable-length array, runs it, caches the ResultSet (in a CachedRowSetImpl), closes the connection, and returns the cached result set.

I have exception handling in the method that logs errors. I log the SQL statement as part of the log since it's very helpful for debugging. My problem is that logging the String variable SQL logs the template statement with ?'s instead of actual values. I want to log the actual statement that was executed (or tried to execute).

So... Is there any way to get the actual SQL statement that will be run by a PreparedStatement? (Without building it myself. If I can't find a way to access the PreparedStatement's SQL, I'll probably end up building it myself in my catches.)

Answer 1

If you are using prepared statements, then there will be no "SQL query" :

You have the statement, containing placeholders, it will be sent to the DB server and prepared there which means that the SQL statement is "analyzed", parsed, some data-structure representing it will be prepared in memory. And, then, you will have bound variables that will be sent to the server and the prepared statement will be executed -- working on those data. But there will be no reconstruction of an actual real SQL query -- neither on the database side nor on the java side.

Hence, there is no way to get the prepared statement's SQL -- as there is no such SQL.

For debugging, the solutions will be either: 

• Ouput the code of the statement, with the placeholders and the list of data.

Or

• To "build" some SQL query "by hand".