Spring JDBC is not logging SQL with log4j

I'm researching spring for a possible switch to a spring stack. One of the things that I thought was cool was the ability for spring jdbc to log all the executed sql. So I put in log4j, set up a log4j.properties file. and no sql.

here is the log4j.properties file:

log4j.appender.stdout=org.apache.log4j.Cons开发者_运维问答oleAppe nder
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.Patt ernLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ ABSOLUTE} %5p %c{1}:%L - %m%n
log4j.rootLogger=debug, stdout
log4j.category.org.springframework.jdbc.core=DEBUG

here is the output for some really simple insert sql via spring jdbc: http://pastie.org/713189

Try setting these additional log4j loggers. The first will spit out the SQL that passes through spring's JdbcTemplate, the second gives you parameter values that Spring sets on prepared statements.

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

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

Clearly this is only going to work if you're directly or indirectly executing SQL using JdbcTemplate.

Are you sure this is the log4.properties that your application is picking up? I copied the log4j.properties you posted into a Spring application on my local machine, and I got a ton of Spring debug entries in addition to the JDBC logging. I don't see debug entries like that in your output.

A few probable culprits for your log4j.properties not getting read correctly are:

• log4j.properties isn't on your classpath. You can trying doing a class.getResource() on it to see if it's finding it at all.
• There's another log4j.properties on your classpath.
• Something is making commons-logging choose to use a different logger. You can find instructions for turning on the commons-logging diagnositics here

Try

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

This helped me, DEBUG just wasn't enough. I am using org.springframework.jdbc-3.0.6.RELEASE.jar with log4j-1.2.15 and slf4j (1.6.4)

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

From my understanding spring will print out information when you use prepared statement.

See this discussion on Spring's forum.

How are you executing the SQL? Spring won't magically log SQL that gets sent to the database, you have to go through the appropriate channels.

For example, if you execute SQL by using JdbcTemplate, either directly or via JdbcDaoSupport, then yes, the SQL will be logged for some operations, but only those operations that involve direct SQL.

If you use Hibernate or prepared statements, then Spring never gets to see the SQL itself, and therefore cannot log it.

If you posted some sample code that demonstrated how you were executing your SQL, it would help a lot.

Assuming Spring 3.0.7.RELEASE and log4j 1.2.17, we got the task accomplished via:

 <!-- activates query logging -->
 <category name="org.springframework.jdbc.core.JdbcTemplate">
    <level value="DEBUG"/>
 </category>
<!-- activates parameter substitution logging -->
<category name="org.springframework.jdbc.core.StatementCreatorUtils">
    <level value="TRACE"/>
</category>

..in our log4j.xml.

Replacing <category/> with <logger/> works also fine.