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).

Answer 1

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}

Answer 2

The Spring documentation tells that 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 as

<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: http://stackoverflow.com/questions/1792244/argh-cant-get-spring-to-log-sql This topic under each suggests to use P6Spy which can also be integrated in Spring according this article.