如何在使用Hibernate时打印带有参数值的查询字符串

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，您建议如何打印带有真实值的查询?

27 个解决方案

#1

You need to enable logging for the the following categories:

您需要为以下类别启用日志记录:

• org.hibernate.SQL   - set to debug to log all SQL DML statements as they are executed
• org.hibernate。SQL -设置为debug，以便在执行所有SQL DML语句时记录它们
• org.hibernate.type - set to trace to log all JDBC parameters
• org.hibernate。键入-设置跟踪以记录所有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=true legacy property, the second prints the bound parameters among other things.

第一个相当于hibernate。show_sql=true legacy属性，第二个属性打印绑定参数。

Another solution (non hibernate based) would be to use a JDBC proxy driver like P6Spy.

另一种解决方案(非基于hibernate的)是使用像P6Spy这样的JDBC代理驱动程序。

#2

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中的输出。日志(示例)如下:

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] - [e-mail]
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

#3

Change hibernate.cfg.xml to:

改变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和以下的“log4j.properties”条目:

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

#4

Log4JDBC is a nice solution which prints the exact SQL 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直接复制到您的DB前端并按原样执行。

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

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:

驱动程序和DB url看起来是:

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 #MySQL

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语句以及所有查询的resultset表。

<?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的文件。类路径根的属性，例如src/test/resources或Mevn项目中的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-log4j2 for 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 |
|----------|---|---|----------|--------|---|-----|
|----------|---|---|----------|--------|---|-----|

#5

You can add category lines to log4j.xml:

您可以向log4j.xml添加类别行:

<category name="org.hibernate.type">
    <priority value="TRACE"/>
</category>

and add hibernate properties:

hibernate和添加属性:

<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="use_sql_comments">true</property>

#6

add following properties and values to your log4j or logback configuration:

向log4j或logback配置添加以下属性和值:

org.hibernate.sql=DEBUG
org.hibernate.type.descriptor.sql.BasicBinder=TRACE

#7

In case of spring boot is being used , just config this :

如果正在使用spring boot，只需配置如下:

aplication.yml

aplication.yml

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type: TRACE

aplication.properties

aplication.properties

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE

and nothing more.

而已。

HTH

HTH

#8

turn on the org.hibernate.type Logger to see how the actual parameters are bind to the question marks.

打开org.hibernate。键入Logger，查看如何将实际参数绑定到问号上。

#9

You can do it using the datasource-proxy, as I described in this post.

正如我在本文中描述的那样，您可以使用数据源代理来完成它。

Assuming your application expects a dataSource bean (e.g. via @Resource), this is how you can configure datasource-proxy:

假设您的应用程序需要一个数据源bean(例如，通过@Resource)，这就是如何配置dataSource -proxy:

<bean id="actualDataSource" class="bitronix.tm.resource.jdbc.PoolingDataSource" init-method="init"
  destroy-method="close">
    <property name="className" value="bitronix.tm.resource.jdbc.lrc.LrcXADataSource"/>
    <property name="uniqueName" value="actualDataSource"/>
    <property name="minPoolSize" value="0"/>
    <property name="maxPoolSize" value="5"/>
    <property name="allowLocalTransactions" value="false" />
    <property name="driverProperties">
        <props>
            <prop key="user">${jdbc.username}</prop>
            <prop key="password">${jdbc.password}</prop>
            <prop key="url">${jdbc.url}</prop>
            <prop key="driverClassName">${jdbc.driverClassName}</prop>
        </props>
    </property>
</bean>

<bean id="proxyDataSource" class="net.ttddyy.dsproxy.support.ProxyDataSource">
    <property name="dataSource" ref="testDataSource"/>
    <property name="listener">
        <bean class="net.ttddyy.dsproxy.listener.ChainListener">
            <property name="listeners">
                <list>
                    <bean class="net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener">
                        <property name="logLevel" value="INFO"/>
                    </bean>
                    <bean class="net.ttddyy.dsproxy.listener.DataSourceQueryCountListener"/>
                </list>
            </property>
        </bean>
    </property>
</bean>

<alias name="proxyDataSource" alias="dataSource"/>

