springboot 打印sql执行信息日志

一、mybatis方式

在你的application.yml文件下面加入下面这段代码

mybatis
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl 

项目启动在你的控制台看到上面这种就说明配置成了：

打印出来的形式如下

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2e943ddb]
JDBC Connection [HikariProxyConnection@898692052 wrapping com.mysql.jdbc.JDBC4Connection@6a0c5a04] will be managed by Spring
==>  Preparing: DELETE FROM user_info WHERE uid in ( ? , ? , ? )
==> Parameters: 44(Long), 45(Long), 46(Long)
<==    Updates: 0
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2e943ddb]
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2e943ddb]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2e943ddb]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2e943ddb]

二、日志方式

在你的application.yml文件下面加入下面这段代码

logging:
  level:
    com.seamax.bdsearch.dao: DEBUG

注意：com.seamax.bdsearch.dao = 你的mapper包。

打印出来的形式如下：

2019-01-24 08:02:14.245 [http-nio-8060-exec-2] DEBUG c.s.a.m.m.U.getUsernameExistSet 159 - ==>  Preparing: SELECT username FROM user_info WHERE username in ( ? , ? , ? ) 
2019-01-24 08:02:14.245 [http-nio-8060-exec-2] DEBUG c.s.a.m.m.U.getUsernameExistSet 159 - ==> Parameters: nike16(String), nike14(String), nike15(String)
2019-01-24 08:02:14.307 [http-nio-8060-exec-2] DEBUG c.s.a.m.m.U.getUsernameExistSet 159 - <==      Total: 0
2019-01-24 08:02:14.323 [http-nio-8060-exec-2] DEBUG c.s.a.m.mapper.UserMapper.saveBatch 159 - ==>  Preparing: INSERT INTO user_info ( username, password, email, telphone, birthday, createTime, updateTime ) values ( ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ? ) 
2019-01-24 08:02:14.323 [http-nio-8060-exec-2] DEBUG c.s.a.m.mapper.UserMapper.saveBatch 159 - ==> Parameters: nike14(String), 4f757a334d69b32b586f3694fbaaa9a9869aee184f98e009b6e02b170f92eb9f(String), [e-mail](String), null, 2018-03-02 02:01:02.0(Timestamp), 2019-01-24 08:02:14.307(Timestamp), 2019-01-24 08:02:14.307(Timestamp), nike15(String), 18a1c9f3e7a69e3f72ab5d80caea96e5c90f5fada8f9a7e92238dc4242ba03f8(String), [e-mail](String), null, 2018-03-02 02:01:02.0(Timestamp), 2019-01-24 08:02:14.307(Timestamp), 2019-01-24 08:02:14.307(Timestamp), nike16(String), 5912bd4ff3ae134b15347610b64d9f352dd3c89dd2fb5c495cf4699683b33271(String), [e-mail](String), null, 2018-03-02 02:01:02.0(Timestamp), 2019-01-24 08:02:14.307(Timestamp), 2019-01-24 08:02:14.307(Timestamp)
2019-01-24 08:02:14.338 [http-nio-8060-exec-2] DEBUG c.s.a.m.mapper.UserMapper.saveBatch 159 - <==    Updates: 3

 三、log4jdbc-log4j2使用

有时候我们程序的接口比较耗时，需要优化，这时我们可能需要了解该接口执行了哪些sql语句以及耗时

1.引入jar包

        <!--监控sql日志-->
        <dependency>
            <groupId>org.bgee.log4jdbc-log4j2</groupId>
            <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
            <version>1.16</version>
        </dependency>

2.引入配置文件 log4jdbc.log4j2.properties

内容为：

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

3.logback-spring.xml 里加入如下内容

    <logger name="jdbc.connection" level="OFF"/>
    <logger name="jdbc.resultset" level="OFF"/>
    <logger name="jdbc.resultsettable" level="OFF"/>
    <logger name="jdbc.audit" level="OFF"/>
    <!--ps：sqltiming和sqlonly差别是多打印了sql语句的耗时，如果不想要这个时间可以修改筛选里面的项。-->
    <logger name="jdbc.sqltiming" level="INFO"/>
    <logger name="jdbc.sqlonly" level="OFF"/>

4.配置文件中修改部分属性值

如图：

即：

  driver-class-name 修改为：net.sf.log4jdbc.sql.jdbcapi.DriverSpy

  url 修改为：jdbc:log4jdbc:mysql:// （在原来的jdbc后面加了：log4jdbc：）

---------------------------------------------------------------------------    

以上完成任务，下面看下效果

---------------------------------------------------------------------------

注意：除了打印sql执行时间外，打印的sql语句是包含sql所带的参数的，select语句的话where后面带参数值，insert语句的话插入对象的值都有

 

 四、p6spy使用

1.导入依赖

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.8.6</version>
</dependency>

2.修改application.properties中的数据库驱动和url

#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.url=jdbc:mysql://localhost:3306/db?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=true
#更换为com.p6spy.engine.spy.P6SpyDriver
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/data?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=true
spring.datasource.username=root
spring.datasource.password=123456

3.在application.properties同级目录下创建spy.properties配置文件

module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.fkzd.framework.config.P6spySqlFormatConfig //自定义P6SpyLogger类的地址
# 使用日志系统记录sql
appender=com.p6spy.engine.spy.appender.StdoutLogger
## 配置记录Log例外
excludecategories=info,debug,result,batc,resultset
# 设置使用p6spy driver来做代理
deregisterdrivers=true
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动
driverlist=com.mysql.cj.jdbc.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 秒
outagedetectioninterval=2

4.自定义sql输出

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import com.fkzd.common.utils.DateUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.Date;

/**
 * 自定义 p6spy sql输出格式
 *
 */
public class P6spySqlFormatConfig implements MessageFormattingStrategy {

    /**
     * 过滤掉定时任务的 SQL
     */
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        return StringUtils.isNotBlank(sql) ? DateUtils.parseDateToStr("yyyy-MM-dd HH:mm:ss", new Date())
                + " | 耗时 " + elapsed + " ms | SQL 语句：" + StringUtils.LF + sql.replaceAll("[\\s]+", StringUtils.SPACE) + ";" : "";
    }
}

执行效果