【SpringBoot应用篇】SpringBoot集成P6Spy记录SQL日志
P6Spy简介
P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架。
通过P6Spy可以对SQL语句进行拦截,相当于一个SQL语句的记录器,这样我们可以用它来作相关的分析,比如性能分析。
我们最需要的功能,查看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
@Slf4j
class 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 {
private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
/**
* 自定义sql日志打印
*
* @param connectionId 连接标识
* @param now 执行时间
* @param elapsed 执行秒数ms
* @param category statement
* @param prepared 预编译sql语句
* @param sql 真实的sql语句
* @param url 数据库url连接
* @return {@link String}
*/
@Override
public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
Map<String, Object> message = new LinkedHashMap<>(4);
if (StringUtils.isNoneBlank(prepared, sql) ) {
message.put("SQL execute time:", this.format.format(new Date()));
message.put("SQL explain time:", elapsed + "ms");
String newPrepared = prepared.replace(" ", "").replace("\n", " ");
message.put("SQL prepared:", newPrepared);
String newSql = sql.replace(" ", "").replace("\n", " ");
message.put("SQL real:", newSql);
}
return JSONObject.toJSONString(message.isEmpty() ? "" : 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
测试类不变
Oracle使用
1、spy.properties修改
# 实际驱动
driverlist=oracle.jdbc.driver.OracleDriver
2、application.yml修改
jdbc.driverClassName=com.p6spy.engine.spy.P6SpyDriver
jdbc.url=jdbc:p6spy:oracle:thin:@27.0.0.1:1521:orcl