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

Community · Accepted Answer · 2017-05-23 12:25:45Z

13

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!

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Aug 30, 2016 at 6:09

Gunith D

1,8731 gold badge31 silver badges36 bronze badges

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

Comments

Matt · Accepted Answer · 2013-10-27 21:16:58Z

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, 2013 at 21:16

Matt

5,87815 gold badges56 silver badges80 bronze badges

1 Comment

Leon Proskurov Over a year ago

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

Arash · Accepted Answer · 2020-03-03 06:27:23Z

7

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

answered Mar 3, 2020 at 6:27

Arash

9442 gold badges11 silver badges21 bronze badges

Comments

Paul Vargas · Accepted Answer · 2013-07-04 14:18:31Z

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 4, 2013 at 14:18

Paul Vargas

42.2k16 gold badges108 silver badges148 bronze badges

4 Comments

eatSleepCode Over a year ago

where to write the configurations>

Paul Vargas Over a year ago

Hi, @eatSleepCode I can not follow you. What do you mean exactly?

eatSleepCode Over a year ago

I mean where to write the configuration, in log4j.xml? Also the thing is I am not able to get the output on console.

Paul Vargas Over a year ago

If you are using maven, put this configuration in the file log4j.xml in the src/main/resources folder. Otherwise, in the default package.

Nailgun · Accepted Answer · 2013-07-30 12:01:43Z

1

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

answered Jul 30, 2013 at 12:01

Nailgun

4,1894 gold badges35 silver badges47 bronze badges

Comments

user52fhy · Accepted Answer · 2022-08-03 06:56:03Z

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 3, 2022 at 6:56

user52fhy

312 bronze badges

Comments

Mafei · Accepted Answer · 2024-03-15 06:17:33Z

0

You can configure it by calling LogFactory.useStdOutLogging(); [java configuration] in the main method or somewhere when the connections factory is created.

Read more https://mybatis.org/mybatis-3/logging.html

answered Mar 15, 2024 at 6:17

Mafei

3,9683 gold badges23 silver badges43 bronze badges

Collectives™ on Stack Overflow

Log SQL queries in project using MyBatis and Spring

7 Answers 7

Comments

1 Comment

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related