在 Java 中记录 PreparedStatement
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/218113/
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
Logging PreparedStatements in Java
提问by kolrie
One thing that always been a pain is to log SQL (JDBC) errors when you have a PreparedStatement instead of the query itself.
当您有 PreparedStatement 而不是查询本身时，总是令人头疼的一件事是记录 SQL (JDBC) 错误。
You always end up with messages like:
你总是以这样的消息结束：
2008-10-20 09:19:48,114 ERROR LoggingQueueConsumer-52 [Logger.error:168] Error
executing SQL: [INSERT INTO private_rooms_bans (room_id, name, user_id, msisdn,
nickname) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE room_id = ?, name = ?,
user_id = ?, msisdn = ?, nickname = ?]
Of course I could write a helper method for retrieving the values and parsing/substitute the question marks with real values (and probably will go down that path if I don't get an outcome of this question), but I just wanted to know if this problem was resolved before by someone else and/or if is there any generic logging helper that would do that automagically for me.
当然，我可以编写一个辅助方法来检索值并用实际值解析/替换问号（如果我没有得到这个问题的结果，可能会沿着这条路走下去），但我只是想知道是否这个问题之前已经被其他人解决了，和/或是否有任何通用的日志助手可以自动为我解决这个问题。
Edited after a few answers:
几个答案后编辑：
The libraries provided so far seems to be suitable to logging the statements for debugging, which no doubt is useful. However, I am looking to a way of taking a PreparedStatement itself (not some subclass) and logging its SQL statement whenever an error occur. I wouldn't like to deploy a production app with an alternate implementation of PreparedStatement.
到目前为止提供的库似乎适合记录调试语句，这无疑是有用的。但是，我正在寻找一种方法来获取 PreparedStatement 本身（不是某个子类）并在发生错误时记录其 SQL 语句。我不想使用 PreparedStatement 的替代实现来部署生产应用程序。
I guess what I am looking for an utility class, not a PreparedStatement specialization.
我想我正在寻找一个实用程序类，而不是 PreparedStatement 专业化。
Thanks!
谢谢！
采纳答案by user418544
I tried log4jdbcand it did the job for me.
我尝试了log4jdbc，它为我完成了这项工作。
SECURITY NOTE: As of today August 2011, the logged results of a log4jdbc prepared statement are NOT SAFE to execute. They can be used for analysis, but should NEVER be fed back into a DBMS.
安全说明：截至 2011 年 8 月的今天，log4jdbc 准备好的语句的记录结果无法安全执行。它们可用于分析，但绝不应反馈到 DBMS。
Exampleof log generated by logjdbc:
实施例通过logjdbc生成的日志的：
2010/08/12 16:30:56 jdbc.sqlonly org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) 8. INSERT INTO A_TABLE (ID_FILE,CODE1,ID_G,ID_SEQUENCE,REF,NAME,BAR,DRINK_ID,AMOUNT,DESCRIPTION,STATUS,CODE2,REJECT_DESCR,ID_CUST_REJ) VALUES (2,'123',1,'2','aa','awe',null,'0123',4317.95,'Rccc','0',null,null,null)
2010/08/12 16:30:56 jdbc.sqlonly org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) 8. INSERT INTO A_TABLE (ID_FILE,CODE1,ID_G,ID_NAME,SEQUENCE,REF,REF) ,DRINK_ID,AMOUNT,DESCRIPTION,STATUS,CODE2,REJECT_DESCR,ID_CUST_REJ) VALUES (2,'123',1,'2','aa','awe',null,'0123',4317.95,'Rccc',' 0',null,null,null)
The library is very easy to setup:
该库非常容易设置：
My configuration with HSQLDB:
我对HSQLDB 的配置：
jdbc.url=jdbc:log4jdbc:hsqldb:mem:sample
With Oracle:
使用甲骨文：
jdbc.url=jdbc:log4jdbc:oracle:thin:@mybdd:1521:smt
jdbc.driverClass=net.sf.log4jdbc.DriverSpy
logback.xml :
logback.xml :
<logger name="jdbc.sqlonly" level="DEBUG"/>
Too bad it wasn't on a maven repository, but still useful.
From what I tried, if you set
太糟糕了，它不在 Maven 存储库中，但仍然有用。
根据我的尝试，如果你设置
You will only get the statements in error, however, I don't know if this library has an impact on performance.
你只会得到错误的语句，但是，我不知道这个库是否对性能有影响。
回答by Anand Rockzz
回答by skaffman
This is very database-dependent. For example, I understand that some JDBC drivers (e.g. sybase, maybe ms-sql) handle prepared statements by create a temporary stored procedure on the server, and then invoking that procedure with the supplied arguments. So the complete SQL is never actually passed from the client.
这是非常依赖于数据库的。例如，我知道某些 JDBC 驱动程序（例如 sybase，也许是 ms-sql）通过在服务器上创建一个临时存储过程，然后使用提供的参数调用该过程来处理准备好的语句。所以完整的 SQL 从来没有真正从客户端传递过。
As a result, the JDBC API does not expose the information you are after. You may be able to cast your statement objects the internal driver implementation, but probably not - your appserver may well wrap the statements in its own implementation.
因此，JDBC API 不会公开您想要的信息。您可能能够将您的语句对象转换为内部驱动程序实现，但可能不能 - 您的应用程序服务器可能会将语句包装在其自己的实现中。
I think you may just have to bite the bullet and write your own class which interpolates the arguments into the placeholder SQL. This will be awkward, because you can't ask PreparedStatement for the parameters that have been set, so you'll have to remember them in a helper object, before passing them to the statement.
我认为您可能只需要硬着头皮编写自己的类，将参数插入到占位符 SQL 中。这会很尴尬，因为您不能向 PreparedStatement 询问已设置的参数，因此在将它们传递给语句之前，您必须在帮助对象中记住它们。
It seems to me that one of the utility libraries which wrap your driver's implementation objects is the most practical way of doing what you're trying to achieve, but it's going to be unpleasant either way.
在我看来，包装驱动程序实现对象的实用程序库之一是完成您要实现的目标的最实用方法，但无论哪种方式都会令人不快。
回答by Kieran Tully
If you are using MySQL, MySQL Connector's PreparedStatement.toString() does include the bound parameters. Though third-party connection pools may break this.
Sub-class PreparedStatement to build up the query string as parameters are added. There's no way to extract the SQL from a PreparedStatement, as it uses a compiled binary form.
如果您使用 MySQL，MySQL 连接器的 PreparedStatement.toString()确实包含绑定参数。尽管第三方连接池可能会破坏这一点。
子类 PreparedStatement 在添加参数时构建查询字符串。无法从 PreparedStatement 中提取 SQL，因为它使用编译后的二进制形式。
LoggedPreparedStatementlooks promising, though I haven't tried it.
LoggedPreparedStatement看起来很有希望，但我还没有尝试过。
One advantage of these over a proxy driver that logs all queries is that you can modify the query string before logging it. For example in a PCI environment you might want to mask card numbers.
与记录所有查询的代理驱动程序相比，它们的一个优点是您可以在记录之前修改查询字符串。例如，在 PCI 环境中，您可能希望屏蔽卡号。
