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.

Artem Bilan · Accepted Answer · 2014-03-21 09:53:13Z

9

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

org.springframework.jdbc

answered Mar 21, 2014 at 9:53

Artem Bilan

123k11 gold badges103 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

vivek Over a year ago

I tried this but not worked. <Logger name="org.springframework.jdbc" level="TRACE" additivity="false">

Artem Bilan Over a year ago

Does it log anything at least for org.springframework? Do you have this bridge in your classpath: logging.apache.org/log4j/2.x/log4j-jcl ?

vivek Over a year ago

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 Over a year ago

my pom dependencies. <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.5</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-ext</artifactId> <version>1.7.5</version> </dependency> <dependency> <groupId>org.apache.logging.log4j.adapters</groupId> <artifactId>log4j-slf4j-impl</artifactId> <version>2.0-beta4</version> <scope>runtime</scope> </dependency>

Artem Bilan Over a year ago

It's not enough: you have to have a bridge for Commons Logging, since org.springframework uses it.

|

Dulaj Atapattu · Accepted Answer · 2016-11-04 08:30:57Z

5

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

edited Nov 4, 2016 at 8:30

Dulaj Atapattu

4467 silver badges23 bronze badges

answered Apr 4, 2014 at 11:24

Gomti

1261 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related