采用mybatis plus拦截器记录慢sql日志

pang.jiabao

2024-10-28 df56f634de797d1f3d44fc78c114a8a3d7880c21

采用mybatis plus拦截器记录慢sql日志

 pom.xml

@@ -108,10 +108,6 @@
            <artifactId>truelicense-core</artifactId>
            <version>1.33</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
    </dependencies>

    <build>

 src/main/java/com/zy/common/config/MyBatisPlusConfig.java

New file
@@ -0,0 +1,20 @@
package com.zy.common.config;

/**
 * @author pang.jiabao
 * @description MyBaits plus sql拦截器配置
 * @createDate 2024/10/25 16:18
 */
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("src.resources.mapper") // 替换为你的Mapper包路径
public class MyBatisPlusConfig {

    @Bean
    public SlowSqlInterceptor slowSqlInterceptor() {
        return new SlowSqlInterceptor();
    }
}

 src/main/java/com/zy/common/config/SlowSqlInterceptor.java

New file
@@ -0,0 +1,53 @@
package com.zy.common.config;

/**
 * @author pang.jiabao
 * @description 记录慢sql日志
 * @createDate 2024/10/25 16:18
 */
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Properties;

@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class SlowSqlInterceptor implements Interceptor {

    private static final Logger logger = LoggerFactory.getLogger(SlowSqlInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        String sqlId = ms.getId(); // 获取 SQL ID
        // 获取 SQL 语句，可以用 sqlSource
        String sql = ms.getBoundSql(invocation.getArgs()[1]).getSql(); // 获取具体的 SQL 语句

        long startTime = System.currentTimeMillis();
        Object result = invocation.proceed();
        long duration = System.currentTimeMillis() - startTime;

        if (duration > 500) { // 阈值
            logger.warn("慢SQL: {}，执行时间: {}毫秒，具体SQL: {}", sqlId, duration, sql);
        }

        return result;
    }

    @Override
    public Object plugin(Object o) {
        return Plugin.wrap(o, this);
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

 src/main/java/com/zy/common/config/SqlExecutionTimeAspect.java

File was deleted

 src/main/resources/logback-spring.xml

@@ -53,10 +53,29 @@
            <charset>UTF-8</charset>
        </encoder>
    </appender>
    <!--waring级别-->
    <appender name="WARN_FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
            <level>WARN</level>
        </filter>
        <file>${LOG_PATH}/warn.log</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <FileNamePattern>${LOG_PATH}/warn_%d{yyyy-MM-dd}.%i.log</FileNamePattern>
            <maxHistory>10</maxHistory>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>10MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
        </rollingPolicy>
        <encoder>
            <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{35} - %msg %n</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <root level="INFO">
        <appender-ref ref="CONSOLE"/>
        <appender-ref ref="INFO_FILE"/>
        <appender-ref ref="ERROR_FILE"/>
        <appender-ref ref="WARN_FILE"/>
    </root>
</configuration>