springboot 使用 p6spy+h2 测试sql数据

引入依赖

	<dependency>
		  <groupId>p6spy</groupId>
      <artifactId>p6spy</artifactId>
      <version>3.9.1</version>
  </dependency>
  <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <scope>runtime</scope>
  </dependency>

spring 配置

spring:
  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    schema: classpath:db/schema-h2.sql
    data: classpath:db/data-h2.sql
    url: jdbc:p6spy:h2:mem:test
    username: root
    password: test

p6spy 设置 spy.properties

# 指定应用的日志拦截模块,默认为com.p6spy.engine.spy.P6SpyFactory
# MybatisPlusLogFactory P6OutageFactory
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印 P6SpyLogger
logMessageFormat=cn.example.mybatisplus.p6spy.P6SpyLogger
#日志输出到控制台 StdoutLogger、使用日志系统记录 Slf4JLogger
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger

# 设置 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

重写 P6SpyLogger 自定义控制台打印格式

public class P6SpyLogger implements MessageFormattingStrategy {
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category,
                                String prepared, String sql, String url) {
        return StringUtils.isNotBlank(sql.trim()) ?
                "Consume Time: " + elapsed + " ms " + now +
                        "\nExecute SQL：" + sql.replaceAll("[\\s]+", " ") + ";\n"
                : "";
    }
}

在 resources 目录下准备 DB 数据

schema-h2.sql

DROP TABLE IF EXISTS sys_user;

CREATE TABLE sys_user
(
    id    BIGINT(20) NOT NULL COMMENT '主键ID',
    name  VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
    age   INT(11) NULL DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (id)
);

data-h2.sql

DELETE FROM sys_user;

INSERT INTO sys_user (id, name, age, email)
VALUES (1, 'Jone', 18, 'test1@baomidou.com'),
       (2, 'Jack', 20, 'test2@baomidou.com'),
       (3, 'Tom', 28, 'test3@baomidou.com'),
       (4, 'Sandy', 21, 'test4@baomidou.com'),
       (5, 'Billie', 24, 'test5@baomidou.com');

启动项目

2022-07-22 13:28:47.490  INFO 12748 --- [  restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/console'. Database available at 'jdbc:h2:mem:test'
Consume Time: 1 ms 2022-07-22 13:28:47.902
Execute SQL：DROP TABLE IF EXISTS sys_user;

Consume Time: 4 ms 2022-07-22 13:28:47.909
Execute SQL：CREATE TABLE sys_user ( id BIGINT(20) NOT NULL COMMENT '主键ID', name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', age INT(11) NULL DEFAULT NULL COMMENT '年龄', email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (id) );

Consume Time: 1 ms 2022-07-22 13:28:47.911
Execute SQL：DELETE FROM sys_user;

Consume Time: 1 ms 2022-07-22 13:28:47.913
Execute SQL：INSERT INTO sys_user (id, name, age, email) VALUES (1, 'Jone', 18, 'test1@baomidou.com'), (2, 'Jack', 20, 'test2@baomidou.com'), (3, 'Tom', 28, 'test3@baomidou.com'), (4, 'Sandy', 21, 'test4@baomidou.com'), (5, 'Billie', 24, 'test5@baomidou.com');

业务层代码

	public R getUser() {
        SysUser sysUser = baseMapper.selectById(1L);
        return R.success().put("data", sysUser);
    }

http://127.0.0.1:9813/getUser

{
    "code": 200,
    "data": {
        "id": 1,
        "name": "Jone",
        "age": 18,
        "email": "test1@baomidou.com"
    },
    "message": "success"
}