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.ymlspring:
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;}
mapperpublic 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.propertiesresources目录添加配置文件，类似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日志打印
p6spyconfigpublic 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.ymlspring:
  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日志的详细内容。