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

This question already has answers here: How can I get the SQL of a PreparedStatement? (14 answers) Closed 5 years ago.

I'm working with PreparedStatement with MySQL Server.

example:

String myQuery = "select id from user where name = ?";
PreparedStatement stmt  = sqlConnection.prepareStatemen开发者_开发问答t(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?

---

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!

---

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.

---

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

---

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)

---

Hi I implement the following code which fetch SQL from PreparedStatement . No need to use any jar and Driver .

public void printSqlStatement(PreparedStatement preparedStatement, String sql) throws SQLException{
        String[] sqlArrya= new String[preparedStatement.getParameterMetaData().getParameterCount()];
        try {
               Pattern pattern = Pattern.compile("\\?");
               Matcher matcher = pattern.matcher(sql);
               StringBuffer sb = new StringBuffer();
               int indx = 1;  // Parameter begin with index 1
               while (matcher.find()) {
             matcher.appendReplacement(sb,String.valueOf(sqlArrya[indx]));
               }
               matcher.appendTail(sb);
              System.err.println("Executing Query [" + sb.toString() + "] with Database[" + "] ...");
               } catch (Exception ex) {
                   System.err.println("Executing Query [" + sql + "] with Database[" +  "] ...");
            }

    }

---

Slightly different approach from all answers here,

If you are familiar with Debugging options in Eclipse. You may try the following:

1. Set a Breakpoint at stmt.executeQUery();

2. Right click your application, say Debug As select Java Application (or Whatever applicable in your case i.e. may be SpringBoot App etc.

3. Perform step that gets you to code mentioned in the Question.

4. If you check Variables tab in Debug Perspective of Eclipse, you will find variables like myQuery , stmt (according to your code)

5. Whatever you see as value of stmt would be the full SQL query you need.

Also, if you don't want to keep looking at this variable always you may try Java Logging and Print your Full SQL query in Logs.