P6Spy简介

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

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

我们最需要的功能，查看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 { 
    private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS"); / * 自定义sql日志打印 * * @param connectionId 连接标识 * @param now 执行时间 * @param elapsed 执行秒数ms * @param category statement * @param prepared 预编译sql语句 * @param sql 真实的sql语句 * @param url 数据库url连接 * @return {@link String} */ @Override public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) { 
    Map<String, Object> message = new LinkedHashMap<>(4); if (StringUtils.isNoneBlank(prepared, sql) ) { 
    message.put("SQL execute time:", this.format.format(new Date())); message.put("SQL explain time:", elapsed + "ms"); String newPrepared = prepared.replace(" ", "").replace("\n", " "); message.put("SQL prepared:", newPrepared); String newSql = sql.replace(" ", "").replace("\n", " "); message.put("SQL real:", newSql); } return JSONObject.toJSONString(message.isEmpty() ? "" : 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 

测试类不变

Oracle使用

1、spy.properties修改

# 实际驱动 driverlist=oracle.jdbc.driver.OracleDriver 

2、application.yml修改

jdbc.driverClassName=com.p6spy.engine.spy.P6SpyDriver jdbc.url=jdbc:p6spy:oracle:thin:@27.0.0.1:1521:orcl