拿来即用的MyBatis Plugin实现SQL语句结构动态新增/更改字段（线上运行中）

一、前言

前一段时间在公司写了一个链路追踪的服务，需要把用户的信息和服务链路的调用信息持久化到业务表，然后使用canal + binlog的方式做日志审计。其中在将用户的信息和服务链路的调用信息持久化到业务表时，采用自定义Mybatis Plugin的方式实现多业务模块通用的拦截落库。

关于MyBatis Plugin的实现原理，参考我以下的三个博文：

1、从JDK动态代理一步步推导到MyBatis Plugin插件实现原理
2、原来MyBatis插件/拦截器（Plugin/Interceptyor）的实现原理这么简单
3、四种方式使通用SDK中自定义Mybatis Plugin生效?

废话不多说，我们直接上代码，完整源码（服务调用链路trace追踪–最终持久化到业务表（此处可按需求调整持久化的地方））已脱敏放在博主的GitHub上：https://github.com/Saint9768/mybatis-dynamic-plugin。

二、代码

jsqlparser的maven依赖如下：

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>3.2</version>
    <optional>true</optional>
</dependency>

代码中主要使用jsqlparser对SQL语句的结构进行动态修改；下列代码的核心逻辑是：

1. 针对insert和update类型语句进行SQL结构的动态调整；包括：
2. insert语句插入一些字段时，如果trace需要的字段在原本的insert语句中已经存在，则将原insert语句中对应的字段–value值修改为trace中的值，否者新增trace中的字段和相应的trace中的value值。
3. update语句更新一些字段时，和insert语句类似。不过update语句中可以存在多个相同的字段，真正更新的值以最后的一个为准。

最后采用兜底措施，对所有的拦截器逻辑进行try{}…catch{}操作，防止拦截器出现不正常问题；有一个点需要注意，必须要先修改业务表，增加trace中的字段，否者try{}…catch{}是没有用的；大家可能会问，为什么不在拦截器中直接写DDL语句修改表结构啊？这里是出于拦截器的性能考虑，尽可能的让拦截器以最少的时间损耗做最更通用的事情。

有一个点就很坑，由于新老项目中可能原本就使用到了jsqlparser，并且使用的版本差异有很大，所以我做SDK也写了两套兼容他们，虽然我已经在SDK对jsqlparser的maven依赖做了<optional>true</optional>标注，但由于版本原因，业务上引入不同版本的jsqlparser还是会报错。

1> 在ThreadLocal中保存trace信息，进而持久到业务表：

package com.saint.constant;

import lombok.Data;
import lombok.experimental.Accessors;

import java.io.Serializable;

/**
 * 链路信息上下文
 *
 * @author Saint
 */
public class MybatisTraceContext implements Serializable {

    private final static InheritableThreadLocal<TraceContext> traceContextHolder = new InheritableThreadLocal<>();

    public static InheritableThreadLocal<TraceContext> get() {
        return traceContextHolder;
    }

    /**
     * 设置traceContext
     *
     * @param traceContext traceContext
     */
    public static void setTraceContext(TraceContext traceContext) {
        traceContextHolder.set(traceContext);
    }

    /**
     * 获取traceContext
     *
     * @return traceContext
     */
    public static TraceContext getTraceContext() {
        return traceContextHolder.get();
    }

    /**
     * 清空trace上下文
     */
    public static void clear() {
        traceContextHolder.remove();
    }

    @Data
    @Accessors(chain = true)
    public static class TraceContext implements Serializable {
        private Long userId;
        private String traceId;
        private String controllerAction;
        private String visitIp;
        private String appName;
    }
}

2> MyBatis拦截器中使用到的常量：

/**
 * MyBatis拦截器中使用到的常量
 *
 * @author Saint
 */
@Getter
public enum MyBatisPluginConst {

    /**
     * 在这里修改业务表字段名
     */
     .......
    DELEGATE_BOUND_SQL("delegate.boundSql.sql"),
    BOUND_SQL("boundSql"),
    DELEGATE_MAPPED_STATEMENT("delegate.mappedStatement"),
    MAPPED_STATEMENT("mappedStatement"),
    METHOD_PREPARE("prepare"),
    METHOD_SET_PARAMETERS("setParameters");

    private String vale;

    MyBatisPluginConst(String vale) {
        this.vale = vale;
    }
}

3> MyBatis插件工具类：获取真正的委托类（目标类）：

public final class MyBatisPluginUtils {

    /**
     * 获取真正的委托类（目标类），由于可能存在多层代理，所以采用递归方式
     */
    @SuppressWarnings("unchecked")
    public static <T> T realTarget(Object target) {
        if (Proxy.isProxyClass(target.getClass())) {
            MetaObject metaObject = SystemMetaObject.forObject(target);
            return realTarget(metaObject.getValue("h.target"));
        }
        return (T) target;
    }
}

1、使用jsqlparser高版本（例如3.2）的自定义MyBatis plugin

这里做了很多mock操作，把原本业务上的注释掉了，采用mock字段名和字段值的方式；

