使用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"));}@Overridepublic 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依赖