一、基礎知識

**
 * @author wl
 * @description 單據生命流程日誌
 * @date 2019/10/9 21:06
 */
@Data
public class PayLifeLog implements Serializable {

    /**
     * 日誌主鍵編碼
     */
    private Integer logId;
    /**
     * 單據包編碼
     */
    private String packageId;
    /**
     * 單據編碼
     */
    private String billCode;
    /**
     * 節點執行結果
     */
    private Byte lifeStatus;
    /**
     * 節點
     */
    private Byte lifeState;
    /**
     * 備註
     */
    private String markMsg;
    /**
     * 更新時間
     */
    private LocalDateTime updatetime;
   }

package com.trace.base.tool.mybatis.study.plugin;

import com.mysql.jdbc.PreparedStatement;
import com.trace.base.tool.logging.BaseLog;
import com.trace.base.tool.logging.Channel;
import com.trace.base.tool.logging.LevelEnum;
import com.trace.base.tool.mybatis.monitor.SlowSqlEnum;
import com.trace.base.tool.mybatis.monitor.SlowSqlLog;
import com.trace.base.tool.util.LocalDateTimeUtil;
import com.zaxxer.hikari.pool.ProxyStatement;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.logging.jdbc.PreparedStatementLogger;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.postgresql.jdbc.PgStatement;

import java.lang.reflect.Field;
import java.lang.reflect.Proxy;
import java.sql.Statement;
import java.util.Properties;

/**
 * 監控慢SQL插件
 *
 * @author wl
 */
@Intercepts({
        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
        @Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class})
})
public class SlowSqlMonitorPlugin implements Interceptor {
    public static final String SLOW_SQL_ENABLE = "sql.slow.enable";
    private static boolean POSTGRESQL_DRIVER_AVAILABLE;
    private static boolean MYSQL_DRIVER_AVAILABLE;
    private static boolean HIKARICP_AVAILABLE;
    private static Field DELEGATE_FIELD;

    static {
        try {
            Class.forName("org.postgresql.jdbc.PgPreparedStatement");
            POSTGRESQL_DRIVER_AVAILABLE = true;
        } catch (ClassNotFoundException e) {
            // ignore
            POSTGRESQL_DRIVER_AVAILABLE = false;
        }
        try {
            Class.forName("com.mysql.jdbc.PreparedStatement");
            MYSQL_DRIVER_AVAILABLE = true;
        } catch (ClassNotFoundException e) {
            // ignore
            MYSQL_DRIVER_AVAILABLE = false;
        }
        try {
            Class.forName("com.zaxxer.hikari.pool.HikariProxyPreparedStatement");
            DELEGATE_FIELD = ProxyStatement.class.getDeclaredField("delegate");
            DELEGATE_FIELD.setAccessible(true);
            HIKARICP_AVAILABLE = true;
        } catch (ClassNotFoundException | NoSuchFieldException e) {
            // ignore
            HIKARICP_AVAILABLE = false;
        }
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long start = System.currentTimeMillis();
        try {
            Object obj = invocation.proceed();
            return obj;
        } finally {
            long end = System.currentTimeMillis();
            long used = end - start;
            // >= 1s
            final long max = 1000L;
            if (used >= max) {
                try {
                    Object target = invocation.getTarget();
                    String sql = "unknown";
                    if (target instanceof StatementHandler) {
                        sql = ((StatementHandler) target).getBoundSql().getSql();
                    }
                    // 外部提前做一次猜測是否為預處理語句,只用instanceof PreparedStatement有可能沒有?,這種情況不需要執行下面邏輯
                    boolean mightPreparedSql = sql.contains("?");
                    Object statementArg = invocation.getArgs()[0];
                    // 可能是預處理語句才處理
                    if (mightPreparedSql) {
                        // 這裡還要區分是否為debug模式,debug模式下,生成的connection和statement都是被mybatis logger類代理
                        if (Proxy.isProxyClass(statementArg.getClass())) {
                            // 獲取到真實被代理的statement
                            statementArg = ((PreparedStatementLogger) Proxy.getInvocationHandler(statementArg)).getPreparedStatement();
                        }
                        // 被HikariProxyPreparedStatement代理，通過反射才能獲取到真實的PreparedStatement
                        if (HIKARICP_AVAILABLE && statementArg instanceof ProxyStatement) {
                            java.sql.PreparedStatement preparedStatement = (java.sql.PreparedStatement) DELEGATE_FIELD.get(statementArg);
                            // postgresql,前提是SQL為預處理語句,避免非預處理語句也執行了toString()造成拿到記憶體地址
                            if (POSTGRESQL_DRIVER_AVAILABLE && preparedStatement instanceof PgStatement) {
                                // 因為PgPreparedStatement是保護類,只能使用PgStatement轉換,實際是執行子類的toString()
                                sql = preparedStatement.toString();
                            }
                            // mysql
                            else if (MYSQL_DRIVER_AVAILABLE && preparedStatement instanceof PreparedStatement) {
                                sql = ((PreparedStatement) preparedStatement).asSql();
                            }
                        }

                    }
                    // 記錄日誌資訊
                    SlowSqlLog slowSqlLog = new SlowSqlLog();
                    slowSqlLog.setTraceId("idnum-0001");
                    slowSqlLog.setType(SlowSqlEnum.DML);
                    slowSqlLog.setMessage("執行DML[" + sql + "]超時1秒");
                    slowSqlLog.setStart(LocalDateTimeUtil.formatMilliPlus8(start));
                    slowSqlLog.setEnd(LocalDateTimeUtil.formatMilliPlus8(end));
                    slowSqlLog.setUsed(used);
                    BaseLog<SlowSqlLog> baseLog = new BaseLog<>();
                    baseLog.setContext(slowSqlLog);
                    baseLog.setLevel(LevelEnum.WARNING.getLevel());
                    baseLog.setLevelName(LevelEnum.WARNING.getLevelName());
                    baseLog.setChannel(Channel.SYSTEM);
                    baseLog.setMessage("slowsql log");
                    baseLog.setDatetime(LocalDateTimeUtil.getMicroSecondFormattedNow());
                    String sqlLog=slowSqlLog.toString();
                    // todo 記錄日誌資訊
                } catch (Throwable ex) {
                    // ignore
                }
            }
        }
    }

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

