java - 如何配置 Spring 以显示 JdbcTemplate 执行的查询？

Question

我正在使用 Spring 开发一批。为了执行查询，我使用JdbcTemplate。

在我的具体情况下，JdbcTemplate 被提供给扩展 Spring JdbcDaoSupport 类的 DAO 类，因此我有以下 DAO:

@Service
@Scope(proxyMode = ScopedProxyMode.TARGET_CLASS)
public class PucManagerColl extends JdbcDaoSupport {

private static Logger log = Logger.getLogger(PucManagerProd.class);

    public PucManagerColl() {
        System.out.println("Costruzione PucManager");
    }

    @Autowired
    public PucManagerColl(@Qualifier("dataSourcePUCColl") DataSource dataSource) {
        setDataSource(dataSource);

    }

    ...............................................................
    ...............................................................

        public void insertTirConsolidatoPolizza(TassoRendimentoInterno tri) {

        log.debug("PucManagerColl.insertTirConsolidatoPolizza()");

        String sql = "INSERT INTO TirConsolidatoPolizza "
                   + "("
                   + "Polizzaid, "
                   + "PercentualeRendimentoDaInizioGestione, "
                   + "PercentualeRendimentoDaInizioAnno, "
                   + "PercentualeRendimentoDaInizioTrimestre, "
                   + "ControvaloreFinaleBF, "
                   + "DataRiferimentoNav"
                   + ") "
                   + "VALUES (?, ?, ?, ?, ?, ?, ?)";


        this.getJdbcTemplate().update(sql,  
                  new Object[] { tri.getPolizzaID(),
                                 tri.getPercRendimentoInizioSottoscrizione(),
                                 tri.getPercRendimentoInizioAnno(),
                                 tri.getPercRendimentoInizioTrimestre(),
                                 tri.getControvaloreQuote(),
                                 tri.getDataRiferimentoNavPUC()});


        log.debug("TERMINATO: PucManagerColl.insertTirConsolidatoPolizza()");
    }
}

正如你所看到的，这个类扩展了Spring类JdbcDaoSupport并且数据源被注入(inject)到构造函数中。

问题是 insertTirConsolidatoPolizza() 方法无法将记录插入到 TirConsolidatoPolizza 表中。我没有得到任何错误，也没有异常(exception)。为什么？您注意到我的查询有问题吗？

所以我想调试由 JdbcTemplate 生成的查询，并尝试在数据库提示符中手动执行它，但我看不到它。

阅读这篇文章:Seeing the underlying SQL in the Spring JdbcTemplate?

以及官方文档:http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html

在我看来，我可以配置JdbcTemplate以在 Debug模式或类似模式下记录查询。

在另一篇文章中显示此 XML 片段来执行此操作:

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

但我不知道该把它放在哪里。

这是我的databaseConfiguration.xml，其中包含数据库连接的信息(我将此文件导入到主配置文件applicationContext.xml中):

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
                http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">


    <tx:annotation-driven transaction-manager="tjtJTransactionManager" />

    <!-- DB CONNECTIONS: -->
    <bean id="tjtJTransactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
        scope="singleton">
        <property name="dataSource" ref="dataSourcePUC" />
    </bean>

    <!-- PROFILO DI PRODUZIONE -->
    <beans profile="PROD">

        <bean id="dataSourcePUC" class="org.apache.commons.dbcp.BasicDataSource"
            destroy-method="close">
            <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
            <property name="url" value="jdbc:jtds:sqlserver://XXX.myCompany.YYYY.it:1433/DB_1" />
            <property name="username" value="username" />
            <property name="password" value="pswd" />
        </bean>

    </beans>

    <!-- PROFILO DI COLLAUDO -->
    <beans profile="COLL">

        <bean id="dataSourcePUC" class="org.apache.commons.dbcp.BasicDataSource"
            destroy-method="close">
            <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
            <property name="url" value="jdbc:jtds:sqlserver://XXXX.MYCOMPANY.YYYY.it:1433/DB_2" />
            <property name="username" value="username" />
            <property name="password" value="pswd" />

        </bean>

    </beans>

</beans>

如何配置查询日志记录以在堆栈跟踪中查看执行的查询？

Answer 1

How can I configure Spring to show the query performed by JdbcTemplate?

这取决于您使用的日志框架。例如，对于 log4j，您可以在 log4j.xml 中放置以下行

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

  <appender name="console" class="org.apache.log4j.ConsoleAppender">
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d{dd.MM.yyyy HH:mm:ss} [%t] %m%n" />
    </layout>
  </appender>

  <root>
    <priority value ="warn" />
    <appender-ref ref="console"/>
  </root>

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

您必须确保 log4j.xml 位于项目类路径中。请参阅https://logging.apache.org/log4j/2.x/manual/configuration.html详细的配置说明。

PS:顺便说一下，正确的查询应该是这样的

getJdbcTemplate().update(sql,  
              tri.getPolizzaID(),
              tri.getPercRendimentoInizioSottoscrizione(),
              tri.getPercRendimentoInizioAnno(),
              tri.getPercRendimentoInizioTrimestre(),
              tri.getControvaloreQuote(),
              tri.getDataRiferimentoNavPUC());