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

Question

I am using spring org.springframework.jdbc.core.JdbcTemplate and org.springframework.jdbc.core.simple.SimpleJdbcCall for my database queries. I am using log4j2.xml for logging. I want to log all my sql queries and their DB result in db.log file.

I have tried to used following logger in my log4j2.xml file but it did not log anything in db.log file. I tried level="TRACE" and level="debug" but both not working.

    <RollingFile name="db" fileName="${sys:catalina.home}/logs/db.log"
        filePattern="${sys:catalina.home}/logs/$${date:yyyy-MM-dd}/db-%d{yyyy-MM-dd}-%i.log.gz">
        <PatternLayout
            pattern="%d{dd/MM/yyyy HH:mm:ss,SSS} [%X{cartID}] [%X{sessionId}] [%p] [%t] [%c] (%F:%L)  - %m%n" />
        <Policies>
            <TimeBasedTriggeringPolicy interval="1"
                modulate="true" />
            <SizeBasedTriggeringPolicy size="10 MB" />
        </Policies>
    </RollingFile>
</Appenders>
<Loggers>
    <Logger name="org.springframework.jdbc.core.JdbcTemplate" level="TRACE" additivity="false">
        <Appender-Ref ref="db" />
    </Logger>

In our java classes we are using following sql

String sQuery = "select count(*) from impersonation_requests where ir_eid = ? and  ir_tmp_userid = ?";
String value =  template
                .queryForObject(sQuery, new Object[] { passwordInfo.getEid(),
                        passwordInfo.getUserId() }, String.class);

Here var template is instance variable of org.springframework.jdbc.core.JdbcTemplate

I want to see sQuery and value entries in my db.log file. Can we achieve this using JdbcTemplate or I need to implement logger in all my DAO classes and log sQuery and values in each class where I am using JdbcTemplate. I want to avoid this approach. Please suggest.

score 9 · Accepted Answer · answered Mar 21 '14 at 09:53

9

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

org.springframework.jdbc

answered Mar 21 '14 at 09:53

Artem Bilan

113,505
11
91
118

I tried this but not worked. – vivek Mar 21 '14 at 10:08
Does it log anything at least for `org.springframework`? Do you have this bridge in your classpath: http://logging.apache.org/log4j/2.x/log4j-jcl/ ? – Artem Bilan Mar 21 '14 at 10:19
I tried org.springframework but nothing log in the file. Sorry I don't get "Do you have this bridge in your classpath". In my java code we are using slf4j logging and we are using slf4j bridge jar to connect with log4j2. – vivek Mar 24 '14 at 05:54
my pom dependencies. org.slf4j slf4j-api 1.7.5 org.slf4j slf4j-ext 1.7.5 org.apache.logging.log4j.adapters log4j-slf4j-impl 2.0-beta4 runtime – vivek Mar 24 '14 at 05:59
It's not enough: you have to have a bridge for Commons Logging, since org.springframework uses it. – Artem Bilan Mar 24 '14 at 10:17
could you please let me know what pom dependency I need to add for common logging. As I know slf4j only allow one bridge. Right now we are using log4j2 bridge. We can't use two bridge at a time with slf4j – vivek Mar 24 '14 at 16:27
This one: http://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-jcl/2.0-rc1. Please, don't mix SLF, Log4j and Commons Logging. You have bridge slf-log4j, but not have cl-log4j – Artem Bilan Mar 26 '14 at 17:55
I have the same issue and i follow your solution but it didn't work !! http://stackoverflow.com/questions/24658384 – Hayi Jul 09 '14 at 16:08

score 5 · Answer 2 · edited Nov 04 '16 at 08:30

<Logger name="org.springframework.jdbc.core.JdbcTemplate" level="TRACE" additivity="false">
      <Appender-Ref ref="db" />
</Logger>

This will definitely work. debug and trace both level will work. Only thing is JDBCTemplate is using common-logging API to log. And if you are using log4j in your application, you will have to add common logging bridge for the same.

Add following in your pom.xml

 <dependency>
     <groupId>org.apache.logging.log4j</groupId>
     <artifactId>log4j-jcl</artifactId>
     <version>2.0-rc1</version>
 </dependency>

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

2 Answers2