Log SQL queries in project using MyBatis and Spring

Question

In my project i have

<bean id="ABCSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="ABCDataSource" />
  <property name="mapperLocations">
      <list>
        <value>classpath:com/myco/dao/XYZMapper.xml</value>
       </list>
  </property>
<bean>

and

log4j.logger.java.sql.Connection=debug, stdout, abclog
log4j.logger.java.sql.PreparedStatement=debug, stdout, abclog
log4j.logger.java.sql=debug, stdout, abclog
log4j.logger.org.mybatis=debug, stdout, abclog
log4j.logger.org.apache.ibatis=debug, stdout, abclog

I dont see the SQL queries when i run the applicartion in log Wanted to know what am i missing

saw this post how to configure log4j for Mybatis to print my SQL suggesting to change mybatis class configuration but not sure how to do with spring SqlSessionFactoryBean

score 13 · Answer 1 · edited May 23 '17 at 12:25

Quoting from an answer of how to configure logback for Mybatis to print my SQL, I'm not sure if this will work for you in entirety. It provides the Spring config for logging. This approach worked for me.

To log SQL statements for particular mybatis mapper set DEBUG (TRACE to see query parameters and results) level for logger with fully qualified mapper name

<logger name="com.mycompany.myapp.mapper.MyMapper" level="DEBUG"/>

You can log all SQL statements from all mappers if they are in the same package like this

<logger name="com.mycompany.myapp.mapper" level="DEBUG"/>

Give it a go, if the problem is still there. Good luck!

score 12 · Answer 2 · answered Oct 27 '13 at 21:16

12

You can add logging for Mybatis via it's mybatis-config.xml.

Add log4j like so:

mybatis-config.xml

<configuration>
  <settings>
    ...
    <setting name="logImpl" value="LOG4J"/>
    ...
  </settings>
</configuration>

Then in your log4j.properties, add the class that you'd like to log:

log4j.logger.org.mybatis.example.MyMapper=TRACE

SQL statements are logged at the DEBUG level, so set output to DEBUG:

log4j.logger.org.mybatis.example=DEBUG

For more details, see the documentation.

answered Oct 27 '13 at 21:16

Matt

5,408
14
52
79

Configuring DEBUG log level will show the query sent + parameters + number of results. Configuring TRACE log level will add the query response columns and values – Leon Proskurov Oct 29 '20 at 11:29

score 5 · Answer 3 · answered Mar 03 '20 at 06:27

Another shortcut is to set the debug level of your mybatis mappers to true in your application.properties file:

logging.level.<packageName>.mapper=DEBUG

Example log printed in console or your log file:

2020-03-03 09:41:27.057 DEBUG 10495 --- [io-50006-exec-1] c.f.t.d.m.M.countByExample               : ==>  Preparing: SELECT count(*) FROM MessageRecivers WHERE ((ReciverId = ? and ReadStats = ? and ReciverMessageFolder <> ?)) 
2020-03-03 09:41:27.066 DEBUG 10495 --- [io-50006-exec-1] c.f.t.d.m.M.countByExample               : ==> Parameters: 58(Long), 0(Short), 1(Short)
2020-03-03 09:41:27.473 DEBUG 10495 --- [io-50006-exec-1] c.f.t.d.m.M.countByExample               : <==      Total: 1

score 1 · Answer 4 · answered Jul 04 '13 at 14:18

1

Test with the simplest way configuration and see in the log. Then customize the output (e.g. the files, levels).

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//log4j/log4j Configuration//EN" 
                                     "log4j.dtd" >
<log4j:configuration>

  <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d %-5p (%c.java:%L).%M - %m%n"/>
    </layout>
  </appender>

  <root>
    <priority value="TRACE" />
    <appender-ref ref="STDOUT"/>
  </root>

</log4j:configuration>

answered Jul 04 '13 at 14:18

Paul Vargas

41,222
15
102
148

where to write the configurations> – eatSleepCode Mar 15 '15 at 13:16
Hi, @eatSleepCode I can not follow you. What do you mean exactly? – Paul Vargas Mar 16 '15 at 02:14
I mean where to write the configuration, in log4j.xml? Also the thing is I am not able to get the output on console. – eatSleepCode Mar 16 '15 at 05:20
If you are using maven, put this configuration in the file `log4j.xml` in the `src/main/resources` folder. Otherwise, in the default package. – Paul Vargas Mar 16 '15 at 05:25

score 1 · Answer 5 · answered Jul 30 '13 at 12:01

Try to add all the necessary lines to your configuration.

Here is the sample that should work:

#configure root logger
log4j.rootLogger=ERROR, file, stdout

#configure all mybatis mappers logging
log4j.logger.com.myco.dao=ERROR
#configure your mapper logging
log4j.logger.com.myco.dao.XYZMapper=DEBUG

#configure appender
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

score 0 · Answer 6 · answered Aug 03 '22 at 06:56

0

Two way to implement:

edit logback.xml:

<logger name="mapperPackageName" level="debug"/>

edit application.properties

logging.level.<mapperPackageName>=DEBUG

the mapperPackageName is the package where the Mapper class is located.

answered Aug 03 '22 at 06:56

user52fhy

31
2

Log SQL queries in project using MyBatis and Spring

6 Answers6

Linked