Get query from java.sql.PreparedStatement [duplicate]

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.

Answer 1

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 currently do so are at least the PostgreSQL 8.x and MySQL 5.x JDBC drivers.

In the case that your JDBC driver doesn't support it, your best bet is using a statement wrapper which records all calls to setXxx() methods and finally populates a SQL string on toString() based on the recorded information. An existing library which does that is P6Spy. In the meanwhile, post an enhancement request to the development team of your JDBC driver and hope that they'll implement the desired toString() behavior as well.

Answer 2

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'