在 Spring JdbcTemplate 中看到底层 SQL 了吗？

Seeing the underlying SQL in the Spring JdbcTemplate?

提问by Artem

I am learning about the wonders of JdbcTemplate and NamedParameterJdbcTemplate. I like what I see, but is there any easy way to see the underlying SQL that it ends up executing? I'd like to see this for debug purposes (in order to for example debug the resulting SQL in an outside tool).

我正在了解 JdbcTemplate 和 NamedParameterJdbcTemplate 的神奇之处。我喜欢我所看到的，但是有什么简单的方法可以查看它最终执行的底层 SQL 吗？我想看到这个用于调试目的（例如为了在外部工具中调试生成的 SQL）。

采纳答案by BalusC

The Spring documentationsays they're logged at DEBUG level:

在Spring文档说，他们在DEBUG级别记录：

All SQL issued by this class is logged at the DEBUGlevel under the categorycorresponding to the fully qualified class nameof the template instance (typically JdbcTemplate, but it may be different if you are using a custom subclass of the JdbcTemplate class).

此类发出的所有 SQL 都在DEBUG级别记录在与模板实例的完全限定类名对应的类别下（通常为 JdbcTemplate，但如果您使用的是 JdbcTemplate 类的自定义子类，则可能会有所不同）。

In XML terms, you need to configure the logger something like:

在 XML 术语中，您需要像这样配置记录器：

<category name="org.springframework.jdbc.core.JdbcTemplate">
    <priority value="debug" />
</category>

This subject was however discussed here a month ago and it seems not as easy to get to work as in Hibernate and/or it didn't return the expected information: Spring JDBC is not logging SQL with log4jThis topic under each suggests to use P6Spywhich can also be integrated in Spring according this article.

然而，这个主题一个月前在这里讨论过，它似乎不像在 Hibernate 中那么容易开始工作和/或它没有返回预期的信息：Spring JDBC 没有使用 log4j 记录 SQL每个下的这个主题建议使用P6Spy根据这篇文章，它也可以集成到 Spring 中。

回答by Kroky

