基于MybatisPlus拦截器实现数据库关键字处理及官方做法

1. 背景

有些老的数据库当中可能会有些字段和数据库关键字冲突，使用mybatisPlus执行Sql的时候有时候会执行失败，前段时间和群友讨论的时候他说遇到了这个问题，当时我提议让他用我以前写的一个自定义注解+mybatis拦截器实现权限控制里边的工具类改造一下。

他说不能实现，然后找到了官方的配置信息，也就是添加最下边的那个column-format， 具体配置如下：

mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml
  configuration:
    # 这个配置会将执行的sql打印出来，在开发或测试的时候可以用
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 返回类型为Map,显示null对应的字段
    call-setters-on-nulls: true
  global-config:
    # 关闭MP3.0自带的banner
    banner: true
    db-config:
      id-type: none
      # 默认数据库表下划线命名
      table-underline: true
      # 拦截器功能 处理数据库关键字
      column-format: "`%s`"

2. 自己实现一个关键字替换的方法

仅仅是一个初步的实现，具体的逻辑还需要变更一下。

主要的实现逻辑就是在字典表中配置上数据库的关键字，然后在Sql执行之前给BoundSql中的关键字替换一下。也就是keywordHandler方法的逻辑

package net.lesscoding.keywordhandler.interceptor;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import net.lesscoding.keywordhandler.entity.SysDict;
import net.lesscoding.keywordhandler.mapper.SysDictMapper;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
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.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.InvocationTargetException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;


@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        }
)
@Slf4j
@Component
@Order(20)
public class KeywordHandlerInterceptor implements Interceptor {

    @Autowired
    @Lazy
    private SysDictMapper dictMapper;

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

        Object[] args = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement) args[0];
        SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
        //获取参数，语句成立，表示sql语句有参数，参数格式是map形式
        Object parameter = args.length > 1 ? args[1] : null;

        //id为执行的mapper方法的全路径名，如com.xxx.xxxMapper.insertXxx
        String sqlId = mappedStatement.getId();
        Executor executor = (Executor) invocation.getTarget();
        //[BoundSql]封装mybatis最终产生的sql类
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        //获取节点的配置
        Configuration configuration = mappedStatement.getConfiguration();
        String sql = getSql(configuration, boundSql, sqlId, false);
        // 非select语句直接过滤
        if (!SqlCommandType.SELECT.equals(sqlCommandType) || sqlId.contains("SysDictMapper")) {
            return proceed(invocation, sqlId, sql);
        }
        String beforeSql = boundSql.getSql().replaceAll("[\\s]+", " ");
        log.info("之前的sql {}", beforeSql);
        //String afterSql = contactConditions(beforeSql, permissionControl, (Map)parameter);
        String afterSql = keywordHandler(beforeSql);
        log.info("关键字处理之后的sql {}", afterSql);
        ReflectUtil.setFieldValue(boundSql, "sql", afterSql);
        log.info("最终执行的sql {}", getSql(configuration, boundSql, sqlId, false));
        MappedStatement ms = (MappedStatement) args[0];
        Object parameterObject = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        ResultHandler resultHandler = (ResultHandler) args[3];
        //可以对参数做各种处理
        CacheKey cacheKey = executor.createCacheKey(ms, parameterObject, rowBounds, boundSql);
        return executor.query(ms, parameterObject, rowBounds, resultHandler, cacheKey, boundSql);
    }

    private String keywordHandler(String beforeSql) {
        List<SysDict> list = dictMapper.selectList(new QueryWrapper<SysDict>()
                .eq("dict_type", "keyword"));
        for (SysDict item : list) {
            beforeSql = beforeSql.replaceAll(StrUtil.format("(?<!_){}", item.getDictCode()), item.getDictValue());
        }
        return beforeSql;
    }

    public static void main(String[] args) {
        String str = "replace name,but not replace _name, test_name,login_name, user_name";
        System.out.println(str.replaceAll("(?<!_)name", "`name`"));
    }

    private Object proceed(Invocation invocation, String sqlId, String sql) throws Throwable{
        try {
            //执行完上面的任务后，执行原有sql的执行过程
            return invocation.proceed();
        } catch (InvocationTargetException e) {
            log.error("当前sql执行错误\n {} \n, 方法id ::: {}\n 执行sql ::: {}", e.getMessage(), sqlId, sql);
            log.error("sql执行异常", e);
            throw new RuntimeException("sql执行异常" + sqlId);
        }
    }



    /**
     * 获取最终的sql语句
     *
     * @param configuration 节点配置
     * @param boundSql      BoundSql
     * @param sqlId         mapper方法的全路径名
     * @param flag          是否需要拼接mapper方法的全路径名
     * @return String
     */
    private String getSql(Configuration configuration, BoundSql boundSql, String sqlId, boolean flag) throws NoSuchFieldException {
        String sql = showSql(configuration, boundSql);
        return flag ? sqlId + ":" + sql : sql;
    }


    /**
     * 进行？的替换
     *
     * @param configuration 节点的配置
     * @param boundSql      BoundSql
     * @return String
     */
    private String showSql(Configuration configuration, BoundSql boundSql) {
        //获取参数
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        //sql语句
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (CollUtil.isNotEmpty(parameterMappings) && parameterObject != null) {
            //获取类型处理器
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            //对应的类型则进行替换
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));
            } else {
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object objectValue = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(objectValue)));
                    }
                    if (boundSql.hasAdditionalParameter(propertyName)) {
                        //动态sql
                        Object additionalParameter = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(additionalParameter)));
                    }
                    //else {
                    //    sql = sql.replaceFirst("\\?", "");
                    //}
                }
            }
        }
        return sql;
    }

    /**
     * 参数二次处理
     *
     * @param parameterObject parameterObject
     * @return String
     */
    private String getParameterValue(Object parameterObject) {
        String format = "'%s'";
        String value = "";
        if (null != parameterObject) {
            value = String.valueOf(parameterObject);
        }
        if (parameterObject instanceof String) {
            value = String.valueOf(parameterObject);
        }
        if (parameterObject instanceof Date) {
            //DateFormat dateTimeInstance = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT,
            // Locale.CHINA);
            //value = dateTimeInstance.format(parameterObject);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            value = sdf.format((Date) parameterObject);
        }
        if (parameterObject instanceof LocalDateTime) {
            value = ((LocalDateTime) parameterObject).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        }
        if (parameterObject instanceof LocalDate) {
            value = ((LocalDate) parameterObject).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        }
        if (parameterObject instanceof LocalTime) {
            value = ((LocalTime) parameterObject).format(DateTimeFormatter.ofPattern("HH:mm:ss"));
        }
        if (parameterObject instanceof Timestamp) {
            value = ((Timestamp) parameterObject).toString();
        }
        return String.format(format, value);
    }

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

    @Override
    public void setProperties(Properties properties) {
        Interceptor.super.setProperties(properties);
    }
}