1. insert时，就判断业务表上有没有user_name字段，有就修改值，否者就添加列和字段值。
2. update时，和insert时一样的逻辑，列存在就修改值，否则添加列和字段值。

package com.saint.mybatis;

import com.saint.constant.MyBatisPluginConst;
import com.saint.utils.MyBatisPluginUtils;
import com.saint.utils.StringUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ItemsListVisitor;
import net.sf.jsqlparser.expression.operators.relational.MultiExpressionList;
import net.sf.jsqlparser.expression.operators.relational.NamedExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.values.ValuesStatement;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.springframework.beans.factory.annotation.Value;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.*;

/**
 * MyBatis拦截器；
 *
 * @author Saint
 */
@Slf4j
@Intercepts({@Signature(type = StatementHandler.class,
        method = "prepare", args = {Connection.class, Integer.class}),
        @Signature(type = ParameterHandler.class, method = "setParameters", args = {PreparedStatement.class})
})
public class MybatisInterceptor implements Interceptor {

    /**
     * Tables not intercepted
     */
    @Value("#{'${mybatis.plugin.ignoreTables:}'.split(',')}")
    private List<String> ignoreTableList = Collections.emptyList();

    /**
     * 从启动命令的-D参数中获取`ENABLE_MYBATIS_PLUGIN`参数的值，表示是否启动mybatis拦截器
     * 当然也可以使用System.getEnv()从环境变量中获取
     */
    private String enableMybatisPlugin = System.getProperty("ENABLE_MYBATIS_PLUGIN");

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        try {

            if (StringUtils.isEmpty(enableMybatisPlugin) || (!StringUtils.equals(enableMybatisPlugin, "true")
                    && !StringUtils.equals(enableMybatisPlugin, "TRUE"))) {
                return invocation.proceed();
            }

            String invocationName = invocation.getMethod().getName();

            if (Objects.equals(invocationName, MyBatisPluginConst.METHOD_PREPARE.getVale())) {

                // case1:通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;
                //   1、MetaObject是Mybatis提供的一个用于方便、优雅访问对象属性的对象;
                //   2、通过它可以简化代码、不需要try/catch各种reflect异常，同时它支持对JavaBean、Collection、Map三种类型对象的操作。
                StatementHandler handler = (StatementHandler) invocation.getTarget();
                MetaObject metaObject = SystemMetaObject.forObject(handler);

                // case2:先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，
                //        其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
                MappedStatement mappedStatement = (MappedStatement) metaObject.getValue(MyBatisPluginConst.DELEGATE_MAPPED_STATEMENT.getVale());

                // id为执行的mapper方法的全路径名，如com.uv.dao.UserMapper.insertUser
                String id = mappedStatement.getId();

                //数据库连接信息
                Configuration configuration = mappedStatement.getConfiguration();

                // sql type: UNKNOWN, INSERT, UPDATE, DELETE, SELECT, FLUSH
                String sqlCommandType = mappedStatement.getSqlCommandType().toString();

                // only intercept update and insert dml
                if (!Objects.equals(sqlCommandType, SqlCommandType.UPDATE.toString())
                        && !Objects.equals(sqlCommandType, SqlCommandType.INSERT.toString())) {
                    return invocation.proceed();
                }

                // obtain original sql，获取到原始sql语句，way1：
                String sql = metaObject.getValue(MyBatisPluginConst.DELEGATE_BOUND_SQL.getVale()).toString();
                //   way2: 也可以通过如下方式获取原始的SQL语句：
//                BoundSql boundSql = handler.getBoundSql();
//                String sql = boundSql.getSql();

                // 通过jsqlparser解析SQL，此处的statement是封装过后的Insert/Update/Query等SQL语句
                Statement statement = CCJSqlParserUtil.parse(sql);

                // todo 如果是简单的加个查询个数限制，可以使用jsqlparser解析SQL，用反射修改SQL语句也可。比如：
//                String mSql = sql + " limit 2";
//                BoundSql boundSql = handler.getBoundSql();
//                Field field = boundSql.getClass().getDeclaredField("sql");
//                field.setAccessible(true);
//                field.set(boundSql, mSql);

                switch (sqlCommandType) {
                    case "INSERT":
                        prepareInsertSql(statement, metaObject);
                        break;
                    case "UPDATE":
                        // can not handle, will not affect execute, but be elegant
                        prepareUpdateSql(statement, metaObject);
                        break;
                    default:
                        break;
                }
            } else if (Objects.equals(invocationName, MyBatisPluginConst.METHOD_SET_PARAMETERS.getVale())) {
                // 获取最原始的参数解析器：ParameterHandler
                ParameterHandler handler = (ParameterHandler) MyBatisPluginUtils.realTarget(invocation.getTarget());
                MetaObject metaObject = SystemMetaObject.forObject(handler);
                MappedStatement mappedStatement = (MappedStatement) metaObject.getValue(MyBatisPluginConst.MAPPED_STATEMENT.getVale());
                // sql type: UNKNOWN, INSERT, UPDATE, DELETE, SELECT, FLUSH
                String sqlCommandType = mappedStatement.getSqlCommandType().toString();
                // only intercept update and insert dml
                if (!Objects.equals(sqlCommandType, SqlCommandType.UPDATE.toString())
                        && !Objects.equals(sqlCommandType, SqlCommandType.INSERT.toString())) {
                    return invocation.proceed();
                }

                BoundSql boundSql = (BoundSql) metaObject.getValue(MyBatisPluginConst.BOUND_SQL.getVale());
                Statement statement = CCJSqlParserUtil.parse(boundSql.getSql());
                switch (sqlCommandType) {
                    case "INSERT":
                        Insert insert = (Insert) statement;
                        if (!matchesIgnoreTables(insert.getTable().getName())) {
                            handleParameterMapping(boundSql);
                        }
                        break;
                    case "UPDATE":
                        Update update = (Update) statement;
                        if (!matchesIgnoreTables(update.getTable().getName())) {
                            handleParameterMapping(boundSql);
                        }
                        break;
                    default:
                        break;
                }
            }

        } catch (Exception e) {
            log.error("Exception in executing MyBatis Interceptor", e);
        }

