mybatis拦截并修改sql

概要

最近要实现一个对全局sql的修改的需求，之前看mybatis源码的时候记得有个拦截器可以实现这样的功能，于是结合着网上写了下面的代码

代码实现

这是针对查询操作的拦截:

package com.xxx.common.data.interceptor;

import com.xxx.common.core.exception.ServiceException;
import com.xxx.common.core.utils.StringUtils;
import com.xxx.common.data.config.SqlInterceptorConfig;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
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.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import java.lang.reflect.Field;
import java.sql.Connection;

/**
 * @author 
 * @since 2023-05-15 15:46
 * @desc sql查询拦截器
 */
@Intercepts(
        {@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})}
)
@Slf4j
public class SqlQueryInterceptor extends AbstractSqlInterceptor implements Interceptor {

    private SqlInterceptorConfig config;

    public SqlQueryInterceptor(SqlInterceptorConfig config) {
        this.config = config;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        String properties = config.getProperties();
        if (!config.getOpen() || StringUtils.isEmpty(properties)) {
            return invocation.proceed();
        }
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = MetaObject
                .forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
                        new DefaultReflectorFactory());
        // 获取mappedStatement
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        SqlCommandType sqlType = mappedStatement.getSqlCommandType();
        if (!SqlCommandType.SELECT.equals(sqlType)) {
            return invocation.proceed();
        }
        BoundSql boundSql = statementHandler.getBoundSql();
        String oriSql = boundSql.getSql();
        try {
            String newSql = installSql(oriSql, properties);
            Field field = boundSql.getClass().getDeclaredField("sql");
            field.setAccessible(true);
            field.set(boundSql, newSql);
        } catch (Exception e) {
            log.error("mybatis sql转换异常:{}", e);
            throw new ServiceException("sql语法错误");
        }

        return invocation.proceed();
    }

}

这是针对更新操作的代码:

package com.xxx.common.data.interceptor;

import com.xxx.common.core.exception.ServiceException;
import com.xxx.common.core.utils.StringUtils;
import com.xxx.common.data.config.SqlInterceptorConfig;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;

/**
 * @author 
 * @since 2023-05-16 10:16
 * @desc sql修改拦截器
 */
@Intercepts(
        {@Signature(method = "update", args = {MappedStatement.class, Object.class}, type = Executor.class)}
)
@Slf4j
public class SqlUpdateInterceptor extends AbstractSqlInterceptor implements Interceptor {

    private SqlInterceptorConfig config;

    public SqlUpdateInterceptor(SqlInterceptorConfig config) {
        this.config = config;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        String properties = config.getProperties();
        if (!config.getOpen() || StringUtils.isEmpty(properties)) {
            return invocation.proceed();
        }

        Object[] args = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement) args[0];
        SqlCommandType sqlType = mappedStatement.getSqlCommandType();
        Object parameter = args[1];
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        String oriSql = boundSql.getSql();
        if (SqlCommandType.INSERT.equals(sqlType) || SqlCommandType.UPDATE.equals(sqlType)) {
            try {
                String newSql = installSql(oriSql, properties);
                Field field = boundSql.getClass().getDeclaredField("sql");
                field.setAccessible(true);
                field.set(boundSql, newSql);
                MappedStatement ms = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(boundSql));
                args[0] = newMs;
            } catch (Exception e) {
                log.error("mybatis sql转换异常:{}", e);
                throw new ServiceException("sql语法错误");
            }
        }

        return invocation.proceed();
    }

    /**
     * 生成新的MappedStatement
     * @param ms
     * @param newSqlSource
     * @return
     */
    private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
            builder.keyProperty(ms.getKeyProperties()[0]);
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    }

    private static class BoundSqlSqlSource implements SqlSource {
        private BoundSql boundSql;

        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }

        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }
}

这是写了个抽象类放公共方法，具体实现看自身业务:

package com.xxx.common.data.interceptor;

import com.xxx.common.core.utils.StringUtils;
import com.xxx.common.security.utils.SecurityUtils;
import lombok.extern.slf4j.Slf4j;
import java.util.Arrays;
import java.util.List;

/**
 * @author 
 * @since 2023-05-16 10:17
 * @desc sql拦截器父类
 */
@Slf4j
public abstract class AbstractSqlInterceptor {
    public static final String SPLIT_COMMA = ",";
    public static final String SPLIT_LEFT_BRACKET = "[";
    public static final String SPLIT_RIGHT_BRACKET = "]";
    public static final String EQUAL = "=";
    public static final String WHITE = " ";
    public static final String EMPTY = "";
    public static final String QUOTE = "'";
    private static final String SQL_WHERE = "where";
    /**
     * 组装sql语句
     * @param sql
     * @param insteadProperties
     * @return
     */
    String installSql(String sql, String insteadProperties) {
        if (log.isDebugEnabled()) {
            log.debug("修改前的sql: {}", sql);
        }
        List<String> insProList = Arrays.asList(insteadProperties.split(SPLIT_COMMA));
        for (String p : insProList) {
            if ((sql.contains(SQL_WHERE) || sql.contains(StringUtils.swapCase(SQL_WHERE)))
                    && (sql.contains(p) || sql.contains(StringUtils.swapCase(p)))) {
                String name = StringUtils.substringBetween(p, SPLIT_LEFT_BRACKET, SPLIT_RIGHT_BRACKET);
                String suffixSql = StringUtils.join(name, WHITE, EQUAL, WHITE, QUOTE, getParam(name), QUOTE);
                sql = StringUtils.replace(sql, p, suffixSql);
            }
        }
        if (log.isDebugEnabled()) {
            log.debug("修改后的sql: {}", sql);
        }
        return sql;
    }

    /**
     * 获取where条件列对应的值
     * @param name where后列名
     * @return
     */
    String getParam(String name) {
        // TODO 
        if (name.equals("param")) {
            return "结合自身业务返回需要替换的value";
        }
        return EMPTY;
    }
}

把拦截器放到执行链中,需要注意的是，上面两个拦截器是new出来的，spring的@Value注解无法使用，所以在这里需要用构造器注入的方式将配置参数注入进来

package com.xxx.common.data.config;

import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.xxx.common.data.interceptor.SqlQueryInterceptor;
import com.xxx.common.data.interceptor.SqlUpdateInterceptor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Component;

/**
 * @author 
 * @desc
 * @since 2023-05-16 16:19
 */

@Component
public class MybatisInterceptorConfig {

    @Autowired
    private SqlInterceptorConfig sqlInterceptorConfig;

    @Bean
    ConfigurationCustomizer sqlInterceptorConfigurationCustomizer() {
        SqlQueryInterceptor sqlQueryInterceptor = new SqlQueryInterceptor(sqlInterceptorConfig);
        SqlUpdateInterceptor sqlUpdateInterceptor = new SqlUpdateInterceptor(sqlInterceptorConfig);
        return (configuration) -> {
            configuration.addInterceptor(sqlQueryInterceptor);
            configuration.addInterceptor(sqlUpdateInterceptor);
        };
    }
}

package com.xxx.common.data.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.stereotype.Component;

/**
 * @author 
 * @since 2023-05-16 14:49
 * @desc sql拦截器配置
 */
@Data
@RefreshScope
@Component
@ConfigurationProperties(prefix = "sql.interceptor")
public class SqlInterceptorConfig {

    private Boolean open;

    private String properties;
}

小结

到这里实现mybatis的sql拦截就结束了，欢迎大佬们评论指正！
谢谢观看