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

Solution 1

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.

Solution 2

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!

Solution 3

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

Solution 4

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>

Solution 5

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

View more solutions