P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架。
通过P6Spy可以对SQL语句进行拦截，相当于一个SQL语句的记录器，这样我们可以用它来作相关的分析，比如性能分析。
springboot集成P6Spy
1.添加依赖
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 exists2020-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 exists2020-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
好文阅读
发表评论