P6Spy使用

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

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

springboot集成P6Spy

1.添加依赖

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

2.修改配置

原来的

spring.jpa.database=h2
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.open-in-view=false
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:./data/test
spring.datasource.username=sa
spring.datasource.password=123456

修改为

spring.jpa.database=h2
spring.jpa.hibernate.ddl-auto=none
spring.jpa.open-in-view=false
spring.datasource.driverClassName=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.url=jdbc:p6spy:h2:./data/test
spring.datasource.username=sa
spring.datasource.password=123456

3.P6Spy的配置

添加配置文件 spy.properties

# 单行日志
logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
# 使用Slf4J记录sql
appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准，单位秒
outagedetectioninterval=2
#日期格式
dateformat=yyyy-MM-dd HH:mm:ss

更多配置查看 https://p6spy.readthedocs.io/en/latest/configandusage.html

schema.sql

drop table users if exists;
drop table goods if exists;
create table users (
    id bigint auto_increment,
    name varchar(255),
    create_time timestamp,
    primary key (id)
);
create table goods (
  id bigint auto_increment,
  name varchar(255),
  price bigint,
  create_time timestamp,
  update_time timestamp,
  primary key (id)
);

data.sql

insert into users (name, create_time) values ('Lili', now());
insert into users (name, create_time) values ('Fiona', now());
insert into goods (name, price, create_time, update_time) values ('bag', 2000, now(), now());
insert into goods (name, price, create_time, update_time) values ('bottole', 2500, now(), now());

启动项目

控制台输出p6spy的部分

2020-04-01 10:05:53.193 INFO 1064 --- [ main] p6spy : 2020-04-01 10:05:53|4|statement|connection 0|url jdbc:p6spy:h2:./data/test|drop table users if exists|drop table users if exists
2020-04-01 10:05:53.194 INFO 1064 --- [ main] p6spy : 2020-04-01 10:05:53|0|statement|connection 0|url jdbc:p6spy:h2:./data/test|drop table goods if exists|drop table goods if exists
2020-04-01 10:05:53.198 INFO 1064 --- [ main] p6spy : 2020-04-01 10:05:53|3|statement|connection 0|url jdbc:p6spy:h2:./data/test|create table users ( id bigint auto_increment, name varchar(255), create_time timestamp, primary key (id) )|create table users ( id bigint auto_increment, name varchar(255), create_time timestamp, primary key (id) )
2020-04-01 10:05:53.199 INFO 1064 --- [ main] p6spy : 2020-04-01 10:05:53|0|statement|connection 0|url jdbc:p6spy:h2:./data/test|create table goods ( id bigint auto_increment, name varchar(255), price bigint, create_time timestamp, update_time timestamp, primary key (id) )|create table goods ( id bigint auto_increment, name varchar(255), price bigint, create_time timestamp, update_time timestamp, primary key (id) )
2020-04-01 10:05:53.216 INFO 1064 --- [ main] p6spy : 2020-04-01 10:05:53|8|statement|connection 0|url jdbc:p6spy:h2:./data/test|insert into users (name, create_time) values ('Lili', now())|insert into users (name, create_time) values ('Lili', now())
2020-04-01 10:05:53.216 INFO 1064 --- [ main] p6spy : 2020-04-01 10:05:53|0|statement|connection 0|url jdbc:p6spy:h2:./data/test|insert into users (name, create_time) values ('Fiona', now())|insert into users (name, create_time) values ('Fiona', now())
2020-04-01 10:05:53.217 INFO 1064 --- [ main] p6spy : 2020-04-01 10:05:53|0|statement|connection 0|url jdbc:p6spy:h2:./data/test|insert into goods (name, price, create_time, update_time) values ('bag', 2000, now(), now())|insert into goods (name, price, create_time, update_time) values ('bag', 2000, now(), now())
2020-04-01 10:05:53.217 INFO 1064 --- [ main] p6spy : 2020-04-01 10:05:53|0|statement|connection 0|url jdbc:p6spy:h2:./data/test|insert into goods (name, price, create_time, update_time) values ('bottole', 2500, now(), now())|insert into goods (name, price, create_time, update_time) values ('bottole', 2500, now(), now())

这样在运行项目的时就可以在控制台中看到具体的sql语句了，从而检查没有错误

注：

　　p6spy配合slf4j+log4j使用，项目中需要整合logback