Seeing the Underlying SQL in the Spring Jdbctemplate

Seeing the underlying SQL in the Spring JdbcTemplate?

The Spring documentation says they're logged at DEBUG level:

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

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

<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 log4j This topic under each suggests to use P6Spy which can also be integrated in Spring according this article.

Spring Boot JDBC Template SQL Log

Try

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

The above statement will print SQL queries with inbound parameters as well.

Incase you need to log only the query use the following

log4j.category.org.springframework.jdbc.core = DEBUG

You can enable in your logback file with the following

<logger name="org.springframework.jdbc.core.JdbcTemplate">
  <level value="debug" />
</logger>

<logger name="org.springframework.jdbc.core.StatementCreatorUtils">
  <level value="debug" />
</logger>

Update : For Springboot 2.x , it would be

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

Thanks zhuguowei!

How to log SQL statements and parameters with Spring Data JDBC

Spring Data JDBC uses a JdbcTemplate to execute all SQL statements.
So you really need to configure logging for that.

The statements itself are logged by the template itself with DEBUG level.
To see these you have to set the log level of org.springframework.jdbc.core.JdbcTemplate to DEBUG.

The arguments for the bind variables are logged by org.springframework.jdbc.core.StatementCreatorUtils on TRACE level.

How you configure those depends on your project setup.
You can use the configuration options of Log4J, Logback or whatever logging implementation you are using.
Or if you happen to use Spring Boot the following rows in the application.properties should do the trick.

logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE
logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG

Or simply

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

If you don't mind possibly getting some extra output.

spring boot could show sql even use JdbcTemplate directly

There is a reason why the property is named spring.jpa something: it is to indicate you that it relates to JPA in some form.

If you're using JdbcTemplate, you're not using JPA hence that property can't have any effect. You can enable logging for the org.springframework.jdbc.core.JdbcTemplate class

logging.level.org.springframework.jdbc.core.JdbcTemplate=debug

How to log spring JdbcTemplate sql queries and DB response in log file using log4j2

I'd say name="org.springframework.jdbc.core.JdbcTemplate" is very 'strict'. Try this category:

org.springframework.jdbc

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

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

It depends on logging framework you use.
For example, for log4j you can place following lines in 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> 

You have to make sure log4j.xml is in the project class path.
See https://logging.apache.org/log4j/2.x/manual/configuration.html for detailed configuration instructions.

PS: By the way, correct query should look like

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

How to enable sql queries console logging in spring JDBC

You should add this in the property file

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

This will enable Spring Jdbc logs

How can I log SQL statements in Spring Boot?

Try using this in your properties file:

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

Spring JDBC and Java 8 - JDBCTemplate: retrieving SQL statement and parameters for debugging

I found a way to get the SQL-statement, so I will answer my own question :)

The PreparedStatementCreator has the following implementation:

private static class SimplePreparedStatementCreator implements PreparedStatementCreator, SqlProvider 

So the SqlProvider has a getSql() method which does exactly what I need.

Posting the "improved" CustomJdbcTemplate class if anyone ever should need to do the same :)

public class CustomJdbcTemplate extends JdbcTemplate {
private static final Logger log = LoggerFactory.getLogger(CustomJdbcTemplate.class);

public CustomJdbcTemplate(DataSource dataSource) {
    super(dataSource);
}

public <T> T query(PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor<T> rse)
        throws DataAccessException {
    if(log.isDebugEnabled()) {
        if(pss instanceof ArgumentPreparedStatementSetter) {
            ArgumentPreparedStatementSetter aps = (ArgumentPreparedStatementSetter) pss;
            try {
                Field args = aps.getClass().getDeclaredField("args");
                args.setAccessible(true);
                Object[] parameters = (Object[]) args.get(aps);
                log.debug("SQL query: [{}]\tParams: {} ", getSql(psc), Arrays.toString(parameters));
            } catch (NoSuchFieldException | IllegalAccessException e) {
                throw new GenericException(e.toString(), e);
            }
        }
    }
    return super.query(psc, pss, rse);
}

private static String getSql(Object sqlProvider) { // this code is also found in the JDBCTemplate class 
    if (sqlProvider instanceof SqlProvider) {
        return ((SqlProvider) sqlProvider).getSql();
    }
    else {
        return null;
    }
}

}

---

Related Topics