在 Java 中记录PreparedStatements
当您有一个PreparedStatement而不是查询本身时，一直很痛苦的一件事是记录SQL（JDBC）错误。
你总是会得到这样的消息：
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 = ?]
当然，我可以编写一个辅助方法来检索值并用实际值解析/替换问号（如果我没有得到这个问题的结果，可能会走这条路） ，但我只是想知道这个问题是否已被其他人解决过，和/或是否有任何通用日志记录助手可以自动为我解决这个问题。
在几个答案后编辑：
到目前为止提供的库似乎适合记录调试语句，这无疑是有用的。 然而，我正在寻找一种方法来获取PreparedStatement本身（而不是某些子类）并在发生错误时记录其SQL语句。 我不想使用PreparedStatement的替代实现来部署生产应用程序。
我想我正在寻找一个实用程序类，而不是一个PreparedStatement 专业化。
谢谢！
One thing that always been a pain is to log SQL (JDBC) errors when you have a PreparedStatement instead of the query itself.
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.
I guess what I am looking for an utility class, not a PreparedStatement specialization.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我尝试了 log4jdbc 它为我完成了这项工作。
安全注意事项：截至 2011 年 8 月今天，log4jdbc 准备好的语句的记录结果执行起来并不安全。 它们可用于分析，但绝不应反馈到 DBMS 中。
logjdbc 生成的日志示例：
该库非常容易设置:
我使用 HSQLDB 的配置 :
使用 Oracle 的配置 :
logback.xml :
太糟糕了，它不在 Maven 存储库上，但仍然有用。
根据我的尝试，如果你设置
You 只会得到错误的语句，但是，我不知道这个库是否对性能有影响。
I tried log4jdbc and it did the job for me.
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.
Example of log generated by logjdbc:
The library is very easy to setup:
My configuration with HSQLDB :
With Oracle :
logback.xml :
Too bad it wasn't on a maven repository, but still useful.
From what I tried, if you set
You will only get the statements in error, however, I don't know if this library has an impact on performance.
这非常依赖于数据库。 例如，据我了解，某些 JDBC 驱动程序（例如 sybase，可能是 ms-sql）通过在服务器上创建临时存储过程，然后使用提供的参数调用该过程来处理准备好的语句。 因此完整的 SQL 永远不会真正从客户端传递。
因此，JDBC API 不会公开您想要的信息。 您也许能够将语句对象强制转换为内部驱动程序实现，但可能不能 - 您的应用程序服务器很可能将语句包装在其自己的实现中。
我认为您可能只需要硬着头皮编写自己的类，将参数插入占位符 SQL 中。 这会很尴尬，因为您无法向PreparedStatement询问已设置的参数，因此在将它们传递给语句之前，您必须在辅助对象中记住它们。
在我看来，包装驱动程序实现对象的实用程序库之一是实现您想要实现的目标的最实用方法，但无论哪种方式都会令人不快。
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.
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.
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.
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.
使用 P6Spy：它的 Oracle、Mysql、JNDI、JMX、Spring 和 < Maven 友好。 高度可配置。
简单且低级别的集成
可以打印堆栈跟踪。
只能打印大量呼叫 - 基于时间阈值。
Use P6Spy: Its Oracle, Mysql, JNDI, JMX, Spring and Maven friendly. Highly configurable.
Simple and low level integration
Can print the stacktrace.
Can only print heavy calls - time threashold based.
如果您使用 MySQL，MySQL Connector 的PreparedStatement.toString() 确实包含绑定参数。 尽管第三方连接池可能会破坏这一点。
子类PreparedStatement，用于在添加参数时构建查询字符串。 无法从PreparedStatement 中提取SQL，因为它使用编译的二进制形式。
LoggedPreparedStatement 看起来很有希望，尽管我还没有尝试过。
与记录所有查询的代理驱动程序相比，这些方法的优点之一是您可以在记录查询字符串之前对其进行修改。 例如，在 PCI 环境中，您可能想要屏蔽卡号。
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.
LoggedPreparedStatement looks promising, though I haven't tried it.
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.