本篇内容介绍了“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@Slf4jclass 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
测试类不变
“SpringBoot如何集成P6Spy实现SQL日志”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站，小编将为大家输出更多高质量的实用文章！
