Mybatis 拦截器--动态修改SQL

一、需求

表结构里面有两个固定字段，DATA_STATE 和 DELETE_MARKER

char(1) DATA_STATE 数据状态 (1正常 0逻辑删除)

char(1) DELETE_MARKER 删除标记 (0: 未删除，null：删除)

逻辑删除 DATA_STATE='0' 时，需要强制对对应的表字段（DELETE_MARKER）进行置空，

设置DELETE_MARKER=null。

二、方案

由于 DELETE_MARKER 字段是为了优化索引才加入表结构。系统中大量SQL语句，逻辑删除时，都只有 set data_state = '0'，如果一个个修改，费事费力。

所以使用 Mybatis 拦截器来做，在SQL预编译的时候，拦截UPDATE类型SQL，判断是否 set data_state = '0' 如果是，修改SQL语句同步更新 delete_marker = null。

三、代码

package com.macrosoft.interceptor;

import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.scripting.xmltags.OgnlCache;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * DELETE_MARKER mybatis拦截器
 * 需求：
 *   DATA_STATE 数据状态 (1正常 0逻辑删除)
 *   DELETE_MARKER 删除标记 (0: 未删除，null：删除)
 *   逻辑删除 DATA_STATE=0时，需要强制对对应的表字段（DELETE_MARKER）进行置空，
 *   设置DELETE_MARKER=null
 * 拦截UPDATE类型SQL，判断是否 update data_state=0
 * 修改SQL语句同步更新delete_marker=null
 * @Author : linzp
 */
@Slf4j
@Component
@ConditionalOnProperty(prefix = "delete_marker_intercept", value = "enable", havingValue = "true")
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class })})
public class DeleteMarkerInterceptor implements Interceptor {

    private static final String LOGICAL_DELETION_STRING_VALUE = "0";

    private static final String DATA_STATE_PROPERTY = "dataState";

    private static final String DATA_STATE_FIELD_UPPERCASE = "DATA_STATE";

    private static final String DATA_STATE_FIELD_LOWERCASE = "data_state";

    private static final String DELETE_MARKER = "delete_marker";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        try {
            if (invocation.getTarget() instanceof StatementHandler) {
                StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
                // MetaObject优雅访问对象的属性(这里访问statementHandler的属性),通过它可以简化代码
                MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
                MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
                // sql语句类型
                String sqlCommandType = mappedStatement.getSqlCommandType().toString();
                log.info("DeleteMarkerInterceptor拦截到sql语句类型:{}", sqlCommandType);
                if (SqlCommandType.UPDATE.name().equals(sqlCommandType)) {
                    // 获取sql语句
                    BoundSql boundSql = statementHandler.getBoundSql();
                    String oldSql = boundSql.getSql();
                    // update set是否包含data_state字段
                    if (this.containsField(oldSql)) {
                        Statement statement = CCJSqlParserUtil.parse(oldSql);
                        Update updateStatement = (Update) statement;
                        Object param = boundSql.getParameterObject();
                        ArrayList<UpdateSet> updateSets = updateStatement.getUpdateSets();
                        String property = this.getProperty(boundSql);
                        // 判断data_state是否为0
                        if (this.isOk(updateSets, param, property)) {
                            // 拼接上 delete_marker = null
                            updateStatement.addUpdateSet(new Column(DELETE_MARKER), new NullValue());
                            String newSql = updateStatement.toString();
                            log.info("DeleteMarkerInterceptor修改过的新sql:{}", newSql);
                            Field field = boundSql.getClass().getDeclaredField("sql");
                            field.setAccessible(true);
                            field.set(boundSql, newSql);
                        }
                    }
                }
            }

        } catch (Exception e) {
            // 这里需要捕获异常，为了保证主流程不会被异常阻断
            log.error("DeleteMarkerInterceptor 拦截器异常",e);
        }

