使用mybatis的statementHander拦截器监控表和字段并发送钉钉消息

新建mybatis的statementHander拦截器拦截器类

面试题：

2.实现

解析Sql时引入JSqlParser

JSqlParser 是一个 SQL 语句解析器。它将 SQL转换为可遍历的 Java 类层次结构。

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.6</version>

</dependency>

添加拦截器代码：

package com.yy.config;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.http.HttpRequest;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import cn.smart.model.LocalUser;
import cn.smart.util.ThreadlocalUtil;
import com.yy.entity.BaseEntity;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.springframework.stereotype.Component;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
@Intercepts({
        // 指定要拦截的方法签名，这里是拦截Executor的update方法
        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
        // 可以添加更多要拦截的方法签名...
})
public class StatementHanderInterceptor implements Interceptor {

    private static List<String> tables = CollUtil.newArrayList("product","category");
    private static Map<String,List<String>> tableColumns = new HashMap<>();

    static {

        tableColumns.put("product",CollUtil.newArrayList("name","price"));
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler =(StatementHandler)invocation.getTarget();
        //要想监控表，首先要从拦截器中拿到sql语句，看sql语句干了什么，对那些表和那些字段做了什么
        BoundSql boundSql = statementHandler.getBoundSql();
        String sql = boundSql.getSql();
//        使用JSqlParser解析器解析sql语句
        net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sql);
        if(statement instanceof Update){
            Update update = (Update)statement;
            String name = update.getTable().getName();
            if(tables.contains(name)){
                List<String> updataSets = tableColumns.get(name);
                ArrayList<UpdateSet> updateSets = update.getUpdateSets();
                for (UpdateSet updateSet : updateSets) {
                    String columnName = updateSet.getColumns().get(0).getColumnName();
                    if(updataSets.contains(columnName)){
                        sendMessage(   "修改了字段"+updateSet,name);
                    }
                }
            }
         }
        else if(statement instanceof Insert){
            Table table = ((Insert) statement).getTable();
            String name = table.getName();
            if(tables.contains(name)){
                sendMessage("添加了数据",name);
            }
        }
        return invocation.proceed();
    }
   private void sendMessage(String option,String tableName){
        String url= "https://oapi.dingtalk.com/robot/send?access_token=1cfb9a7b20e849a26b572a8ff98f62ee2a220028cd1452901a486e131435621f";
       JSONArray array = new JSONArray();
       array.add("18236435312");

       JSONObject msg = new JSONObject();
       msg.set("msgtype","text");
       msg.set("text",new JSONObject().set("content","警告："+ThreadlocalUtil.get().getNickName()+"-> "+option+" ，表名是："+tableName));
       msg.set("at",new JSONObject().set("atMobiles",array));
       String json = JSONUtil.toJsonStr(msg);
       String body = HttpRequest.post(url).body(json).execute().body();
   }

}

遇到的问题：

因为这个pagehelper是从stater中继承过来的，可以在stater中也加一个4.6版本的jsqlparser依赖