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

Using prepared statements, there is no "SQL query" :

• You have a statement, containing placeholders
  • it is sent to the DB server
  • and prepared there
  • which means the SQL statement is "analysed", parsed, some data-structure representing it is prepared in memory
• And, then, you have bound variables
  • which are sent to the server
  • and the prepared statement is executed -- working on those data

But there is no re-construction of an actual real SQL query -- neither on the Java side, nor on the database side.

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

For debugging purpose, the solutions are either to :

• Ouput the code of the statement, with the placeholders and the list of data
• Or to "build" some SQL query "by hand".

Answer 2

It's nowhere definied in the JDBC API contract, but if you're lucky, the JDBC driver in question may return the complete SQL by just calling PreparedStatement#toString(). I.e.

System.out.println(preparedStatement); 

At least MySQL 5.x and PostgreSQL 8.x JDBC drivers support it. However, most other JDBC drivers doesn't support it. If you have such one, then your best bet is using Log4jdbc or P6Spy.

Alternatively, you can also write a generic function which takes a Connection, a SQL string and the statement values and returns a PreparedStatement after logging the SQL string and the values. Kickoff example:

public static PreparedStatement prepareStatement(Connection connection, String sql, Object... values) throws SQLException {     PreparedStatement preparedStatement = connection.prepareStatement(sql);     for (int i = 0; i < values.length; i++) {         preparedStatement.setObject(i + 1, values[i]);     }     logger.debug(sql + " " + Arrays.asList(values));     return preparedStatement; } 

and use it as

try {     connection = database.getConnection();     preparedStatement = prepareStatement(connection, SQL, values);     resultSet = preparedStatement.executeQuery();     // ... 

Another alternative is to implement a custom PreparedStatement which wraps (decorates) the real PreparedStatement on construction and overrides all the methods so that it calls the methods of the real PreparedStatement and collects the values in all the setXXX() methods and lazily constructs the "actual" SQL string whenever one of the executeXXX() methods is called (quite a work, but most IDE's provides autogenerators for decorator methods, Eclipse does). Finally just use it instead. That's also basically what P6Spy and consorts already do under the hoods.