        // 继续执行该执行的方法
        return invocation.proceed();
    }

    /**
     * 获取Mapper.xml文件配置的OGNL表达式
     * 如：
     *   #{map.dataState} 中的map.dataState
     *   #{dataState} 中的dataState
     * @Author : linzp
     * @param boundSql SQL包装对象
     * @return OGNL表达式
     */
    private String getProperty(BoundSql boundSql) {
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        for (ParameterMapping pm : parameterMappings) {
            if (pm.getProperty().contains(DATA_STATE_PROPERTY)) {
                return pm.getProperty();
            }
        }
        return DATA_STATE_PROPERTY;
    }

    /**
     * 判断是否需要动态修改SQL语句
     * update set 包含data_state字段，且字段值要是0
     * 才需要拼接上 delete_marker = null
     * SQL格式有如下几种：
     *      update table set data_state = 0 where condition; --> LongValue
     *      update table set data_state = '0' where condition; --> StringValue
     *      update table set data_state = #{dataState} where condition; --> JdbcParameter
     *      update table set data_state = #{map.dataState} where condition; --> JdbcParameter
     * @Author : linzp
     * @param updateSets sql语句update set 后面的字段
     * @param obj 参数对象
     * @param property Ognl表达式
     * @return 是否需要动态修改SQL语句
     */
    private boolean isOk(ArrayList<UpdateSet> updateSets, Object obj, String property) {
        for (UpdateSet updateSet : updateSets) {
            // 包含DATA_STATE字段，且字段值要是0
            Column col = updateSet.getColumns().get(0);
            if (col.getColumnName().equalsIgnoreCase(DATA_STATE_FIELD_UPPERCASE)) {
                Expression exp = updateSet.getExpressions().get(0);
                if (exp instanceof StringValue) {
                    StringValue strV = (StringValue) exp;
                    return LOGICAL_DELETION_STRING_VALUE.equals(strV.getValue());
                } else if (exp instanceof JdbcParameter) {
                    Object value = OgnlCache.getValue(property, obj);
                    return LOGICAL_DELETION_STRING_VALUE.equals(String.valueOf(value));
                } else if (exp instanceof LongValue) {
                    LongValue longV = (LongValue) exp;
                    return LOGICAL_DELETION_STRING_VALUE.equals(longV.getStringValue());
                }
                return false;
            }
        }

        return false;
    }

    /**
     * sql语句 update set是否包含data_state字段
     * 快速判断，以便让大部分SQL通过
     * @Author : linzp
     * @param oldSql 旧SQL
     * @return update set是否包含data_state字段
     */
    private boolean containsField(String oldSql) {
        int begin = !oldSql.contains("set") ? oldSql.indexOf("SET") : oldSql.indexOf("set");
        int end   = !oldSql.contains("where") ? oldSql.indexOf("WHERE") : oldSql.indexOf("where");
        String str = oldSql.substring(begin, end);
        return str.contains(DATA_STATE_FIELD_UPPERCASE) || str.contains(DATA_STATE_FIELD_LOWERCASE);
    }

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

    @Override
    public void setProperties(Properties properties) {
    }
}

代码说明：

@ConditionalOnProperty(prefix = "delete_marker_intercept", value = "enable", havingValue = "true")

条件注解做的一个开关，只有当 application.xml 中配置了

delete_marker_intercept:
  enable: ${DELETE_MARKER_ENABLE:false}

DELETE_MARKER_ENABLE = true 时，这个拦截器才生效

@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class })})

MyBatis拦截器默认可以拦截的类型只有四种，即四种接口类型

Executor、StatementHandler、ParameterHandler和ResultSetHandler。

org.apache.ibatis.executor.Executor  //拦截执行器方法
org.apache.ibatis.executor.statement.StatementHandler  //拦截SQL语法构建处理
org.apache.ibatis.executor.parameter.ParameterHandler  //拦截参数处理
org.apache.ibatis.executor.resultset.ResultSetHandler  //拦截结果集处理

对于我们的自定义拦截器必须使用MyBatis提供的@Intercepts注解来指明我们要拦截的是四种类型中的哪一种接口。

@Intercepts // 描述：标志该类是一个拦截器

@Signature  // 描述：指明该拦截器需要拦截哪一个接口的哪一个方法

          type; // 四种类型接口中的某一个接口，如 StatementHandler.class；
          method; // 对应接口中的某一个方法名，比如StatementHandler的prepare方法；
          args; // 对应接口中的某一个方法的参数，方法因为重载原因，有多个，args就是指明参数类型，从而确定是具体哪一个方法；

对于本例中我们拦截的就是 StatementHandler的prepare方法，它有两个参数Connection和Integer。

四、测试用例

TestCase:

@SpringBootTest(classes = {MyApplication.class})
public class MyServiceTest {

    @Autowired
    private MyService myService;


    @Test
    public void test1() {
        ParamVO vo = new ParamVO();
        vo.setDataState("0");
        myService.test1();
    }

    @Test
    public void test2() {
        myService.test2();
    }

    @Test
    public void test3() {
        List<MyVO> list = myService.test3();
    }

    @Test
    public void test4() {
        Map<String, Object> map = new HashMap<>();
        map.put("dataState", "0");
        map.put("name", "lilei");
        myService.test4(map);
    }

    @Test
    public void test5() {
        myService.test5("0");
    }

}

Service:

@Service
public class MyService {

    @Autowired(required = false)
    private MyMapper myMapper;

    public void test1(ParamVO vo) {
        myMapper.test1(vo);
    }

    public void test2() {
        myMapper.test2();
    }

    public List<MyVO> test3() {
        return myMapper.test3();
    }

    public void test4(Map<String, Object> map) {
        myMapper.test4(map);
    }

    public void test5(String dataState) {
        myMapper.test5(dataState);
    }

}

 Mapper.java:

@Mapper
public interface MyMapper extends BaseMapper<MyVO> {

    void test1(ParamVO vo);

    void test2();

    List<MyVO> test3();

    void test4(@Param("map") Map<String, Object> map);

    void test5(@Param("dataState") String dataState);
}

Mapper.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.macrosoft.mapper.MyMapper">

    <update id="test1">
        update my_table set name='lilei', DATA_STATE = #{dataState}, age = 6
        where ID = '1';
    </update>

    <update id="test2">
        update my_table c set c.DATA_STATE = '0' where ID = '1';
    </update>
    
    <select id="test3" resultType="com.macrosoft.entity.MyVO">
        select * from my_table where ID = '1';
    </select>
    
    <update id="test4" parameterType="map">
        update my_table set name=#{map.name}, DATA_STATE = #{map.dataState}, age = 6
        where ID = '1';
    </update>

    <update id="test5">
        update my_table set name='lilei', data_state = #{dataState}, age = 6
        where ID = '1';
    </update>

</mapper>