This works for me with org.springframework.jdbc-3.0.6.RELEASE.jar. I could not find this anywhere in the Spring docs (maybe I'm just lazy) but I found (trial and error) that the TRACE level did the magic.

这对我有用 org.springframework.jdbc-3.0.6.RELEASE.jar。我在 Spring 文档中的任何地方都找不到它（也许我只是懒惰），但我发现（反复试验）TRACE 级别发挥了神奇作用。

I'm using log4j-1.2.15 along with slf4j (1.6.4) and properties file to configure the log4j:

我使用 log4j-1.2.15 以及 slf4j (1.6.4) 和属性文件来配置 log4j：

log4j.logger.org.springframework.jdbc.core = TRACE

This displays both the SQL statement and bound parameters like this:

这将显示 SQL 语句和绑定参数，如下所示：

Executing prepared SQL statement [select HEADLINE_TEXT, NEWS_DATE_TIME from MY_TABLE where PRODUCT_KEY = ? and NEWS_DATE_TIME between ? and ? order by NEWS_DATE_TIME]
Setting SQL statement parameter value: column index 1, parameter value [aaa], value class [java.lang.String], SQL type unknown
Setting SQL statement parameter value: column index 2, parameter value [Thu Oct 11 08:00:00 CEST 2012], value class [java.util.Date], SQL type unknown
Setting SQL statement parameter value: column index 3, parameter value [Thu Oct 11 08:00:10 CEST 2012], value class [java.util.Date], SQL type unknown

Not sure about the SQL type unknown but I guess we can ignore it here

不确定 SQL 类型未知，但我想我们可以在这里忽略它

For just an SQL (i.e. if you're not interested in bound parameter values) DEBUGshould be enough.

仅对于 SQL（即，如果您对绑定参数值不感兴趣）DEBUG就足够了。

回答by braincell

Parameter values seem to be printed on TRACE level. This worked for me:

参数值似乎打印在 TRACE 级别。这对我有用：

log4j.logger.org.springframework.jdbc.core.JdbcTem plate=DEBUG, file
log4j.logger.org.springframework.jdbc.core.StatementCreatorUtils=TRACE, file

Console output:

控制台输出：

02:40:56,519 TRACE http-bio-8080-exec-13 core.StatementCreatorUtils:206 - Setting SQL statement parameter value: column index 1, parameter value [Tue May 31 14:00:00 CEST 2005], value class [java.util.Date], SQL type unknown
02:40:56,528 TRACE http-bio-8080-exec-13 core.StatementCreatorUtils:206 - Setting SQL statement parameter value: column index 2, parameter value [61], value class [java.lang.Integer], SQL type unknown
02:40:56,528 TRACE http-bio-8080-exec-13 core.StatementCreatorUtils:206 - Setting SQL statement parameter value: column index 3, parameter value [80], value class [java.lang.Integer], SQL type unknown

回答by Vladislav Kysliy

I use this line for Spring Boot applications:

我将此行用于 Spring Boot 应用程序：

logging.level.org.springframework.jdbc.core = TRACE

This approach pretty universal and I usually use it for any other classes inside my application.

这种方法非常普遍，我通常将它用于我的应用程序中的任何其他类。

回答by JRichardsz

This worked for me with log4j2 and xml parameters:

这对我有用 log4j2 和 xml 参数：

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="debug">
    <Properties>
        <Property name="log-path">/some_path/logs/</Property>
        <Property name="app-id">my_app</Property>
    </Properties>

    <Appenders>
        <RollingFile name="file-log" fileName="${log-path}/${app-id}.log"
            filePattern="${log-path}/${app-id}-%d{yyyy-MM-dd}.log">
            <PatternLayout>
                <pattern>[%-5level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %c{1} - %msg%n
                </pattern>
            </PatternLayout>
            <Policies>
                <TimeBasedTriggeringPolicy interval="1"
                    modulate="true" />
            </Policies>
        </RollingFile>

        <Console name="console" target="SYSTEM_OUT">
            <PatternLayout
                pattern="[%-5level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %c{1} - %msg%n" />
        </Console>
    </Appenders>
    <Loggers>

        <Logger name="org.springframework.jdbc.core" level="trace" additivity="false">
            <appender-ref ref="file-log" />
            <appender-ref ref="console" />
        </Logger>

        <Root level="info" additivity="false">
            <appender-ref ref="file-log" />
            <appender-ref ref="console" />
        </Root>
    </Loggers>

</Configuration>

Result console and file log was:

结果控制台和文件日志是：

JdbcTemplate - Executing prepared SQL query
JdbcTemplate - Executing prepared SQL statement [select a, b from c where id = ? ]
StatementCreatorUtils - Setting SQL statement parameter value: column index 1, parameter value [my_id], value class [java.lang.String], SQL type unknown

Just copy/past

只需复制/过去

HTH

HTH

回答by 2787184

Try adding in log4j.xml

尝试添加 log4j.xml

<!--  enable query logging -->
<category name="org.springframework.jdbc.core.JdbcTemplate">
    <priority value="DEBUG" />
</category>

<!-- enable query logging for SQL statement parameter value -->
<category name="org.springframework.jdbc.core.StatementCreatorUtils">
    <priority value="TRACE" />
</category>

your logs looks like:

你的日志看起来像：

DEBUG JdbcTemplate:682 - Executing prepared SQL query
DEBUG JdbcTemplate:616 - Executing prepared SQL statement [your sql query]
TRACE StatementCreatorUtils:228 - Setting SQL statement parameter value: column index 1, parameter value [param], value class [java.lang.String], SQL type unknown

回答by danben

I'm not 100% sure what you're getting at since usually you will pass in your SQL queries (parameterized or not) to the JdbcTemplate, in which case you would just log those. If you have PreparedStatements and you don't know which one is being executed, the toStringmethod should work fine. But while we're on the subject, there's a nice Jdbc logger package herewhich will let you automatically log your queries as well as see the bound parameters each time. Very useful. The output looks something like this:

我不是 100% 确定你得到了什么，因为通常你会将你的 SQL 查询（参数化与否）传递给 JdbcTemplate，在这种情况下你只需记录这些。如果您有PreparedStatements 并且您不知道正在执行哪个，则该toString方法应该可以正常工作。但是当我们讨论这个主题时，这里有一个很好的 Jdbc 记录器包，它可以让你自动记录你的查询以及每次查看绑定参数。很有用。输出如下所示：

executing PreparedStatement: 'insert into ECAL_USER_APPT
(appt_id, user_id, accepted, scheduler, id) values (?, ?, ?, ?, null)'
     with bind parameters: {1=25, 2=49, 3=1, 4=1}