    @Override
    public void setProperties(Properties properties) {
    }
}

package com.trace.base.tool.mybatis.study;


import com.trace.base.tool.mybatis.monitor.MonitorSpringManagedTransactionFactory;
import com.trace.base.tool.mybatis.study.plugin.PagePlugin;
import com.trace.base.tool.mybatis.study.plugin.SlowSqlMonitorPlugin;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.AutoMappingBehavior;
import org.apache.ibatis.session.Configuration;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.EnvironmentAware;
import org.springframework.core.env.Environment;


/**
 * 自定義的sqlSessionFactoryBean
 *
 * @author wl
 * @date 2021-3-9
 */
public class StudySqlSessionFactoryBean extends SqlSessionFactoryBean implements EnvironmentAware {
    private Interceptor[] plugins;
    public static Configuration CONFIGURATION;
    private boolean slowSqlEnabled = false;

    public StudySqlSessionFactoryBean() {
        this(null);
    }

    public StudySqlSessionFactoryBean(Configuration configuration) {
        super();
        if (configuration == null) {
            configuration = new Configuration();
            configuration.setMapUnderscoreToCamelCase(true);
            configuration.setAutoMappingBehavior(AutoMappingBehavior.FULL);
        }
        CONFIGURATION = configuration;
        setConfiguration(configuration);
    }

    @Override
    public void setPlugins(Interceptor[] plugins) {
        this.plugins = plugins;
    }

    /**
     * 真實執行設置插件,setPlugins只用於記錄客戶端自定義的plugin,便於後續拷貝
     */
    private void actualSetPlugins() {
        if (slowSqlEnabled) {
            // 使用自定義監控功能的事務管理器工廠類
            setTransactionFactory(new MonitorSpringManagedTransactionFactory());
            this.plugins = ArrayUtils.add(plugins == null ? new Interceptor[0] : plugins, new SlowSqlMonitorPlugin());
        }
        super.setPlugins(plugins);
    }

    @Override
    public void setEnvironment(Environment environment) {
        slowSqlEnabled = environment.getProperty(SlowSqlMonitorPlugin.SLOW_SQL_ENABLE, boolean.class, true);
        actualSetPlugins();
    }
}