如题,主要是借助p6spy的方式进行,可以打印出sql在db中运行时间,主要如下:
1.引入依赖
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.9.1</version>
</dependency>
2.修改springboot application文件DB配置
spring:
datasource:
#driver-class-name: com.mysql.cj.jdbc.Driver
#url: jdbc:mysql://127.0.0.1:3306/zll?useUnicode=true&characterEncoding=utf-8&serverTimezone=CTT
username: root
password: linqazse1
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
url: jdbc:p6spy:mysql://127.0.0.1:3306/zll?useUnicode=true&characterEncoding=utf-8&serverTimezone=CTT
3.引入p6spy配置文件spy.properties
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2
4.生成测试方法
@Test
public void testQueryPage(){
QueryWrapper<User> wrapper= new QueryWrapper<User>();
wrapper.ne("age",200);//.select(User.class,info -> !info.getColumn().equals("deleted"));
Page<User> page= new Page<User>(6,20,true);
IPage<User> iPage= userMapper.selectPage(page,wrapper);
System.out.println("总页数:"+iPage.getPages()+",总记录数:"+iPage.getTotal()+",当前页:"+iPage.getCurrent());
List<User> users= iPage.getRecords();
users.forEach(System.out::println);
}
5.运行log,可以看出sql实际运行时间
2022-10-10 22:13:34.811 DEBUG 20352 --- [ main] c.m.s.m.UserMapper.selectPage_mpCount : ==> Preparing: SELECT COUNT(*) AS total FROM user WHERE deleted = 0 AND (age <> ?)
2022-10-10 22:13:34.850 DEBUG 20352 --- [ main] c.m.s.m.UserMapper.selectPage_mpCount : ==> Parameters: 200(Integer)
Consume Time:16 ms 2022-10-10 22:13:34
Execute SQL:SELECT COUNT(*) AS total FROM user WHERE deleted = 0 AND (age <> 200)
2022-10-10 22:13:34.890 DEBUG 20352 --- [ main] c.m.s.m.UserMapper.selectPage_mpCount : <== Total: 1
2022-10-10 22:13:34.901 DEBUG 20352 --- [ main] c.m.s.mapper.UserMapper.selectPage : ==> Preparing: SELECT id,name,age,email,deleted,createtime,version FROM user WHERE deleted=0 AND (age <> ?) LIMIT ?,?
2022-10-10 22:13:34.902 DEBUG 20352 --- [ main] c.m.s.mapper.UserMapper.selectPage : ==> Parameters: 200(Integer), 100(Long), 20(Long)
Consume Time:1 ms 2022-10-10 22:13:34
Execute SQL:SELECT id,name,age,email,deleted,createtime,version FROM user WHERE deleted=0 AND (age <> 200) LIMIT 100,20
2022-10-10 22:13:34.921 DEBUG 20352 --- [ main] c.m.s.mapper.UserMapper.selectPage : <== Total: 16
总页数:6,总记录数:116,当前页:6
User(id=197, name=zouli197, age=227, email=zoulinlin197@163.com, deleted=0, createtime=null, version=0)
User(id=198, name=zouli198, age=228, email=zoulinlin198@163.com, deleted=0, createtime=null, version=0)
User(id=199, name=zouli199, age=229, email=zoulinlin199@163.com, deleted=0, createtime=null, version=0)