将mybatis的sql存入数据库

众所周知，mybatis输出的sql日志中的参数是？，并不能直接使用；当我们去排查问题的时候还要去找日志，拼参数；如果参数多的时候就会很蛋疼了。于是我就在想能不能直接将sql直接存入数据库，并且能够直接使用，不用在去为拼接参数而头痛。
于是就有了以下的程序

package com.rskj.cashbook.handle;

import com.rskj.cashbook.service.iml.SqlMonitorLogService;
import com.rskj.cashbook.vo.SqlTxtMonitorLog;
import com.rskj.core.utils.SnowflakeIdWorker;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;

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

@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})
})
@Slf4j
@Component
public class SqlLogInterceptor implements Interceptor {

    List<String> IGNORE_SET = Arrays.asList("com.rskj.cashbook.dao.SqlTxtMonitorLogDao.insert","_COUNT","PageHelper");// 过滤掉不需要写入数据库的查询语句

    @Lazy
    @Autowired
    private SqlMonitorLogService sqlMonitorLogService;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object result = invocation.proceed();
        Object realTarget = realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(realTarget);
        MappedStatement ms = (MappedStatement) metaObject.getValue("delegate.mappedStatement");

        String sql = getSql(metaObject, ms);
        if (!IGNORE_SET.stream().anyMatch(ms.getId()::contains)) {
            sqlMonitorLogService.saveSqlLog((SqlTxtMonitorLog) new SqlTxtMonitorLog()
                    .setSqlText(sql)
                    .setMethodName(ms.getId())
                    .setId(SnowflakeIdWorker.getSnowId()
                    )
            );
        }
        return result;
    }

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

    @Override
    public void setProperties(Properties properties) {
    }

    @SuppressWarnings("unchecked")
    public <T> T realTarget(Object target) {
        if (Proxy.isProxyClass(target.getClass())) {
            MetaObject metaObject = SystemMetaObject.forObject(target);
            return realTarget(metaObject.getValue("h.target"));
        }
        return (T) target;
    }

    private String getSql(MetaObject metaObject, MappedStatement ms) {
        BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
        String sql = boundSql.getSql();
        Object parameterObject = boundSql.getParameterObject();

        if (parameterObject == null) {
            return sql;
        }
        // foreach 处理
        if (parameterObject instanceof Map<?, ?> paramMap) {
            Object value = paramMap.get("param1");
            if (value instanceof List<?> list) {
                for (Object o : list) {
                    sql = buildSql(boundSql, o, sql);
                }
                return sql;
            }
        }
        return buildSql(boundSql, parameterObject, sql);
    }

    private String buildSql(BoundSql boundSql, Object parameterObject, String sql) {
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        for (ParameterMapping parameterMapping : parameterMappings) {
            String parameterName = parameterMapping.getProperty();
            parameterName = parameterName.substring(parameterName.lastIndexOf(".") + 1);
            Object parameterValue = null; // 获取参数的值
            if (IGNORE_SET.stream().anyMatch(parameterName::contains)){
                continue;
            }
            try {
                Field field = getFieldFromSuperclass(parameterObject.getClass(), parameterName);
                field.setAccessible(true);
                parameterValue = field.get(parameterObject);
            } catch (Exception e) {
                log.error("Error getting parameter value: {}", e.getMessage());
            }
            sql = sql.replaceFirst("\\?", parameterValue != null ? parameterValue.toString() : "null");
        }
        return sql.replace("LIMIT ? OFFSET ?","LIMIT 1 OFFSET 10");
    }

    private Field getFieldFromSuperclass(Class<?> clazz, String fieldName) throws NoSuchFieldException {
        while (clazz != null) {
            try {
                return clazz.getDeclaredField(fieldName);
            } catch (NoSuchFieldException e) {
                clazz = clazz.getSuperclass();
            }
        }
        throw new NoSuchFieldException("Field " + fieldName + " not found in class or its superclasses");
    }
}