Spring-boot with Spring -mybatis - how to get it to log all SQL queries

Question

I have a simple spring-boot-mybatis application (remember please). Mybatis logs SQL queries only in the event of a failure (by exception). Please tell me how to make it register the entire SQL query to the console?

At this point I am using slf4jlogger (automatically configured spring-boot).
I find this link: http://www.mybatis.org/mybatis-3/logging.html
however I did not succeed. First of all, the configuration is shown for log4j, and I'm not sure. If I understand correctly: is it enough to configure in application.properties?

Thanks in advance

Answer 1

Spring boot uses the log file as the default logging provider for Slf4j. The Ibatis factory internal magazine loads SLF4j as the registrar of first choice. All you have to do is configure spring boot log to publish log messages for ibatis mapper.

Add the following lines to the properties of the boot application.

logging.level.org.springframework=WARN
logging.level.com.spring.ibatis.UserMapper=DEBUG
logging.file=logs/spring-boot-logging.log

In the second line, you define the log entry for ibatis mapper with the DEBUG log level. com.spring.ibatisis a package, but UserMappera sample.

The following logs will appear in the console and in the spring-boot-logging file. These are the log messages created from saveUserand findByNamethe class method ApplicationTest.

2016-12-19 22:07:06.358  INFO 7248 --- [main] com.spring.ibatis.ApplicationTest        : Started ApplicationTest in 3.048 seconds (JVM running for 4.209)
2016-12-19 22:07:06.424 DEBUG 7248 --- [main] com.spring.ibatis.UserMapper.saveUser    : ==>  Preparing: insert into users(name) values(?) 
2016-12-19 22:07:06.444 DEBUG 7248 --- [main] com.spring.ibatis.UserMapper.saveUser    : ==> Parameters: ibatis(String)
2016-12-19 22:07:06.445 DEBUG 7248 --- [main] com.spring.ibatis.UserMapper.saveUser    : <==    Updates: 1
2016-12-19 22:07:06.457 DEBUG 7248 --- [main] com.spring.ibatis.UserMapper.findByName  : ==>  Preparing: select name from users WHERE name=? 
2016-12-19 22:07:06.470 DEBUG 7248 --- [main]  com.spring.ibatis.UserMapper.findByName  : ==> Parameters: ibatis(String)
2016-12-19 22:07:06.504 DEBUG 7248 --- [main]  com.spring.ibatis.UserMapper.findByName  : <==      Total: 1

, , , . , .

Junit https://github.com/saagar2000/ibatis

Answer 2

SLF4J log4j, . log4j /.

https://softwareengineering.stackexchange.com/questions/108683/slf4j-vs-log4j-which-one-to-prefer

log4j-, slf4j, log4j slf4j.

, , slf4j log4j logger: http://saltnlight5.blogspot.ca/2013/08/how-to-configure-slf4j-with-different.html

: src/main/resources/log4j.properties , , . :

... SQL- DEBUG (FINE JDK) TRACE (FINER JDK)...

, log4j.logger.org.mybatis.example=DEBUG .

Answer 3

-, log4jdbc2, SQL, , . (, iBatis, JPA ..).

https://code.google.com/archive/p/log4jdbc-log4j2/

, SQL DB- .

1 Maven:

<dependency>
    <groupId>org.bgee.log4jdbc-log4j2</groupId>
    <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
    <version>1.16</version>
</dependency>

2 . logback.xml

<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
        <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n
        </pattern>
    </encoder>
</appender>

<logger name="jdbc.audit" level="ERROR" />
<logger name="jdbc.connection" level="ERROR" />
<logger name="jdbc.sqltiming" level="ERROR" />
<logger name="jdbc.resultset" level="ERROR" />

<!-- UNCOMMENT THE BELOW TO HIDE THE RESULT SET TABLE OUTPUT -->
<!--<logger name="jdbc.resultsettable" level="ERROR" /> -->

<root level="debug">
    <appender-ref ref="STDOUT" />
</root>

3 log4jdbc2 :

log4jdbc.log4j2.properties src/test/resources src/main/resources Maven. , :

log4jdbc.spylogdelegator.name = net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

4 URL- DB, :

spring.database.driverClassName=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
#append log4jdbc after jdbc part of the URL: hsql example
spring.datasource.url=jdbc:log4jdbc:hsqldb:mem:db_name 

SQL . .

:

10:44:29.400 [main] DEBUG jdbc.sqlonly -
5. select memberrole0_.member_id as member_i2_12_0_, memberrole0_.id as id1_12_0_, memberrole0_.id 
as id1_12_1_, memberrole0_.member_id as member_i2_12_1_, memberrole0_.role_id as role_id3_12_1_, 
role1_.id as id1_17_2_, role1_.name as name2_17_2_ from member_roles memberrole0_ left outer 
join roles role1_ on memberrole0_.role_id=role1_.id where memberrole0_.member_id=104 

10:44:29.402 [main] INFO  jdbc.resultsettable - 
|----------|---|---|----------|--------|---|-----|
|member_id |id |id |member_id |role_id |id |name |
|----------|---|---|----------|--------|---|-----|
|----------|---|---|----------|--------|---|-----|

Answer 4

If you use mybatis with JPArepository, this works, add this to your application.properties file.

    spring.jpa.properties.hibernate.show_sql=true
    spring.jpa.properties.hibernate.use_sql_comments=true
    spring.jpa.properties.hibernate.format_sql=true
    spring.jpa.properties.hibernate.type=trace