SpringBoot怎么集成P6Spy实现SQL日志

P6Spy简介

P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架。

通过P6Spy可以对SQL语句进行拦截，相当于一个SQL语句的记录器，这样我们可以用它来作相关的分析，比如性能分析。

应用场景

pom

<dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter</artifactId>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-test</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>druid-spring-boot-starter</artifactId>

            <version>1.1.20</version>

        </dependency>

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

        </dependency>

        <dependency>

            <groupId>com.baomidou</groupId>

            <artifactId>mybatis-plus-boot-starter</artifactId>

            <version>3.1.1</version>

        </dependency>

        <dependency>

            <groupId>org.projectlombok</groupId>

            <artifactId>lombok</artifactId>

        </dependency>

        <dependency>

            <groupId>p6spy</groupId>

            <artifactId>p6spy</artifactId>

            <version>3.8.2</version>

        </dependency>

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>fastjson</artifactId>

            <version>1.2.83</version>

        </dependency>

    </dependencies>

application.yml

spring:

  datasource:

    driver-class-name: com.mysql.cj.jdbc.Driver

    #driver-class-name: com.p6spy.engine.spy.P6SpyDriver

    type: com.alibaba.druid.pool.DruidDataSource

    url: jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8

    # url: jdbc:p6spy:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8

    username: root

    password: root

# 打开mybatis-plus的sql日志输出

mybatis-plus:

  configuration:

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

entity

@Data

@NoArgsConstructor

@AllArgsConstructor

@TableName("course_1")

public class Course {

    @TableField("cid")

    private Long cId;

    private String cName;

    private Integer userId;

    private String cStatus;

}

Mapper

public interface CourseMapper extends BaseMapper<Course> {

}

启动类

@SpringBootApplication

@MapperScan(basePackages = "cn.zysheep.mapper")

public class ShardingjdbcdemoApplication {

    public static void main(String[] args) {

        SpringApplication.run(ShardingjdbcdemoApplication.class, args);

    }

}

测试类

@SpringBootTest

@Slf4j

class ShardingjdbcdemoApplicationTests {

    @Autowired

    private CourseMapper courseMapper;

    @SneakyThrows

    @Test

    void findCourse() {

        courseMapper.selectList(null).forEach(System.out::println);

    }

}

mybatis-plus也可以打印输出的sql日志，但是不是我们想要的效果，如何来控制想要的sql日志输出，可以使用P6Spy开源产品。

P6Spy入门使用

spy.properties

resources目录添加配置文件，类似log4j.xml，记录配置信息

module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory

# sql日志打印输出

# 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)

# 3、自定义日志打印(全限定类名)

logMessageFormat=cn.zysheep.config.P6SPYConfig

# 使用日志系统记录sql

appender=com.p6spy.engine.spy.appender.Slf4JLogger

## 配置记录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

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

P6SPYConfig

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);

    }

}

application.yml

spring:

  datasource:

    # driver-class-name: com.mysql.cj.jdbc.Driver

    driver-class-name: com.p6spy.engine.spy.P6SpyDriver

    type: com.alibaba.druid.pool.DruidDataSource

    #  url: jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8

    url: jdbc:p6spy:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8

    username: root

    password: root

# 打开mybatis-plus的sql日志输出

mybatis-plus:

  configuration:

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

测试类不变