Now the Hibernate output vs datasource-proxy:

现在Hibernate输出vs数据源代理:

INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:1, Num:1, Query:{[select company0_.id as id1_6_, company0_.name as name2_6_ from Company company0_][]}
INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:0, Num:1, Query:{[insert into WarehouseProductInfo (id, quantity) values (default, ?)][19]}
INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:0, Num:1, Query:{[insert into Product (id, code, company_id, importer_id, name, version) values (default, ?, ?, ?, ?, ?)][phoneCode,1,-5,Phone,0]}

The datasource-proxy queries contain parameter values and you can even add custom JDBC statement interceptors so that you can catch N+1 query issues right from your integration tests.

数据源代理查询包含参数值，您甚至可以添加自定义JDBC语句拦截器，以便您可以从集成测试中捕获N+1查询问题。

#10

**If you want hibernate to print generated sql queries with real values instead of question marks.**
**add following entry in hibernate.cfg.xml/hibernate.properties:**
show_sql=true
format_sql=true
use_sql_comments=true

**And add following entry in log4j.properties :**
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

#11

<!-- A time/date based rolling appender -->
<appender name="FILE" class="org.apache.log4j.RollingFileAppender">
    <param name="File" value="logs/system.log" />
    <param name="Append" value="true" />
    <param name="ImmediateFlush" value="true" />
    <param name="MaxFileSize" value="200MB" />
    <param name="MaxBackupIndex" value="100" />

    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="%d %d{Z} [%t] %-5p (%F:%L) - %m%n" />
    </layout>
</appender>

<appender name="journaldev-hibernate" class="org.apache.log4j.RollingFileAppender">
    <param name="File" value="logs/project.log" />
    <param name="Append" value="true" />
    <param name="ImmediateFlush" value="true" />
    <param name="MaxFileSize" value="200MB" />
    <param name="MaxBackupIndex" value="50" />

    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="%d %d{Z} [%t] %-5p (%F:%L) - %m%n" />
    </layout>
</appender>

<logger name="com.journaldev.hibernate" additivity="false">
    <level value="DEBUG" />
    <appender-ref ref="journaldev-hibernate" />
</logger>

<logger name="org.hibernate" additivity="false">
    <level value="INFO" />
    <appender-ref ref="FILE" />
</logger>

<logger name="org.hibernate.type" additivity="false">
    <level value="TRACE" />
    <appender-ref ref="FILE" />
</logger>

<root>
    <priority value="INFO"></priority>
    <appender-ref ref="FILE" />
</root>

#12

This answer is a little variance for the question. Sometimes, we only need the sql only for debug purposes in runtime. In that case, there are a more easy way, using debug on editors.

这个答案与这个问题有点不同。有时，我们只需要sql在运行时用于调试目的。在这种情况下，有一种更简单的方法，在编辑器上使用debug。

• Put a breakpoint on org.hibernate.loader.Loader.loadEntityBatch (or navigate on the stack until there);
• 在org.hibernate.loader.Loader. loader上放置一个断点。loadEntityBatch(或导航到堆栈);
• When execution is suspended, look the value of variable this.sql ;
• 当执行被挂起时，查看变量this的值。sql;

This is for hibernate 3. I'm not sure that this work on other versions.

这是针对hibernate 3的。我不确定这是否适用于其他版本。

#13

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>

#14

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 

#15

<appender name="console" class="org.apache.log4j.ConsoleAppender">
    <layout class="org.apache.log4j.PatternLayout">
    <param name="ConversionPattern" 
      value="%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n" />
    </layout>
</appender>

<logger name="org.hibernate" additivity="false">
    <level value="INFO" />
    <appender-ref ref="console" />
</logger>

<logger name="org.hibernate.type" additivity="false">
    <level value="TRACE" />
    <appender-ref ref="console" />
</logger>

#16

Using Hibernate 4 and slf4j/log4j2 , I tried adding the following in my log4j2.xml configuration :

使用Hibernate 4和slf4j/log4j2，我尝试在log4j2中添加以下内容。xml配置:

<Logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="trace" additivity="false"> 
    <AppenderRef ref="Console"/> 