        return invocation.proceed();
    }

    /**
     * handle update sql in StatementHandler#prepare() phase
     *
     * @param statement  statement
     * @param metaObject metaObject
     */
    private void prepareUpdateSql(Statement statement, MetaObject metaObject) {

        Update update = (Update) statement;
        if (matchesIgnoreTables(update.getTable().getName())) {
            return;
        }

        boolean isContainsUserIdColumn = false;
        int modifyDateColumnIndex = 0;

        for (int i = 0; i < update.getColumns().size(); i++) {
            Column column = update.getColumns().get(i);
            if (column.getColumnName().equals("user_name")) {
                // sql中包含了设置的列名，则只需要设置值
                isContainsUserIdColumn = true;
                modifyDateColumnIndex = i;
            }
        }

        // 如果sql语句已经包含了`user_name`字段，则更新字段值，否者新增字段列和值
        if (isContainsUserIdColumn) {
            updateValueWithIndex(modifyDateColumnIndex, "Saint-update", update);
        } else {
            updateValue("user_name", "Saint-update", update);
        }

        log.debug("intercept update sql is : {}", update);
        metaObject.setValue("delegate.boundSql.sql", update.toString());

    }

    /**
     * handle insert sql in StatementHandler#prepare() phase
     *
     * @param statement  statement
     * @param metaObject metaObject
     */
    private void prepareInsertSql(Statement statement, MetaObject metaObject) {

        Insert insert = (Insert) statement;
        if (matchesIgnoreTables(insert.getTable().getName())) {
            return;
        }

        boolean isContainsUserIdColumn = false;
        int createDateColumnIndex = 0;
        for (int i = 0; i < insert.getColumns().size(); i++) {
            Column column = insert.getColumns().get(i);
            if (column.getColumnName().equals("user_name")) {
                // sql中包含了设置的列名，则只需要设置值
                isContainsUserIdColumn = true;
                createDateColumnIndex = i;
            }
        }

        if (isContainsUserIdColumn) {
            intoValueWithIndex(createDateColumnIndex, "Saint-insert", insert);
        } else {
            intoValue("user_name", "Saint-insert", insert);
        }

        log.debug("intercept insert sql is : {}", insert);

        metaObject.setValue("delegate.boundSql.sql", insert.toString());
    }

    /**
     * update sql update column value
     *
     * @param modifyDateColumnIndex
     * @param columnValue
     * @param update
     */
    private void updateValueWithIndex(int modifyDateColumnIndex, Object columnValue, Update update) {
        if (columnValue instanceof Long) {
            update.getExpressions().set(modifyDateColumnIndex, new LongValue((Long) columnValue));
        } else if (columnValue instanceof String) {
            update.getExpressions().set(modifyDateColumnIndex, new StringValue((String) columnValue));
        } else {
            // if you need to add other type data, add more if branch
            update.getExpressions().set(modifyDateColumnIndex, new StringValue((String) columnValue));
        }
    }

    /**
     * update sql add column
     *
     * @param updateDateColumnName
     * @param columnValue
     * @param update
     */
    private void updateValue(String updateDateColumnName, Object columnValue, Update update) {
        // 添加列
        update.getColumns().add(new Column(updateDateColumnName));
        if (columnValue instanceof Long) {
            update.getExpressions().add(new LongValue((Long) columnValue));
        } else if (columnValue instanceof String) {
            update.getExpressions().add(new StringValue((String) columnValue));
        } else {
            // if you need to add other type data, add more if branch
            update.getExpressions().add(new StringValue((String) columnValue));
        }
    }

    /**
     * insert sql add column
     *
     * @param columnName
     * @param columnValue
     * @param insert
     */
    private void intoValue(String columnName, final Object columnValue, Insert insert) {
        // 添加列
        insert.getColumns().add(new Column(columnName));
        // 通过visitor设置对应的值
        if (insert.getItemsList() == null) {
            insert.getSelect().getSelectBody().accept(new PlainSelectVisitor(-1, columnValue));
        } else {
            insert.getItemsList().accept(new ItemsListVisitor() {
                @Override
                public void visit(SubSelect subSelect) {
                    throw new UnsupportedOperationException("Not supported yet.");
                }

                @Override
                public void visit(ExpressionList expressionList) {
                    // 这里表示添加列时。列值在数据库中的数据类型， 目前只用到了Long和String，需要的自行扩展
                    // todo 下面出现此类代码的都一样
                    if (columnValue instanceof String) {
                        expressionList.getExpressions().add(new StringValue((String) columnValue));
                    } else if (columnValue instanceof Long) {
                        expressionList.getExpressions().add(new LongValue((Long) columnValue));
                    } else {
                        // if you need to add other type data, add more if branch
                        expressionList.getExpressions().add(new StringValue((String) columnValue));
                    }
                }

                @Override
                public void visit(NamedExpressionList namedExpressionList) {
                    throw new UnsupportedOperationException("Not supported yet.");
                }

                @Override
                public void visit(MultiExpressionList multiExpressionList) {
                    for (ExpressionList expressionList : multiExpressionList.getExprList()) {
                        if (columnValue instanceof String) {
                            expressionList.getExpressions().add(new StringValue((String) columnValue));
                        } else if (columnValue instanceof Long) {
                            expressionList.getExpressions().add(new LongValue((Long) columnValue));
                        } else {
                            // if you need to add other type data, add more if branch
                            expressionList.getExpressions().add(new StringValue((String) columnValue));
                        }
                    }
                }
            });
        }
    }

    /**
     * insert sql update column value
     *
     * @param index
     * @param columnValue
     * @param insert
     */
    private void intoValueWithIndex(final int index, final Object columnValue, Insert insert) {
        // 通过visitor设置对应的值
        if (insert.getItemsList() == null) {
            insert.getSelect().getSelectBody().accept(new PlainSelectVisitor(index, columnValue));
        } else {
            insert.getItemsList().accept(new ItemsListVisitor() {
                @Override
                public void visit(SubSelect subSelect) {
                    throw new UnsupportedOperationException("Not supported yet.");
                }

                @Override
                public void visit(ExpressionList expressionList) {
                    if (columnValue instanceof String) {
                        expressionList.getExpressions().set(index, new StringValue((String) columnValue));
                    } else if (columnValue instanceof Long) {
                        expressionList.getExpressions().set(index, new LongValue((Long) columnValue));
                    } else {
                        // if you need to add other type data, add more if branch
                        expressionList.getExpressions().set(index, new StringValue((String) columnValue));
                    }
                }

                @Override
                public void visit(NamedExpressionList namedExpressionList) {
                    throw new UnsupportedOperationException("Not supported yet.");
                }

                @Override
                public void visit(MultiExpressionList multiExpressionList) {
                    for (ExpressionList expressionList : multiExpressionList.getExprList()) {
                        if (columnValue instanceof String) {
                            expressionList.getExpressions().set(index, new StringValue((String) columnValue));
                        } else if (columnValue instanceof Long) {
                            expressionList.getExpressions().set(index, new LongValue((Long) columnValue));
                        } else {
                            // if you need to add other type data, add more if branch
                            expressionList.getExpressions().set(index, new StringValue((String) columnValue));
                        }
                    }
                }
            });
        }
    }

    /**
     * 将已经存在的列从ParameterMapping中移除
     * 以解决原始sql语句中已包含自动添加的列 导致参数数量映射异常的问题
     *
     * @param boundSql
     */
    private void handleParameterMapping(BoundSql boundSql) {
        List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
        Iterator<ParameterMapping> it = parameterMappingList.iterator();
        String userIdProperty = StringUtil.snakeToCamelCase("user_name");
        while (it.hasNext()) {
            ParameterMapping pm = it.next();
            // 后面的条件为兼容批量插入操作(不能用contains)
            if (pm.getProperty().equals(userIdProperty) || pm.getProperty().endsWith("." + userIdProperty)) {
                log.debug("原始Sql语句已包含自动添加的列: {}", userIdProperty);
                it.remove();
            }
        }
    }

    /**
     * 忽略处理配置的表
     *
     * @param tableName 当前执行的sql表
     * @return true：表示匹配忽略的表，false：表示不匹配忽略的表
     */
    private boolean matchesIgnoreTables(String tableName) {
        for (String ignoreTable : ignoreTableList) {
            if (tableName.matches(ignoreTable)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 支持INSERT INTO SELECT 语句
     */
    private class PlainSelectVisitor implements SelectVisitor {
        int index;
        Object columnValue;

        public PlainSelectVisitor(int index, Object columnValue) {
            this.index = index;
            this.columnValue = columnValue;
        }

        @Override
        public void visit(PlainSelect plainSelect) {
            if (index != -1) {
                if (columnValue instanceof String) {
                    plainSelect.getSelectItems().set(index, new SelectExpressionItem(new StringValue((String) columnValue)));
                } else if (columnValue instanceof Long) {
                    plainSelect.getSelectItems().set(index, new SelectExpressionItem(new LongValue((Long) columnValue)));
                } else {
                    // if you need to add other type data, add more if branch
                    plainSelect.getSelectItems().set(index, new SelectExpressionItem(new StringValue((String) columnValue)));
                }
            } else {
                if (columnValue instanceof String) {
                    plainSelect.getSelectItems().add(new SelectExpressionItem(new StringValue((String) columnValue)));
                } else if (columnValue instanceof Long) {
                    plainSelect.getSelectItems().add(new SelectExpressionItem(new LongValue((Long) columnValue)));
                } else {
                    // if you need to add other type data, add more if branch
                    plainSelect.getSelectItems().add(new SelectExpressionItem(new StringValue((String) columnValue)));
                }
            }
        }

        @Override
        public void visit(SetOperationList setOperationList) {
            throw new UnsupportedOperationException("Not supported yet.");
        }

        @Override
        public void visit(WithItem withItem) {
            if (index != -1) {
                if (columnValue instanceof String) {
                    withItem.getWithItemList().set(index, new SelectExpressionItem(new StringValue((String) columnValue)));
                } else if (columnValue instanceof Long) {
                    withItem.getWithItemList().set(index, new SelectExpressionItem(new LongValue((Long) columnValue)));
                } else {
                    // if you need to add other type data, add more if branch
                    withItem.getWithItemList().set(index, new SelectExpressionItem(new StringValue((String) columnValue)));
                }
            } else {
                if (columnValue instanceof String) {
                    withItem.getWithItemList().add(new SelectExpressionItem(new StringValue((String) columnValue)));
                } else if (columnValue instanceof Long) {
                    withItem.getWithItemList().add(new SelectExpressionItem(new LongValue((Long) columnValue)));
                } else {
                    // if you need to add other type data, add more if branch
                    withItem.getWithItemList().add(new SelectExpressionItem(new StringValue((String) columnValue)));
                }
            }
        }

        @Override
        public void visit(ValuesStatement valuesStatement) {
            if (index != -1) {
                if (columnValue instanceof String) {
                    valuesStatement.getExpressions().set(index, new StringValue((String) columnValue));
                } else if (columnValue instanceof Long) {
                    valuesStatement.getExpressions().set(index, new LongValue((Long) columnValue));
                } else {
                    // if you need to add other type data, add more if branch
                    valuesStatement.getExpressions().set(index, new StringValue((String) columnValue));
                }
            } else {
                if (columnValue instanceof String) {
                    valuesStatement.getExpressions().add(new StringValue((String) columnValue));
                } else if (columnValue instanceof Long) {
                    valuesStatement.getExpressions().add(new LongValue((Long) columnValue));
                } else {
                    // if you need to add other type data, add more if branch
                    valuesStatement.getExpressions().add(new StringValue((String) columnValue));
                }
            }
        }
    }

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

    @Override
    public void setProperties(Properties properties) {
        // 接收到配置文件的property参数
    }
}

2、使用jsqlparser低版本（例如1.2）的自定义MyBatis plugin

maven依赖：

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

自定MyBatis Plugin核心代码：

package com.saint.mybatis;

import com.saint.constant.MyBatisPluginConst;
import com.saint.utils.MyBatisPluginUtils;
import com.saint.utils.StringUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ItemsListVisitor;
import net.sf.jsqlparser.expression.operators.relational.MultiExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.springframework.beans.factory.annotation.Value;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.*;

/**
 * MyBatis拦截器；自定义TraceContext落盘业务表逻辑
 *
 * @author Saint
 */
@Slf4j
@Intercepts({@Signature(type = StatementHandler.class,
        method = "prepare", args = {Connection.class, Integer.class}),
        @Signature(type = ParameterHandler.class, method = "setParameters", args = {PreparedStatement.class})
})
public class MybatisInterceptor implements Interceptor {

    /**
     * Tables not intercepted
     */
    @Value("#{'${mybatis.plugin.ignoreTables:}'.split(',')}")
    private List<String> ignoreTableList = Collections.emptyList();

    /**
     * 从启动命令的-D参数中获取`ENABLE_MYBATIS_PLUGIN`参数的值，表示是否启动mybatis拦截器
     * 当然也可以使用System.getEnv()从环境变量中获取
     */
    private String enableMybatisPlugin = System.getProperty("ENABLE_MYBATIS_PLUGIN");

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        try {

            if (StringUtils.isEmpty(enableMybatisPlugin) || (!StringUtils.equals(enableMybatisPlugin, "true")
                    && !StringUtils.equals(enableMybatisPlugin, "TRUE"))) {
                return invocation.proceed();
            }

            String invocationName = invocation.getMethod().getName();

            if (Objects.equals(invocationName, MyBatisPluginConst.METHOD_PREPARE.getVale())) {

                // case1:通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;
                //   1、MetaObject是Mybatis提供的一个用于方便、优雅访问对象属性的对象;
                //   2、通过它可以简化代码、不需要try/catch各种reflect异常，同时它支持对JavaBean、Collection、Map三种类型对象的操作。
                StatementHandler handler = (StatementHandler) invocation.getTarget();
                MetaObject metaObject = SystemMetaObject.forObject(handler);

                // case2:先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，
                //        其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
                MappedStatement mappedStatement = (MappedStatement) metaObject.getValue(MyBatisPluginConst.DELEGATE_MAPPED_STATEMENT.getVale());

                // id为执行的mapper方法的全路径名，如com.uv.dao.UserMapper.insertUser
                String id = mappedStatement.getId();

                //数据库连接信息
                Configuration configuration = mappedStatement.getConfiguration();

                // sql type: UNKNOWN, INSERT, UPDATE, DELETE, SELECT, FLUSH
                String sqlCommandType = mappedStatement.getSqlCommandType().toString();

                // only intercept update and insert dml
                if (!Objects.equals(sqlCommandType, SqlCommandType.UPDATE.toString())
                        && !Objects.equals(sqlCommandType, SqlCommandType.INSERT.toString())) {
                    return invocation.proceed();
                }

                // obtain original sql，获取到原始sql语句，way1：
                String sql = metaObject.getValue(MyBatisPluginConst.DELEGATE_BOUND_SQL.getVale()).toString();
                //   way2: 也可以通过如下方式获取原始的SQL语句：
//                BoundSql boundSql = handler.getBoundSql();
//                String sql = boundSql.getSql();

                // 通过jsqlparser解析SQL，此处的statement是封装过后的Insert/Update/Query等SQL语句
                Statement statement = CCJSqlParserUtil.parse(sql);

                // todo 如果是简单的加个查询个数限制，可以使用jsqlparser解析SQL，用反射修改SQL语句也可。比如：
//                String mSql = sql + " limit 2";
//                BoundSql boundSql = handler.getBoundSql();
//                Field field = boundSql.getClass().getDeclaredField("sql");
//                field.setAccessible(true);
//                field.set(boundSql, mSql);

                switch (sqlCommandType) {
                    case "INSERT":
                        prepareInsertSql(statement, metaObject);
                        break;
                    case "UPDATE":
                        // can not handle, will not affect execute, but be elegant
                        prepareUpdateSql(statement, metaObject);
                        break;
                    default:
                        break;
                }
            } else if (Objects.equals(invocationName, MyBatisPluginConst.METHOD_SET_PARAMETERS.getVale())) {
                // 获取最原始的参数解析器：ParameterHandler
                ParameterHandler handler = (ParameterHandler) MyBatisPluginUtils.realTarget(invocation.getTarget());
                MetaObject metaObject = SystemMetaObject.forObject(handler);
                MappedStatement mappedStatement = (MappedStatement) metaObject.getValue(MyBatisPluginConst.MAPPED_STATEMENT.getVale());
                // sql type: UNKNOWN, INSERT, UPDATE, DELETE, SELECT, FLUSH
                String sqlCommandType = mappedStatement.getSqlCommandType().toString();
                // only intercept update and insert dml
                if (!Objects.equals(sqlCommandType, SqlCommandType.UPDATE.toString())
                        && !Objects.equals(sqlCommandType, SqlCommandType.INSERT.toString())) {
                    return invocation.proceed();
                }

                BoundSql boundSql = (BoundSql) metaObject.getValue(MyBatisPluginConst.BOUND_SQL.getVale());
                Statement statement = CCJSqlParserUtil.parse(boundSql.getSql());
                switch (sqlCommandType) {
                    case "INSERT":
                        Insert insert = (Insert) statement;
                        if (!matchesIgnoreTables(insert.getTable().getName())) {
                            handleParameterMapping(boundSql);
                        }
                        break;
                    case "UPDATE":
                        Update update = (Update) statement;
                        if (!matchesIgnoreTables(update.getTables().get(0).getName())) {
                            handleParameterMapping(boundSql);
                        }
                        break;
                    default:
                        break;
                }
            }

        } catch (Exception e) {
            log.error("Exception in executing MyBatis Interceptor", e);
        }

        return invocation.proceed();
    }

    /**
     * handle update sql in StatementHandler#prepare() phase
     *
     * @param statement  statement
     * @param metaObject metaObject
     */
    private void prepareUpdateSql(Statement statement, MetaObject metaObject) {

        Update update = (Update) statement;
        if (matchesIgnoreTables(update.getTables().get(0).getName())) {
            return;
        }

        boolean isContainsUserIdColumn = false;
        int modifyDateColumnIndex = 0;

        for (int i = 0; i < update.getColumns().size(); i++) {
            Column column = update.getColumns().get(i);
            if (column.getColumnName().equals("user_name")) {
                // sql中包含了设置的列名，则只需要设置值
                isContainsUserIdColumn = true;
                modifyDateColumnIndex = i;
            }
        }

        // 如果sql语句已经包含了`user_name`字段，则更新字段值，否者新增字段列和值
        if (isContainsUserIdColumn) {
            updateValueWithIndex(modifyDateColumnIndex, "Saint-update", update);
        } else {
            updateValue("user_name", "Saint-update", update);
        }

        log.debug("intercept update sql is : {}", update);
        metaObject.setValue("delegate.boundSql.sql", update.toString());

    }

    /**
     * handle insert sql in StatementHandler#prepare() phase
     *
     * @param statement  statement
     * @param metaObject metaObject
     */
    private void prepareInsertSql(Statement statement, MetaObject metaObject) {

        Insert insert = (Insert) statement;
        if (matchesIgnoreTables(insert.getTable().getName())) {
            return;
        }

        boolean isContainsUserIdColumn = false;
        int createDateColumnIndex = 0;
        for (int i = 0; i < insert.getColumns().size(); i++) {
            Column column = insert.getColumns().get(i);
            if (column.getColumnName().equals("user_name")) {
                // sql中包含了设置的列名，则只需要设置值
                isContainsUserIdColumn = true;
                createDateColumnIndex = i;
            }
        }

        if (isContainsUserIdColumn) {
            intoValueWithIndex(createDateColumnIndex, "Saint-insert", insert);
        } else {
            intoValue("user_name", "Saint-insert", insert);
        }

        log.debug("intercept insert sql is : {}", insert);

        metaObject.setValue("delegate.boundSql.sql", insert.toString());
    }

    /**
     * update sql update column value
     *
     * @param modifyDateColumnIndex
     * @param columnValue
     * @param update
     */
    private void updateValueWithIndex(int modifyDateColumnIndex, Object columnValue, Update update) {
        if (columnValue instanceof Long) {
            update.getExpressions().set(modifyDateColumnIndex, new LongValue((Long) columnValue));
        } else if (columnValue instanceof String) {
            update.getExpressions().set(modifyDateColumnIndex, new StringValue((String) columnValue));
        } else {
            // if you need to add other type data, add more if branch
            update.getExpressions().set(modifyDateColumnIndex, new StringValue((String) columnValue));
        }
    }

    /**
     * update sql add column
     *
     * @param updateDateColumnName
     * @param columnValue
     * @param update
     */
    private void updateValue(String updateDateColumnName, Object columnValue, Update update) {
        // 添加列
        update.getColumns().add(new Column(updateDateColumnName));
        if (columnValue instanceof Long) {
            update.getExpressions().add(new LongValue((Long) columnValue));
        } else if (columnValue instanceof String) {
            update.getExpressions().add(new StringValue((String) columnValue));
        } else {
            // if you need to add other type data, add more if branch
            update.getExpressions().add(new StringValue((String) columnValue));
        }
    }

    /**
     * insert sql add column
     *
     * @param columnName
     * @param columnValue
     * @param insert
     */
    private void intoValue(String columnName, final Object columnValue, Insert insert) {
        // 添加列
        insert.getColumns().add(new Column(columnName));
        // 通过visitor设置对应的值
        if (insert.getItemsList() == null) {
            insert.getSelect().getSelectBody().accept(new PlainSelectVisitor(-1, columnValue));
        } else {
            insert.getItemsList().accept(new ItemsListVisitor() {
                @Override
                public void visit(SubSelect subSelect) {
                    throw new UnsupportedOperationException("Not supported yet.");
                }

                @Override
                public void visit(ExpressionList expressionList) {
                    // 这里表示添加列时。列值在数据库中的数据类型， 目前只用到了Long和String，需要的自行扩展
                    // todo 下面出现此类代码的都一样
                    if (columnValue instanceof String) {
                        expressionList.getExpressions().add(new StringValue((String) columnValue));
                    } else if (columnValue instanceof Long) {
                        expressionList.getExpressions().add(new LongValue((Long) columnValue));
                    } else {
                        // if you need to add other type data, add more if branch
                        expressionList.getExpressions().add(new StringValue((String) columnValue));
                    }
                }

                @Override
                public void visit(MultiExpressionList multiExpressionList) {
                    for (ExpressionList expressionList : multiExpressionList.getExprList()) {
                        if (columnValue instanceof String) {
                            expressionList.getExpressions().add(new StringValue((String) columnValue));
                        } else if (columnValue instanceof Long) {
                            expressionList.getExpressions().add(new LongValue((Long) columnValue));
                        } else {
                            // if you need to add other type data, add more if branch
                            expressionList.getExpressions().add(new StringValue((String) columnValue));
                        }
                    }
                }
            });
        }
    }

    /**
     * insert sql update column value
     *
     * @param index
     * @param columnValue
     * @param insert
     */
    private void intoValueWithIndex(final int index, final Object columnValue, Insert insert) {
        // 通过visitor设置对应的值
        if (insert.getItemsList() == null) {
            insert.getSelect().getSelectBody().accept(new PlainSelectVisitor(index, columnValue));
        } else {
            insert.getItemsList().accept(new ItemsListVisitor() {
                @Override
                public void visit(SubSelect subSelect) {
                    throw new UnsupportedOperationException("Not supported yet.");
                }

                @Override
                public void visit(ExpressionList expressionList) {
                    if (columnValue instanceof String) {
                        expressionList.getExpressions().set(index, new StringValue((String) columnValue));
                    } else if (columnValue instanceof Long) {
                        expressionList.getExpressions().set(index, new LongValue((Long) columnValue));
                    } else {
                        // if you need to add other type data, add more if branch
                        expressionList.getExpressions().set(index, new StringValue((String) columnValue));
                    }
                }

                @Override
                public void visit(MultiExpressionList multiExpressionList) {
                    for (ExpressionList expressionList : multiExpressionList.getExprList()) {
                        if (columnValue instanceof String) {
                            expressionList.getExpressions().set(index, new StringValue((String) columnValue));
                        } else if (columnValue instanceof Long) {
                            expressionList.getExpressions().set(index, new LongValue((Long) columnValue));
                        } else {
                            // if you need to add other type data, add more if branch
                            expressionList.getExpressions().set(index, new StringValue((String) columnValue));
                        }
                    }
                }
            });
        }
    }

    /**
     * 将已经存在的列从ParameterMapping中移除
     * 以解决原始sql语句中已包含自动添加的列 导致参数数量映射异常的问题
     *
     * @param boundSql
     */
    private void handleParameterMapping(BoundSql boundSql) {
        List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
        Iterator<ParameterMapping> it = parameterMappingList.iterator();
        String userIdProperty = StringUtil.snakeToCamelCase("user_name");
        while (it.hasNext()) {
            ParameterMapping pm = it.next();
            // 后面的条件为兼容批量插入操作(不能用contains)
            if (pm.getProperty().equals(userIdProperty) || pm.getProperty().endsWith("." + userIdProperty)) {
                log.debug("原始Sql语句已包含自动添加的列: {}", userIdProperty);
                it.remove();
            }
        }
    }

    /**
     * 忽略处理配置的表
     *
     * @param tableName 当前执行的sql表
     * @return true：表示匹配忽略的表，false：表示不匹配忽略的表
     */
    private boolean matchesIgnoreTables(String tableName) {
        for (String ignoreTable : ignoreTableList) {
            if (tableName.matches(ignoreTable)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 支持INSERT INTO SELECT 语句
     */
    private class PlainSelectVisitor implements SelectVisitor {
        int index;
        Object columnValue;

        public PlainSelectVisitor(int index, Object columnValue) {
            this.index = index;
            this.columnValue = columnValue;
        }

        @Override
        public void visit(PlainSelect plainSelect) {
            if (index != -1) {
                if (columnValue instanceof String) {
                    plainSelect.getSelectItems().set(index, new SelectExpressionItem(new StringValue((String) columnValue)));
                } else if (columnValue instanceof Long) {
                    plainSelect.getSelectItems().set(index, new SelectExpressionItem(new LongValue((Long) columnValue)));
                } else {
                    // if you need to add other type data, add more if branch
                    plainSelect.getSelectItems().set(index, new SelectExpressionItem(new StringValue((String) columnValue)));
                }
            } else {
                if (columnValue instanceof String) {
                    plainSelect.getSelectItems().add(new SelectExpressionItem(new StringValue((String) columnValue)));
                } else if (columnValue instanceof Long) {
                    plainSelect.getSelectItems().add(new SelectExpressionItem(new LongValue((Long) columnValue)));
                } else {
                    // if you need to add other type data, add more if branch
                    plainSelect.getSelectItems().add(new SelectExpressionItem(new StringValue((String) columnValue)));
                }
            }
        }

        @Override
        public void visit(SetOperationList setOperationList) {
            throw new UnsupportedOperationException("Not supported yet.");
        }

        @Override
        public void visit(WithItem withItem) {
            if (index != -1) {
                if (columnValue instanceof String) {
                    withItem.getWithItemList().set(index, new SelectExpressionItem(new StringValue((String) columnValue)));
                } else if (columnValue instanceof Long) {
                    withItem.getWithItemList().set(index, new SelectExpressionItem(new LongValue((Long) columnValue)));
                } else {
                    // if you need to add other type data, add more if branch
                    withItem.getWithItemList().set(index, new SelectExpressionItem(new StringValue((String) columnValue)));
                }
            } else {
                if (columnValue instanceof String) {
                    withItem.getWithItemList().add(new SelectExpressionItem(new StringValue((String) columnValue)));
                } else if (columnValue instanceof Long) {
                    withItem.getWithItemList().add(new SelectExpressionItem(new LongValue((Long) columnValue)));
                } else {
                    // if you need to add other type data, add more if branch
                    withItem.getWithItemList().add(new SelectExpressionItem(new StringValue((String) columnValue)));
                }
            }
        }
    }

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

    @Override
    public void setProperties(Properties properties) {
        // 接收到配置文件的property参数
    }
}

三、如何使用自定义的Mybatis Plugin生效？

参考我的另外一篇博文：四种方式使通用SDK中自定义Mybatis Plugin生效?

最后

以上就是忧郁网络为你收集整理的拿来即用的MyBatis Plugin实现SQL语句结构动态新增/更改字段（线上运行中）的全部内容，希望文章能够帮你解决拿来即用的MyBatis Plugin实现SQL语句结构动态新增/更改字段（线上运行中）所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。