Can I get the full query that a PreparedStatement is about to execute?

Question

I'm working with PreparedStatement with MySQL server.

example:

String myQuery = "select id from user where name = ?";
PreparedStatement stmt  = sqlConnection.prepareStatement(myQuery);
stmt.setString(1, "test");
stmt.executeQUery();
ResultSet rs = stmt.getResultSet();

How can I receive the full SQL query that is about to be executed on the MySQL server?

thanks!

Answer 1

You can't, as Java isn't responsible for constructing it. Prepared statements are supported within MySQL, so Java sends the actual parameterized SQL ("select id from user where name = ?") straight to MySQL along with the parameters

Answer 2

I can tell you what it is. If you're using MySQL 4.1 or newer with Connector/J 3.1 or newer, it will be something like:

PREPARE stmt FROM 'select id from user where name = ?'
SET @a = 'test'
EXECUTE stmt USING @a

This is because MySQL supports server-side prepared statements.

(More likely it uses the binary protocol, but this code is just to make a point)

Answer 3

You cannot really get out the query that will be executed but there are logging APIs that will log database calls for you such as log4jdbc and p6spy.

Answer 4

It's not mandated by the JDBC spec, but several JDBC drivers let the toString of a PreparedStatement return sort-of the query that will be run, and MySQL's Connector/J happens to have this behavior (or at least it did a few years ago).

String myQuery = "select id from user where name = ?";
PreparedStatement stmt  = sqlConnection.prepareStatement(myQuery);
stmt.setString(1, "test");
System.out.println(stmt); // May do what you want!