argh - can't get spring to log sql

Question

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

Answer 1

You could try using P6Spy. This is a JDBC driver that logs all SQL statements before passing them onto the real JDBC driver. Some information about how to integrate it with Spring is available here.

Another alternative is to enable logging at the database level, though this probably won't help much if multiple processes are accessing the DB simultaneously.

Answer 2

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

See this discussion on Spring's forum.

Answer 3

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.

Answer 4

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

Answer 5

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.