mybatis插件配合SpringMVC攔截器實現(xiàn)操作日志統(tǒng)計

在Mybatis的攔截器中，只能統(tǒng)計出最終執(zhí)行的Sql語句，無法統(tǒng)計出每行語句執(zhí)行的操作人。


如果想看一個用戶主動對數(shù)據(jù)庫的操作日志，則單使用攔截器無法實現(xiàn)。

可以借助SpringMvc的攔截器，將請求頭的信息記錄下來，這樣就能獲取到每一個人的操作日志。

新建一個 MyBatisIntercept 類，繼承 HandlerInterceptorAdapter 攔截器 并 實現(xiàn) Mybatis的Interceptor接口
攔截Update和Query操作


@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 MyBatisIntercept extends HandlerInterceptorAdapter implements Interceptor {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {

        return true;
    }
    @Override
    public Object intercept(Invocation invocation) throws Throwable {

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

    @Override
    public void setProperties(Properties properties) {

    }
}

創(chuàng)建一個全局ThreadLocal對象，用于存儲用SpringMvc攔截器進行來的用戶身份信息


    private ThreadLocal<Object> threadLocal = new InheritableThreadLocal<>();

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        // 將請求中可以標識用戶信息的數(shù)據(jù)給塞進去
        objectThreadLocal.set("");
        return true;
    }

如果是同步操作的話，SpringMvc的攔截器和Mybatis的攔截器必然會在一個線程里面。

在攔截器中將用戶信息給取出來，然后處理一下Mybatis的Sql語句，這樣就能對整個語句進行一個操作人的記錄。


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 能將先前存儲的用戶信息給獲取出來
        threadLocal.get();
    }

這樣的方式用于記錄操作人是可行的，但是這種方式會使Mybatis攔截器的職責不明確，需要去處理請求里面的內容。

如果有使用日志框架，可以使用MDC對象，MDC對象對ThreadLocal進行了一個優(yōu)化，可以將request中的信息保存到MDC對象中，
然后配置logback的配置文件，直接將日志通過mq的方式進行存儲處理。

最后成了這樣：


/**
 * @author : 小咖啡
 * @create : 2018-01-08 10:29
 * mybatis 操作攔截器
 * sql直接拷貝 http://phncz310.iteye.com/blog/2251712
 */
@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 MyBatisIntercept extends HandlerInterceptorAdapter implements Interceptor {

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

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        MDC.put("operationType", request.getHeader("operationType"));
        return true;
    }


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        // 傳入的對象
        Object obj = args[1];

        MappedStatement mappedStatement = (MappedStatement) args[0];
        // 記錄執(zhí)行結果
        Object resultObj = invocation.proceed();
        String name = mappedStatement.getSqlCommandType().name().toUpperCase();
        //執(zhí)行的sql
        BoundSql boundSql = mappedStatement.getBoundSql(obj);
        Configuration configuration = mappedStatement.getConfiguration();
        String sql;
        try {
            sql = showSql(configuration, boundSql);
        }catch (Exception e){
            sql = "SQL分析出錯";
            logger.warn("SQL分析出錯 {}",JSONObject.toJSONString(resultObj));
            return resultObj;
        }
        if (name.startsWith("INSERT")) {
            logger.info("{}||{}", sql, sql.substring(sql.toUpperCase().indexOf("INTO") + 4, sql.toUpperCase().indexOf("(")).trim());
        }
        if (name.startsWith("UPDATE")) {
            // 找where和limit中的參數(shù)就是條件
            String keywords = sql.substring(sql.toUpperCase().lastIndexOf("WHERE")).toUpperCase();
            if (keywords.contains("LIMIT")) {
                keywords = keywords.substring("WHERE".length(), sql.toUpperCase().lastIndexOf("LIMIT"));
            }
            StringBuilder sb = new StringBuilder();
            for (String key : keywords.split("AND")) {
                sb.append(key.split("=")[1].trim()).append(",");
            }
            logger.info("{}||{}||{}", sql, sql.substring(name.length(), sql.toUpperCase().lastIndexOf("SET")).trim(), sb.toString());
        }
        if (name.startsWith("DELETE")) {
            String keywords = sql.substring(sql.toUpperCase().lastIndexOf("WHERE")).toUpperCase();
            if (keywords.contains("LIMIT")) {
                keywords = keywords.substring("WHERE".length(), sql.toUpperCase().lastIndexOf("LIMIT"));
            }
            StringBuilder sb = new StringBuilder();
            for (String key : keywords.split("AND")) {
                sb.append(key.split("=")[1].trim()).append(",");
            }
            logger.info("{}||{}||{}", sql, sql.substring(sql.toUpperCase().lastIndexOf("FROM"), sql.toUpperCase().lastIndexOf("WHERE")).trim(), sb.toString());
        }
        if (name.startsWith("SELECT")) {
            logger.info("查詢結果 -> {} , {}", sql, JSONObject.toJSONString(resultObj));
        }
        return resultObj;
    }

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

    @Override
    public void setProperties(Properties properties) {

    }

    private static String getParameterValue(Object obj) {
        String value = null;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(new Date()) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }

        }
        return value;
    }

    public static String showSql(Configuration configuration, BoundSql boundSql) {
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (parameterMappings.size() > 0 && parameterObject != null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
            } else {
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else {
                        Map map = (Map) metaObject;
                        sql = sql.replaceFirst("\\?", getParameterValue(map.get(propertyName)));
                    }
                }
            }
        }
        return sql;
    }
    private String camelToUnderline(String param){
        if (param==null||"".equals(param.trim())){
            return "";
        }
        int len=param.length();
        StringBuilder sb=new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c=param.charAt(i);
            if (Character.isUpperCase(c)){
                sb.append("_");
                sb.append(Character.toLowerCase(c));
            }else{
                sb.append(c);
            }
        }
        return sb.toString();
    }

}

logback的配置文件：


    <springProperty  name="host" source="spring.rabbitmq.host"/>
    <springProperty  name="username" source="spring.rabbitmq.username"/>
    <springProperty  name="password" source="spring.rabbitmq.password"/>

    <appender name="AMQP" class="org.springframework.amqp.rabbit.logback.AmqpAppender">
        <layout>
            <pattern>%X{operationType}||%X{operationId}||%X{X-B3-TraceId}||%X{X-B3-SpanId}||%m%n</pattern>
        </layout>
        <filter class="ch.qos.logback.core.filter.EvaluatorFilter">
            <evaluator>
                <!--判斷操作人不為空-->
                <expression>
                    mdc.get("operationType") != null
                </expression>
            </evaluator>
            <OnMatch>ACCEPT</OnMatch>
            <OnMismatch>DENY</OnMismatch>
        </filter>
        <host>${host}</host>
        <port>5672</port>
        <username>${username}</username>
        <password>${password}</password>
        <applicationId>AmqpAppenderTest</applicationId>
        <generateId>true</generateId>
        <exchangeName>operationWithParamDestination</exchangeName>
        <charset>UTF-8</charset>
        <durable>false</durable>
        <deliveryMode>NON_PERSISTENT</deliveryMode>
    </appender>
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容