Seeing the underlying SQL in the Spring JdbcTemplate

Question

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

User · Answer

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

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

User · Answer

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 toString method should work fine   But while we re on the subject  there s a nice Jdbc logger package here which will let you automatically log your queries as well as see the bound parameters each time   Very useful   The output looks something like this   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

User · Answer

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    lt category name  org springframework jdbc core JdbcTemplate  gt       lt priority value  debug    gt   lt  category gt    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

User · Answer

This worked for me with log4j2 and xml parameters    lt  xml version  1 0  encoding  UTF-8   gt   lt Configuration status  debug  gt       lt Properties gt           lt Property name  log-path  gt  some path logs  lt  Property gt           lt Property name  app-id  gt my app lt  Property gt       lt  Properties gt        lt Appenders gt           lt RollingFile name  file-log  fileName    log-path    app-id  log              filePattern    log-path    app-id - d yyyy-MM-dd  log  gt               lt PatternLayout gt                   lt pattern gt   -5level   d yyyy-MM-dd HH mm ss SSS    t   c 1  -  msg n                  lt  pattern gt               lt  PatternLayout gt               lt Policies gt                   lt TimeBasedTriggeringPolicy interval  1                      modulate  true    gt               lt  Policies gt           lt  RollingFile gt            lt Console name  console  target  SYSTEM OUT  gt               lt PatternLayout                 pattern    -5level   d yyyy-MM-dd HH mm ss SSS    t   c 1  -  msg n    gt           lt  Console gt       lt  Appenders gt       lt Loggers gt            lt Logger name  org springframework jdbc core  level  trace  additivity  false  gt               lt appender-ref ref  file-log    gt               lt appender-ref ref  console    gt           lt  Logger gt            lt Root level  info  additivity  false  gt               lt appender-ref ref  file-log    gt               lt appender-ref ref  console    gt           lt  Root gt       lt  Loggers gt    lt  Configuration gt    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

User · Answer

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   I m using log4j-1 2 15 along with slf4j  1 6 4  and properties file to configure the log4j   log4j logger org springframework jdbc core   TRACE   This displays both the SQL statement and bound parameters like this   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  For just an SQL  i e  if you re not interested in bound parameter values  DEBUG should be enough

User · Answer

I use this line for Spring Boot applications   logging level org springframework jdbc core   TRACE   This approach pretty universal and I usually use it for any other classes inside my application

User · Answer

Try adding in log4j xml   lt  --  enable query logging -- gt   lt category name  org springframework jdbc core JdbcTemplate  gt       lt priority value  DEBUG    gt   lt  category gt    lt  -- enable query logging for SQL statement parameter value -- gt   lt category name  org springframework jdbc core StatementCreatorUtils  gt       lt priority value  TRACE    gt   lt  category gt    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

[sql] Seeing the underlying SQL in the Spring JdbcTemplate?

Examples related to sql

Examples related to jdbc

Examples related to spring-jdbc