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?

score 12 · Accepted Answer · answered Jun 14 '10 at 15:49

12

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!

answered Jun 14 '10 at 15:49

gustafc

28,465
7
73
99

1

thank you! stmt.toString() resolved the issue. – ufk Jun 14 '10 at 16:03
1

If stmt.toString() doesn't work, try following the solution provided in this article: http://www.javaworld.com/javaworld/jw-01-2002/jw-0125-overpower.html?page=1 – A Costa Aug 07 '13 at 13:10
It works on Postgres driver (9.1) too. – Adán Jul 30 '15 at 09:36

score 4 · Answer 2 · answered Jun 14 '10 at 15:37

4

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.

answered Jun 14 '10 at 15:37

krock

28,904
13
79
85

score 3 · Answer 3 · answered Jun 14 '10 at 15:35

3

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

answered Jun 14 '10 at 15:35

Michael Mrozek

169,610
28
168
175

Actually some JDBC drivers do the work on the client side and send a fully-parameterized query to the server (I don't know which way the MySQL driver uses). There is no standardized way to get the "complete" SQL statement anyway. – Joachim Sauer Jun 14 '10 at 15:37
@Joachim Sauer: MySQL Connector/J 3.1 and newer will user server-side prepared statements. – Powerlord Jun 14 '10 at 15:41

Powerlord · Answer 4 · 2010-06-14T15:42:29.950

3

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)

edited Jun 14 '10 at 15:42

answered Jun 14 '10 at 15:36

Powerlord

87,612
17
125
175

score 3 · Answer 5 · answered Dec 13 '16 at 05:55

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[" +  "] ...");
            }

    }

rdj7 · Answer 6 · 2017-08-24T08:37:58.273

Slightly different approach from all answers here,

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

Set a Breakpoint at stmt.executeQUery();
Right click your application, say Debug As select Java Application (or Whatever applicable in your case i.e. may be SpringBoot App etc.
Perform step that gets you to code mentioned in the Question.
If you check Variables tab in Debug Perspective of Eclipse, you will find variables like myQuery , stmt (according to your code)
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.

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

6 Answers6

Linked