Java 我可以获得 PreparedStatement 即将执行的完整查询吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3038523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I get the full query that a PreparedStatement is about to execute?
提问by ufk
I'm working with PreparedStatementwith MySQL server.
我正在PreparedStatement使用 MySQL 服务器。
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!
谢谢！
采纳答案by gustafc
It's not mandated by the JDBC spec, but several JDBC drivers let the toStringof a PreparedStatementreturn 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驱动程序让toString一个的PreparedStatement回报将要运行的查询排序的，和MySQL的连接器/ 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!
回答by Michael Mrozek
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
回答by Powerlord
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:
我可以告诉你它是什么。如果您将 MySQL 4.1 或更新版本与 Connector/J 3.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)
（更有可能它使用二进制协议，但这段代码只是为了说明这一点）
回答by krock
回答by gustafc
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[" + "] ...");
}
}
回答by rdj7
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();Right click your application, say
Debug AsselectJava 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 tabinDebug Perspectiveof Eclipse, you will find variables likemyQuery,stmt(according to your code)Whatever you see as value of
stmtwould be the full SQL query you need.
设置断点在
stmt.executeQUery();右键单击您的应用程序，说
Debug As选择Java Application（或任何适用于您的情况，即可能是 SpringBoot 应用程序等。执行使您获得问题中提到的代码的步骤。
如果您签
Variables tab入Debug PerspectiveEclipse，您会发现诸如myQuery, 之类的变量stmt（根据您的代码）无论您看到什么值，
stmt都将是您需要的完整 SQL 查询。
Also, if you don't want to keep looking at this variable always you may try Java Loggingand Print your Full SQL query in Logs.
此外，如果您不想一直查看这个变量，您可以尝试Java Logging在日志中打印您的完整 SQL 查询。
