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

快来打我* 2023-10-03 13:57 34阅读 0赞

**目录**

前言

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>
    
    
    <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][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 的文件中提供备用配置。这个文件只需要在类路径的根目录下。有关 详细信息，请参阅[配置和使用][Configuration and Usage _ p6spy 3.9.2-SNAPSHOT documentation]。

[Configuration and Usage _ p6spy 3.9.2-SNAPSHOT documentation]: https://p6spy.readthedocs.io/en/latest/configandusage.html