</Logger> 
<Logger name="org.hibernate.type.EnumType" level="trace" additivity="false"> 
    <AppenderRef ref="Console"/>
</Logger>

But without success.

但没有成功。

I found out through this thread that the jboss-logging framework used by hibernate needed to be configured in order to log through slf4j. I added the following argument to the VM arguments of the application:

通过这个线程，我发现hibernate使用的jboss-logging框架需要进行配置，以便通过slf4j进行日志记录。我在应用程序的VM参数中添加了以下参数:

-Dorg.jboss.logging.provider=slf4j

And it worked like a charm.

它就像一种魔力。

#17

if you are using hibernate 3.2.xx use

如果您正在使用hibernate 3.2。xx使用

log4j.logger.org.hibernate.SQL=trace

instead of

而不是

log4j.logger.org.hibernate.SQL=debug 

#18

You can log this:

你可以登录:

net.sf.hibernate.hql.QueryTranslator

Output example:

输出的例子:

2013-10-31 14:56:19,029 DEBUG [net.sf.hibernate.hql.QueryTranslator] HQL: select noti.id, noti.idmicrosite, noti.fcaducidad, noti.fpublicacion, noti.tipo, noti.imagen, noti.visible, trad.titulo, trad.subtitulo, trad.laurl, trad.urlnom, trad.fuente, trad.texto  from org.ibit.rol.sac.micromodel.Noticia noti join noti.traducciones trad where index(trad)='ca' and noti.visible='S' and noti.idmicrosite=985 and noti.tipo=3446

2013-10-31 14:56:19,029 DEBUG [net.sf.hibernate.hql.QueryTranslator] SQL: select noticia0_.NOT_CODI as x0_0_, noticia0_.NOT_MICCOD as x1_0_, noticia0_.NOT_CADUCA as x2_0_, noticia0_.NOT_PUBLIC as x3_0_, noticia0_.NOT_TIPO as x4_0_, noticia0_.NOT_IMAGEN as x5_0_, noticia0_.NOT_VISIB as x6_0_, traduccion1_.NID_TITULO as x7_0_, traduccion1_.NID_SUBTIT as x8_0_, traduccion1_.NID_URL as x9_0_, traduccion1_.NID_URLNOM as x10_0_, traduccion1_.NID_FUENTE as x11_0_, traduccion1_.NID_TEXTO as x12_0_ from GUS_NOTICS noticia0_ inner join GUS_NOTIDI traduccion1_ on noticia0_.NOT_CODI=traduccion1_.NID_NOTCOD where (traduccion1_.NID_CODIDI='ca' )and(noticia0_.NOT_VISIB='S' )and(noticia0_.NOT_MICCOD=985 )and(noticia0_.NOT_TIPO=3446 )

#19

Log4Jdbc plugin would be best for your requirement. It shows following-

Log4Jdbc插件最适合您的需求。它显示后,

1. Complete SQL query being hit to the db
2. Parameter values being passed to the query
3. Execution time taken by each query

Refer below link to configure Log4Jdbc-

请参考下面的链接来配置Log4Jdbc-

https://code.google.com/p/log4jdbc/

#20

Logging works but not exactly you want or i wanted some time ago, but P6Spy does work perfectly,

日志可以工作，但不是你想要的或者我之前想要的，但是P6Spy工作得很好，

here is the simple tutorial to implement as well MKYONG tutorial for P6Spy.

这里有一个简单的教程来实现P6Spy的MKYONG教程。

for me it worked like charm.

对我来说，它很有魅力。

1. Download P6Spy library
2. 下载P6Spy库

Get the “p6spy-install.jar“

获得“p6spy-install.jar”

2. Extract it
3. 提取它

Extract the p6spy-install.jar file, look for p6spy.jar and spy.properties

提取p6spy-install。jar文件，找p6spy。jar和spy.properties

3. Add library dependency
4. 添加库依赖关系

Add p6spy.jar into your project library dependency

添加p6spy。将jar放到项目库依赖项中

4. Modify P6Spy properties file
5. 修改P6Spy属性文件

