P6Spy是一個可以用來在應用程式中攔截和修改資料操作語句的開源框架。
透過P6Spy可以對SQL語句進行攔截,相當於一個SQL語句的記錄器,這樣我們就可以用它來做相關的分析,例如效能分析。
<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>
spring:
mybatis-plus:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
#driver-class-name: com.p6spy.engine.spy.P6SpyDriver
type: com.alibaba id. ://localhost:3306/course_db?serverTimezone=GMT+8
# url: jdbc:p6spy:mysql://localhost:3306/course_db?serverTimezone=GMT+8
root
# 開啟mybatis-plus的sql日誌輸出configuration:
entity
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
@Data @NoArgsConstructor @AllArgsConstructor @TableName("course_1") public class Course { @TableField("cid") private Long cId; private String cName; private Integer userId; private String cStatus; }
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入門使用
目錄新增設定文件,類似log4j .xml
,記錄設定資訊
## sql日誌列印輸出# 1、logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
#P6Spy有內建的SQL輸出格式,如上設定檔。這裡我們使用自訂SQL日誌列印
# 2、logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
# customLogMessageFormat=%(currentTime) | 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
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); } }
datasource:# driver-class-name : com.mysql.cj.jdbc.Driver
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
type: com.alibaba.druid.pool.DruidDataSource # type: com.alibaba.druid。 mysql://localhost:3306/course_db?serverTimezone=GMT+8
url: jdbc:p6spy:mysql://localhost:3306/course_db?serverTimezone=GMT+8
username: 代碼 root
# 開啟mybatis-plus的sql日誌輸出
mybatis-plus:
configuration:
測試類別不變
以上是SpringBoot怎麼整合P6Spy實作SQL日誌的詳細內容。更多資訊請關注PHP中文網其他相關文章!