Get query from java.sql.PreparedStatement

Question

In my code I am using java.sql.PreparedStatement.

I then execute the setString() method to populate the wildcards of the prepared statement.

Is there a way for me to retrieve (and print out) the final query before the executeQuery() method is called and the query is executed? I Just want this for debugging purposes.

Thanks.

Answer 1

You could try calling toString() on the prepared statement after you've set the bind values.

PreparedStatement query = connection.prepareStatement(aSQLStatement);
System.out.println("Before : " + query.toString());
query.setString(1, "Hello");
query.setString(2, "World");
System.out.println("After : " + query.toString());

This works when you use the JDBC MySQL driver, but I'm not sure if it will in other cases. You may have to keep track of all the bindings you make and then print those out.

Sample output from above code.

Before : com.mysql.jdbc.JDBC4PreparedStatement@fa9cf: SELECT * FROM test WHERE blah1=** NOT SPECIFIED ** and blah2=** NOT SPECIFIED **
After : com.mysql.jdbc.JDBC4PreparedStatement@fa9cf: SELECT * FROM test WHERE blah1='Hello' and blah2='World'

Answer 2

This is 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);

To my experience, the ones which do so are at least the PostgreSQL 8.x and MySQL 5.x JDBC drivers. For the case your JDBC driver doesn't support it, your best bet is using a statement wrapper which logs all setXxx() methods and finally populates a SQL string on toString() based on the logged information. For example Log4jdbc or the old and well known P6Spy.