Modify your database configuration file. You need to replace your existing JDBC driver with P6Spy JDBC driver – com.p6spy.engine.spy.P6SpyDriver

修改数据库配置文件。您需要将现有的JDBC驱动程序替换为P6Spy JDBC驱动程序- com.p6sp . engineering .sp . p6spydriver

Original is MySQL JDBC driver – com.mysql.jdbc.Driver

原来是MySQL驱动程序- com. sql. JDBC . driver

<session-factory>
  <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mkyong</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="show_sql">true</property>
</session-factory>

Changed it to P6Spy JDBC driver – com.p6spy.engine.spy.P6SpyDriver

将它更改为P6Spy JDBC驱动程序- com. P6Spy . engineering .sp . p6spydriver

<session-factory>
  <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
  <property name="hibernate.connection.driver_class">com.p6spy.engine.spy.P6SpyDriver
  </property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mkyong</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="show_sql">true</property>
</session-factory>

5. Modify P6Spy properties file Modify the P6Spy properties file – spy.properties
6. 修改P6Spy属性文件修改P6Spy属性文件sp .properties文件

Replace the real driver with your existing MySQL JDBC driver

用现有的MySQL JDBC驱动程序替换真正的驱动程序

realdriver=com.mysql.jdbc.Driver

#specifies another driver to use
realdriver2=
#specifies a third driver to use
realdriver3=

Change the Log file location Change the log file location in logfile property, all SQL statements will log into this file.

更改日志文件位置更改日志文件属性中的日志文件位置，所有SQL语句都将登录到此文件。

Windows

窗户

logfile     = c:/spy.log

*nix

* nix

logfile     = /srv/log/spy.log

6. Copy “spy.properties” to project classpath
7. 复制“间谍。属性”项目的类路径中

Copy “spy.properties” to your project root folder, make sure your project can locate “spy.properties”, else it will prompt “spy.properties” file not found exception.

复制“间谍。属性“到您的项目根文件夹，确保您的项目可以找到“spy”。属性，否则它会提示“spy”。属性“文件未找到异常”。

#21

Use Wireshark or something similar:

使用线鲨或类似的东西:

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/命令。

#22

All of the answers here are helpful, but if you're using a Spring application context XML to setup your session factory, setting the log4j SQL level variable only gets you part of the way there, you also have to set the hibernate.show_sql variable in the app context itself to get Hibernate to start actually showing the values.

这里的所有答案都很有帮助，但是如果您使用Spring应用程序上下文XML来设置会话工厂，那么设置log4j SQL level变量只会让您了解其中的一部分，您还必须设置hibernate。在app上下文中显示show_sql变量，让Hibernate开始实际显示值。

ApplicationContext.xml has:

ApplicationContext。xml有:

<property name="hibernateProperties">
            <value>
            hibernate.jdbc.batch_size=25
            ... <!-- Other parameter values here -->
            hibernate.show_sql=true
            </value>
 </property>

And your log4j file needs

您的log4j文件需要

log4j.logger.org.hibernate.SQL=DEBUG

#23

mysql jdbc driver has already provide a convenient to meet this requirement, you must at least the have the jar version >= mysql-connect-jar-5.1.6.jar

mysql jdbc驱动程序已经为满足这一需求提供了方便，您至少必须有jar版本的>= mysql-connect-jar-5.1.6.jar。

step 1: [configure your jdbc.url to add logger and custom logging]

步骤1:[配置jdbc。添加日志记录器和自定义日志记录的url]

    jdbc.url=jdbc:mysql://host:port/your_db?logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true&profilerEventHandler=com.xxx.CustomLoggingProfilerEventHandler

now, it is using slf4j logging, if your default logging is log4j, you must add slf4j-api, slf4j-log4j12 dependencies to use slf4j logging

现在，它正在使用slf4j日志记录，如果您的默认日志记录是log4j，那么您必须添加slf4j-api、slf4j-log4j12依赖项来使用slf4j日志记录

step 2: [write your custom logging]

步骤2:[编写自定义日志]

package com.xxx;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.log.Log;

public class CustomLoggingProfilerEventHandler implements ProfilerEventHandler {
    private Log log;

