mybatis-plus3.5.10.x版本使用p6spy打印sql

MyBatis-Plus 3.5.10.x用P6Spy打印SQL

原创已于 2025-04-06 23:11:13 修改 · 308 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #sql #java

于 2025-04-06 23:04:01 首次发布

mybatis-plus3.5.10.x版本使用p6spy打印sql

1、导入依赖

     <!-- https://mvnrepository.com/artifact/p6spy/p6spy -->
        <dependency>
            <groupId>com.github.gavlyukovskiy</groupId>
            <artifactId>p6spy-spring-boot-starter</artifactId>
            <version>1.10.0</version>
        </dependency>

2、配置数据库连接

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.url=jdbc:p6spy:mysql://172.16.211.95:3306/hntt-dev?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=hnttdev

3、配置文件 P6SPYConfig

import com.alibaba.fastjson2.JSONObject;
import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import org.apache.commons.lang3.StringUtils;
import org.springframework.context.annotation.Configuration;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;

@Configuration
public class P6SPYConfig  implements MessageFormattingStrategy {

    private final 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);

    }
}

其他没有了，注意 spring.datasource.url=jdbc:p6spy:mysql://xxx 要加上jdbc:p6spy 很多人就是遗漏了这里