Java 使用 Hibernate 时如何打印带有参数值的查询字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1710476/
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
How to print a query string with parameter values when using Hibernate
提问by craftsman
Is it possible in Hibernate to print generated SQL queries with real values instead of question marks?
是否可以在 Hibernate 中使用实际值而不是问号打印生成的 SQL 查询？
How would you suggest to print queries with real values if it is not possible with Hibernate API?
如果 Hibernate API 无法实现，您将如何建议打印具有真实值的查询？
采纳答案by Pascal Thivent
You need to enable loggingfor the the following categories:
您需要为以下类别启用日志记录：
org.hibernate.SQL- set todebugto log all SQL DML statements as they are executedorg.hibernate.type- set totraceto log all JDBC parameters
org.hibernate.SQL- 设置为debug在执行时记录所有 SQL DML 语句org.hibernate.type- 设置为trace记录所有 JDBC 参数
So a log4j configuration could look like:
因此，log4j 配置可能如下所示：
# logs the SQL statements
log4j.logger.org.hibernate.SQL=debug
# Logs the JDBC parameters passed to a query
log4j.logger.org.hibernate.type=trace
The first is equivalent to hibernate.show_sql=truelegacy property, the second prints the bound parameters among other things.
第一个等效于hibernate.show_sql=truelegacy property，第二个打印绑定参数等。
Another solution (non hibernate based) would be to use a JDBC proxy driver like P6Spy.
另一种解决方案（非基于休眠的）是使用像P6Spy这样的 JDBC 代理驱动程序。
回答by flybywire
turn on the org.hibernate.typeLogger to see how the actual parameters are bind to the question marks.
打开org.hibernate.typeLogger 以查看实际参数如何绑定到问号。
回答by diy
if you are using hibernate 3.2.xx use
如果您使用的是休眠 3.2.xx，请使用
log4j.logger.org.hibernate.SQL=trace
instead of
代替
log4j.logger.org.hibernate.SQL=debug
回答by zime
The solution is correct but logs also all bindings for the result objects. To prevent this it's possibile to create a separate appender and enable filtering, for example:
解决方案是正确的，但也会记录结果对象的所有绑定。为了防止这种情况，可以创建一个单独的 appender 并启用过滤，例如：
<!-- A time/date based rolling appender -->
<appender name="FILE_HIBERNATE" class="org.jboss.logging.appender.DailyRollingFileAppender">
<errorHandler class="org.jboss.logging.util.OnlyOnceErrorHandler"/>
<param name="File" value="${jboss.server.log.dir}/hiber.log"/>
<param name="Append" value="false"/>
<param name="Threshold" value="TRACE"/>
<!-- Rollover at midnight each day -->
<param name="DatePattern" value="'.'yyyy-MM-dd"/>
<layout class="org.apache.log4j.PatternLayout">
<!-- The default pattern: Date Priority [Category] Message\n -->
<param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
</layout>
<filter class="org.apache.log4j.varia.StringMatchFilter">
<param name="StringToMatch" value="bind" />
<param name="AcceptOnMatch" value="true" />
</filter>
<filter class="org.apache.log4j.varia.StringMatchFilter">
<param name="StringToMatch" value="select" />
<param name="AcceptOnMatch" value="true" />
</filter>
<filter class="org.apache.log4j.varia.DenyAllFilter"/>
</appender>
<category name="org.hibernate.type">
<priority value="TRACE"/>
</category>
<logger name="org.hibernate.type">
<level value="TRACE"/>
<appender-ref ref="FILE_HIBERNATE"/>
</logger>
<logger name="org.hibernate.SQL">
<level value="TRACE"/>
<appender-ref ref="FILE_HIBERNATE"/>
</logger>
回答by Hari
Change hibernate.cfg.xmlto:
更改hibernate.cfg.xml为：
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="use_sql_comments">true</property>
Include log4j and below entries in "log4j.properties":
在“log4j.properties”中包含 log4j 和以下条目：
log4j.logger.org.hibernate=INFO, hb
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
log4j.appender.hb=org.apache.log4j.ConsoleAppender
log4j.appender.hb.layout=org.apache.log4j.PatternLayout
回答by Michal Z m u d a
You can add category lines to log4j.xml:
您可以向 log4j.xml 添加类别行：
<category name="org.hibernate.type">
<priority value="TRACE"/>
</category>
and add hibernate properties:
并添加休眠属性：
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="use_sql_comments">true</property>
回答by Christoph
Just for convenience, here is the same configuration example for Logback (SLF4J)
为方便起见，这里是 Logback (SLF4J) 的相同配置示例
<appender name="SQLROLLINGFILE">
<File>/tmp/sql.log</File>
<rollingPolicy>
<FileNamePattern>logFile.%d{yyyy-MM-dd}.log</FileNamePattern>
</rollingPolicy>
<layout>
<Pattern>%-4date | %msg %n</Pattern>
</layout>
</appender>
<logger name="org.hibernate.SQL" additivity="false" >
<level value="DEBUG" />
<appender-ref ref="SQLROLLINGFILE" />
</logger>
<logger name="org.hibernate.type" additivity="false" >
<level value="TRACE" />
<appender-ref ref="SQLROLLINGFILE" />
</logger>
The output in your sql.log (example) then looks like this:
sql.log（示例）中的输出如下所示：
2013-08-30 18:01:15,083 | update stepprovider set created_at=?, lastupdated_at=?, version=?, bundlelocation=?, category_id=?, customer_id=?, description=?, icon_file_id=?, name=?, shareStatus=?, spversion=?, status=?, title=?, type=?, num_used=? where id=?
2013-08-30 18:01:15,084 | binding parameter [1] as [TIMESTAMP] - 2012-07-11 09:57:32.0
2013-08-30 18:01:15,085 | binding parameter [2] as [TIMESTAMP] - Fri Aug 30 18:01:15 CEST 2013
2013-08-30 18:01:15,086 | binding parameter [3] as [INTEGER] -
2013-08-30 18:01:15,086 | binding parameter [4] as [VARCHAR] - com.mypackage.foo
2013-08-30 18:01:15,087 | binding parameter [5] as [VARCHAR] -
2013-08-30 18:01:15,087 | binding parameter [6] as [VARCHAR] -
2013-08-30 18:01:15,087 | binding parameter [7] as [VARCHAR] - TODO
2013-08-30 18:01:15,087 | binding parameter [8] as [VARCHAR] -
2013-08-30 18:01:15,088 | binding parameter [9] as [VARCHAR] - [email protected]
2013-08-30 18:01:15,088 | binding parameter [10] as [VARCHAR] - PRIVATE
2013-08-30 18:01:15,088 | binding parameter [11] as [VARCHAR] - 1.0
2013-08-30 18:01:15,088 | binding parameter [12] as [VARCHAR] - 32
2013-08-30 18:01:15,088 | binding parameter [13] as [VARCHAR] - MatchingStep
2013-08-30 18:01:15,089 | binding parameter [14] as [VARCHAR] -
2013-08-30 18:01:15,089 | binding parameter [15] as [INTEGER] - 0
2013-08-30 18:01:15,089 | binding parameter [16] as [VARCHAR] - 053c2e65-5d51-4c09-85f3-2281a1024f64
回答by Aneesh Vijendran
Use Wiresharkor something similar:
使用Wireshark或类似的东西：
None of the above mentioned answers will print sql with parameters properly or is a pain. I achieved this by using WireShark, which captures all sql/commands being send from the application to Oracle/Mysql etc with the queries.
上面提到的答案都不会正确地打印带参数的 sql 或者是痛苦的。我通过使用WireShark实现了这一点，它通过查询捕获从应用程序发送到 Oracle/Mysql 等的所有 sql/命令。
回答by Alan Hay
Log4JDBC is a nice solution which prints the exactSQL going to the database with parameters in place rather than the most popular answer here which does not do this. One major convenience of this is that you can copy the SQL straight to your DB front-end and execute as is.
Log4JDBC 是一个很好的解决方案，它打印准确的SQL 到数据库的参数到位，而不是这里最流行的答案，它不这样做。这样做的一个主要便利是您可以将 SQL 直接复制到您的数据库前端并按原样执行。
http://log4jdbc.sourceforge.net/
http://log4jdbc.sourceforge.net/
https://code.google.com/p/log4jdbc-remix/
https://code.google.com/p/log4jdbc-remix/
The latter also outputs a tabular representation of query results.
后者还输出查询结果的表格表示。
Sample Output showing generated SQL with params in place together with result set table from query:
示例输出显示生成的带有参数的 SQL 以及来自查询的结果集表：
5. insert into ENQUIRY_APPLICANT_DETAILS (ID, INCLUDED_IN_QUOTE, APPLICANT_ID, TERRITORY_ID, ENQUIRY_ID, ELIGIBLE_FOR_COVER) values (7, 1, 11, 1, 2, 0)
10 Oct 2013 16:21:22 4953 [main] INFO jdbc.resultsettable - |---|--------|--------|-----------|----------|---------|-------|
10 Oct 2013 16:21:22 4953 [main] INFO jdbc.resultsettable - |ID |CREATED |DELETED |CODESET_ID |NAME |POSITION |PREFIX |
10 Oct 2013 16:21:22 4953 [main] INFO jdbc.resultsettable - |---|--------|--------|-----------|----------|---------|-------|
10 Oct 2013 16:21:22 4953 [main] INFO jdbc.resultsettable - |2 |null |null |1 |Country 2 |1 |60 |
10 Oct 2013 16:21:22 4953 [main] INFO jdbc.resultsettable - |---|--------|--------|-----------|----------|---------|-------|
Update 2016
2016 年更新
Most recently I have now been using log4jdbc-log4j2 (https://code.google.com/archive/p/log4jdbc-log4j2/) with SLF4j and logback. Maven dependencies required for my set-up are as below:
最近我一直在使用带有 SLF4j 和 logback 的log4jdbc-log4j2 ( https://code.google.com/archive/p/log4jdbc-log4j2/)。我的设置所需的 Maven 依赖项如下：
<dependency>
<groupId>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
<version>1.16</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>${logback.version}</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>$logback.version}</version>
</dependency>
The Driver and DB Urls then look like:
Driver 和 DB Urls 如下所示：
database.driver.class=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
database.url=jdbc:log4jdbc:hsqldb:mem:db_name #Hsql
#database.url=jdbc:log4jdbc:mysql://localhost:3306/db_name
My logback.xml configuration file looks like the below: this outputs all SQL statements with parameters plus the resultset tables for all queries.
我的 logback.xml 配置文件如下所示：它输出所有带有参数的 SQL 语句以及所有查询的结果集表。
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n
</pattern>
</encoder>
</appender>
<logger name="jdbc.audit" level="ERROR" />
<logger name="jdbc.connection" level="ERROR" />
<logger name="jdbc.sqltiming" level="ERROR" />
<logger name="jdbc.resultset" level="ERROR" />
<!-- UNCOMMENT THE BELOW TO HIDE THE RESULT SET TABLE OUTPUT -->
<!--<logger name="jdbc.resultsettable" level="ERROR" /> -->
<root level="debug">
<appender-ref ref="STDOUT" />
</root>
</configuration>
Finally, I had to create a file named log4jdbc.log4j2.properties at the root of the classpath e.g. src/test/resources or src/main/resources in a Mevn project. This file has one line which is the below:
最后，我必须在类路径的根目录创建一个名为 log4jdbc.log4j2.properties 的文件，例如在 Mevn 项目中的 src/test/resources 或 src/main/resources。该文件有一行，如下所示：
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
The above will depend on your logging library. See the docs at https://code.google.com/archive/p/log4jdbc-log4j2for further info
以上将取决于您的日志记录库。有关更多信息，请参阅https://code.google.com/archive/p/log4jdbc-log4j2 上的文档
Sample Output:
示例输出：
10:44:29.400 [main] DEBUG jdbc.sqlonly - org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
5. select memberrole0_.member_id as member_i2_12_0_, memberrole0_.id as id1_12_0_, memberrole0_.id
as id1_12_1_, memberrole0_.member_id as member_i2_12_1_, memberrole0_.role_id as role_id3_12_1_,
role1_.id as id1_17_2_, role1_.name as name2_17_2_ from member_roles memberrole0_ left outer
join roles role1_ on memberrole0_.role_id=role1_.id where memberrole0_.member_id=104
10:44:29.402 [main] INFO jdbc.resultsettable -
|----------|---|---|----------|--------|---|-----|
|member_id |id |id |member_id |role_id |id |name |
|----------|---|---|----------|--------|---|-----|
|----------|---|---|----------|--------|---|-----|
回答by Frizz1977
I like this for log4j:
我喜欢这样的 log4j：
log4j.logger.org.hibernate.SQL=trace
log4j.logger.org.hibernate.engine.query=trace
log4j.logger.org.hibernate.type=trace
log4j.logger.org.hibernate.jdbc=trace
log4j.logger.org.hibernate.type.descriptor.sql.BasicExtractor=error
log4j.logger.org.hibernate.type.CollectionType=error
