我可以获得PreparedStatement即将执行的完整查询吗？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

How can I get the SQL of a PreparedStatement? 12 answers

如何获取PreparedStatement的SQL? 12个答案

I'm working with PreparedStatement with MySQL server.

我正在使用带有MySQL服务器的PreparedStatement。

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?

如何收到即将在MySQL服务器上执行的完整SQL查询?

thanks!

6 个解决方案

#1

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).

这不是JDBC规范的强制要求,但是几个JDBC驱动程序让PreparedStatement的toString返回将要运行的查询的排序,并且MySQL的Connector / J碰巧有这种行为(或者至少它在几年前发生过) 。

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!

#2

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.

您无法真正获得将要执行的查询,但有一些日志API会记录您的数据库调用,例如log4jdbc和p6spy。

#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

你不能,因为Java不负责构建它。 MySQL中支持预处理语句,因此Java将实际参数化SQL(“select id from user where name =?”)直接发送到MySQL以及参数

#4

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:

我可以告诉你它是什么。如果您使用的是带有Connector / J 3.1或更高版本的MySQL 4.1或更高版本,它将类似于:

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.

这是因为MySQL支持服务器端预处理语句。

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

(更有可能它使用二进制协议,但这段代码只是为了说明一点)

#5

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

嗨,我实现了以下从PreparedStatement获取SQL的代码。无需使用任何jar和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[" +  "] ...");
            }

    }

#6

Slightly different approach from all answers here,

这里所有答案略有不同,

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

如果您熟悉Eclipse中的调试选项。您可以尝试以下方法:

Set a Breakpoint at stmt.executeQUery();

在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.

右键单击您的应用程序,说“调试为选择Java应用程序”(或者适用于您的情况,即可能是SpringBoot App等。
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)

如果你在Eclipse的Debug Perspective中检查Variables选项卡,你会发现像myQuery,stmt这样的变量(根据你的代码)
Whatever you see as value of stmt would be the full SQL query you need.

无论你认为什么是stmt的价值,你都需要完整的SQL查询。

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.

此外,如果您不想继续查看此变量,则可以尝试在日志中进行Java日志记录和打印完整SQL查询。

#1

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!

#2