【SpringBoot应用篇】SpringBoot集成p6spy格式化SQL

目录

前言

1、p6spy快速集成

1.1.pom.xml添加依赖

1.2.修改数据库连接配置application.properties

1.3.P6Spy的配置

1.4.自定义sql打印日志

1.5.yml中增加启用开关 

2.补充说明日志文件

---

前言

        p6spy是针对数据库访问操作的动态监测框架（开源项目）它使得数据库数据可无缝截取和操纵，而不必对现有应用程序的代码作任何修改。常见集成p6spy的方式是在资源目录下新增spy.properties配置文件，然后通过驱动装载。这种方式已经非常简单易用了，但是还有更简单的方式，通过spring boot autoconfig的特性，可以做到自动装配

        p6spy的一个非常典型的用例是启用 SQL 日志记录来解决开发过程中的各种数据库相关问题。直接打印已拼接参数的sql

1、p6spy快速集成

1.1.pom.xml添加依赖

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

<!-- https://mvnrepository.com/artifact/com.github.gavlyukovskiy/p6spy-spring-boot-starter -->
<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.9.0</version>
</dependency>

1.2.修改数据库连接配置application.properties

上边为spring配置，下边为p6spy配置

#数据源
#spring.datasource.url=jdbc:oracle:thin:@192.168.5.10:1521:ky
#spring.datasource.username=sjch_dz
#spring.datasource.password=MsI1#11OI1#1
#spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

#p6spy配置
spring.datasource.url=jdbc:p6spy:oracle:thin:@192.168.5.10:1521:ky
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=sjch_dz
spring.datasource.password=MsI1#11OI1#1

1.3.P6Spy的配置

在resources新建文件spy.properties

modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
# 1、logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
# 2、logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
# customLogMessageFormat=%(currentTime) | SQL use time: %(executionTime) ms | connect info: %(category)-%(connectionId) | execute sql: %(sql)
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=1

1.4.自定义sql打印日志

P6Spy有内置的SQL输出格式，如上配置文件。这里我们使用自定义SQL日志打印

public class P6SPYConfig  implements MessageFormattingStrategy {
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        Map<String, Object> message = new LinkedHashMap<>(8);
        String newPrepared = prepared.replace("   ", "").replace("\n", " ");
        message.put("prepared", newPrepared);
        String newSql = sql.replace("   ", "").replace("\n", " ");
        message.put("sql", newSql);
        return JSONObject.toJSONString(message, true);
    }
}

1.5.yml中增加启用开关 

# p6spy sql打印
decorator:
  datasource:
    enabled: true # 是否启用

更多配置查看 Configuration and Usage — p6spy 3.9.2-SNAPSHOT documentation

控制台数据如下所示：

2021-08-17 14:37:18.859 [scheduling-1] DEBUG org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession  
2021-08-17 14:37:18.859 [scheduling-1] DEBUG org.mybatis.spring.SqlSessionUtils - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1c6c34a2] was not registered for synchronization because synchronization is not active  
2021-08-17 14:37:18.859 [scheduling-1] DEBUG o.m.spring.transaction.SpringManagedTransaction - JDBC Connection [HikariProxyConnection@312641135 wrapping com.p6spy.engine.wrapper.ConnectionWrapper@7d5d3ad7] will not be managed by Spring  
2021-08-17 14:37:18.860 [scheduling-1] DEBUG c.y.e.mapper.EticketNotesMapper.insertAndupdate - ==>  Preparing: begin update ETICKET_NOTES set TABLE_SEQ = ?, TASK_ID = ?, REQUES_TID = ?, TICKET_STATE = ?, STATE_TIME = ?, RESULT_CODE = ?, RESULT_MSG = ?, DATABASE_TIMESTAMP = ?, LASTTIMESTAMP = sysdate where TABLE_SEQ = ? AND MAC_ID = ? AND ETICKET_STATE = ? AND NETTICKE_TID = ?; if sql%notfound then insert into ETICKET_NOTES (RES_SEQ, TABLE_SEQ, MAC_ID, TASK_ID, REQUES_TID, NETTICKE_TID, TICKET_STATE, STATE_TIME, RESULT_CODE, RESULT_MSG, DATABASE_TIMESTAMP,ETICKET_STATE,LASTTIMESTAMP) values (NOTES_SEQUENCE.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,sysdate); end if; end;  
2021-08-17 14:37:18.860 [scheduling-1] DEBUG c.y.e.mapper.EticketNotesMapper.insertAndupdate - ==> Parameters: 800180010000000032497276(String), jAUwlocXAL0Ymgu3fJvt(String), 202129182238859294(String), 2(String), 2021-08-17 12:21:01(String), 10000(String), 成功(String), 2021-08-17 12:21:09(String), 800180010000000032497276(String), 8001(String), 02(String), 5080010002100020263820(String), 800180010000000032497276(String), 8001(String), jAUwlocXAL0Ymgu3fJvt(String), 202129182238859294(String), 5080010002100020263820(String), 2(String), 2021-08-17 12:21:01(String), 10000(String), 成功(String), 2021-08-17 12:21:09(String), 02(String)  
 Consume Time：16 ms 2021-08-17 14:37:18
 Execute SQL：begin update ETICKET_NOTES set TABLE_SEQ = '800180010000000032497276', TASK_ID = 'jAUwlocXAL0Ymgu3fJvt', REQUES_TID = '202129182238859294', TICKET_STATE = '2', STATE_TIME = '2021-08-17 12:21:01', RESULT_CODE = '10000', RESULT_MSG = '成功', DATABASE_TIMESTAMP = '2021-08-17 12:21:09', LASTTIMESTAMP = sysdate where TABLE_SEQ = '800180010000000032497276' AND MAC_ID = '8001' AND ETICKET_STATE = '02' AND NETTICKE_TID = '5080010002100020263820'; if sql%notfound then insert into ETICKET_NOTES (RES_SEQ, TABLE_SEQ, MAC_ID, TASK_ID, REQUES_TID, NETTICKE_TID, TICKET_STATE, STATE_TIME, RESULT_CODE, RESULT_MSG, DATABASE_TIMESTAMP,ETICKET_STATE,LASTTIMESTAMP) values (NOTES_SEQUENCE.nextval, '800180010000000032497276', '8001', 'jAUwlocXAL0Ymgu3fJvt', '202129182238859294', '5080010002100020263820', '2', '2021-08-17 12:21:01', '10000', '成功', '2021-08-17 12:21:09','02',sysdate); end if; end;

2.补充说明日志文件

        默认情况下，将在当前工作目录中创建一个名为 spy.log 的文件。要自定义日志记录（包括使用应用程序的日志记录框架），您可以在名为 spy.properties 的文件中提供备用配置。这个文件只需要在类路径的根目录下。有关 详细信息，请参阅配置和使用。