慢SQL拦截提示器

记录一个java的慢sql拦截器

package com.jzjf.interceptor;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * @Author: zhufawen
 * @Date: 2024/8/15 10:00
 */

@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),
        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
        @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})})
@Component
@Slf4j
public class SqlExecuteTimeInterceptor implements Interceptor {

    @Autowired
    private YourService service;

    // 超时时间 毫秒
    private final static Long timeout = 100000L;

    /**
     * @Description sql拦截器
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object target = invocation.getTarget();
        long begin = System.currentTimeMillis();
        StatementHandler statementHandler = (StatementHandler) target;
        try {
            return invocation.proceed();
        } finally {
            long end = System.currentTimeMillis();
            // 判断超时
            if ((end - begin) > timeout) {
                // 方法名称、类型
                MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
                MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
                String methodName = mappedStatement.getId();
                String sqlType = mappedStatement.getSqlCommandType().toString();
                BoundSql boundSql = statementHandler.getBoundSql();
                String sql = boundSql.getSql();
                // 参数map
                Object parameterObject = boundSql.getParameterObject();
                // 参数列表
                List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
                // 格式化sql语句，去除换行符，替换参数
                sql = formatSQL(sql, parameterObject, parameterMappingList);
                // 转换参数map为String
                String params = changeSqlMapParams(parameterObject, parameterMappingList);
                log.error("执行 SQL：[ {} ],方法：[ {} ], 类型: [ {} ], 参数：[ {} ], 执行耗时[ {} ms ]", sql, methodName, sqlType, params, (end - begin));

                RequestPayloadDTO requestPayloadDTO = new RequestPayloadDTO();
                Map<String, String> contentMap = new HashMap<>();
                contentMap.put("Content-Type", "application/json");
                requestPayloadDTO.setHeaderParamMap(contentMap);
                Map<String, Object> json = new HashMap<>();
                Map<String, Object> text = new HashMap<>();
                json.put("msg_type", "text");
                text.put("text", "执行 SQL：[ "+sql+" ],方法：[ "+methodName+" ], 类型: [ "+sqlType+" ], 参数：[ "+params+" ], 执行耗时[ "+(end - begin)+" ms ]");
                json.put("content", text);
                service.dosomething(json);
            }
        }
    }

    private String changeSqlMapParams(Object parameterObject, List<ParameterMapping> parameterMappingList) {
        StringBuilder sb = new StringBuilder();
        Map<String, Object> params = (Map<String, Object>) parameterObject;
        for (ParameterMapping pm : parameterMappingList) {
            if (pm.getMode().name().equals("IN")) {
                String param = ",[" + params.get(pm.getProperty()).toString() + "]";
                sb.append(param);
            }
        }
        return sb.length() > 0 ? sb.substring(1) : null;
    }

    private String formatSQL(String sql, Object parameterObject, List<ParameterMapping> parameterMappingList) {
        if (sql == null || sql.length() == 0) {
            return "";
        }
        // 去除换行符
        sql = sql.replaceAll("[\s\n ]+", "  ");
        // 替换参数
        Map<String, Object> params = (Map<String, Object>) parameterObject;
        for (ParameterMapping pm : parameterMappingList) {
            if (pm.getMode().name().equals("IN")) {
                sql = sql.replaceFirst("\?", params.get(pm.getProperty()).toString());
            }
        }
        return sql;
    }

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

    @Override
    public void setProperties(Properties properties) {

    }
}