    public LoggingProfilerEventHandler() {
    }

    public void consumeEvent(ProfilerEvent evt) {
            /**
             * you can only print the sql as        this.log.logInfo(evt.getMessage())
             * you can adjust your sql print log level with: DEBUG,INFO
             * you can also handle the message to meet your requirement
             */ 
            this.log.logInfo(evt);
    }

    public void destroy() {
        this.log = null;
    }

    public void init(Connection conn, Properties props) throws SQLException {
        this.log = conn.getLog();
    }

}

#24

Here is what worked for me, set below property in the log4j.file:

下面是我的工作，在log4j.file中设置如下属性:

log4j.logger.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Hibernate properties settings :

Hibernate属性设置:

hibernate.show_sql=true

#25

In Java:

在Java中:

Transform your query in TypedQuery if it's a CriteriaQuery (javax.persistence).

如果查询是CriteriaQuery (java .persistence)，则在TypedQuery中转换查询。

Then:

然后:

query.unwrap(org.hibernate.Query.class).getQueryString();

query.unwrap(org.hibernate.Query.class).getQueryString();

#26

Hibernate shows query and their parameter values in different lines.

Hibernate在不同的行中显示查询及其参数值。

If you are using application.properties in spring boot and you can use below highlighted parameter in application.properties.

如果您正在使用应用程序。在spring boot中的属性，可以在application.properties中使用下面突出显示的参数。

1. org.hibernate.SQL will show queries

   org.hibernate。SQL将显示查询

   logging.level.org.hibernate.SQL=DEBUG

2. org.hibernate.type will show all parameter values, which will map with select , insert and update queries. logging.level.org.hibernate.type=TRACE

   org.hibernate。type将显示所有参数值，这些值将与select、insert和update查询进行映射。logging.level.org.hibernate.type =跟踪

   • org.hibernate.type.EnumType will show enum type parameter value

     org.hibernate.type。枚举类型将显示enum类型参数值

     logging.level.org.hibernate.type.EnumType=TRACE

     example ::

     例如::

     2018-06-14 11:06:28,217 TRACE [main] [EnumType.java : 321] Binding [active] to parameter: [1]
     

   • sql.BasicBinder will show integer, varchar, boolean type parameter value

     sql。BasicBinder将显示整型、varchar、布尔型参数值

     logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

     example ::

     例如::

     • 2018-06-14 11:28:29,750 TRACE [http-nio-9891-exec-2] [BasicBinder.java : 65] binding parameter [1] as [BOOLEAN] - [true]
     • 2018-06-14 11:28:29 750 TRACE [http-nio-9891- execl -2] [BasicBinder]将参数[1]绑定为[布尔]-[真]
     • 2018-06-14 11:28:29,751 TRACE [http-nio-9891-exec-2] [BasicBinder.java : 65] binding parameter [2] as [INTEGER] - [1]
     • 2018-06-14 11:28 29,751追踪[http-nio-9891-exec-2] [BasicBinder]。将参数[2]绑定为[INTEGER] - [1]
     • 2018-06-14 11:28:29,752 TRACE [http-nio-9891-exec-2] [BasicBinder.java : 65] binding parameter [3] as [VARCHAR] - [public]
     • 2018-06-14 11:28:29,752追踪[http-nio-9891-exec-2] [BasicBinder]java: 65]绑定参数[3]为[VARCHAR] - [public]

#27

The simplest solution for me is implementing a regular stringReplace to replace parameter inputs with parameter values (treating all parameters as string, for simplicity):

对于我来说，最简单的解决方案是实现一个常规的stringReplace，用参数值替换参数输入(为了简单起见，将所有参数都当作字符串处理):

 String debugedSql = sql;
 //then, for each named parameter
     debugedSql = debugedSql.replaceAll(":"+key, "'"+value.toString()+"'");
 //and finnaly
 println(debugedSql);

or something similar for positional parameters (?).
Take care of null values and specific value types like date, if you want a run ready sql to be logged.

或者类似于位置参数(?)如果希望记录已运行的sql，请注意空值和特定的值类型(如date)。