mybatis项目实现动态表名方法总结

实现动态表名是个很常见的需求，网上也有很多解决方法，这边总结了三种实现方式。

一、手动给每个方法加个表名的变量

缺点很明显，侵入性大，不方便，不推荐

二、mybatis插件机制拦截sql替换表名实现动态表名

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;

/**
 * mybatis插件实现动态表名，可以拦截器新增、编辑、删除、查询等
 */
@Component
@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {
                Connection.class, Integer.class})})
public class ReplaceTablePlugin implements Interceptor {

    private static final Logger LOG = LoggerFactory.getLogger(ReplaceTablePlugin.class);

    private final static Map<String, String> TABLE_MAP = new LinkedHashMap<>();

    /**
     * 需要替换的表（替换前的表名和替换后的表名）
     */
    static {
        TABLE_MAP.put("t_user", "t_user_20220101");
        TABLE_MAP.put("t_org", "t_org_20220202");
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        doTable(statementHandler, metaObject);
        return invocation.proceed();
    }

    private void doTable(StatementHandler handler, MetaObject metaStatementHandler) throws ClassNotFoundException {
        BoundSql boundSql = handler.getBoundSql();
        String originalSql = boundSql.getSql();
        if (originalSql != null && !originalSql.equals("")) {
            LOG.info("拦截前的sql:{}", originalSql);
            if (isReplaceTableName(originalSql)) {
                for (Map.Entry<String, String> entry : TABLE_MAP.entrySet()) {
                    originalSql = originalSql.replace(entry.getKey(), entry.getValue());
                }
                LOG.info("拦截后的sql:{}", originalSql);
                metaStatementHandler.setValue("delegate.boundSql.sql", originalSql);
            }
        }
    }

    private boolean isReplaceTableName(String sql) {
        for (String tableName : TABLE_MAP.keySet()) {
            if (sql.contains(tableName)) {
                return true;
            }
        }
        return false;
    }

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

    @Override
    public void setProperties(Properties properties) {
    }

    /**
     * Obtain real processing objects, possibly multi-layer agents
     *
     * @param target
     * @param <T>
     * @return
     */
    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;
    }
}

需要注意的是，mybatis的interceptor无法直接注入类，需要这样写

ApplicationContext applicationContext = SpringContextHolder.getApplicationContext();
XXXXX XXXXX= (XXXXX) applicationContext.getBean(XXXXX.class);

三、mybatis-plus的DynamicTableNameInnerInterceptor实现

3.1引入mybatis-plus的pom jar包依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-extension</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-annotation</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-core</artifactId>
    <version>3.5.2</version>
</dependency>

3.2 实现的配置类

import java.util.Map;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.redis.core.RedisTemplate;


/**
*
* mybatis-plus实现动态替换表名
*/
@MapperScan("com.xiaweiyi8080.dal.mapper")
@ComponentScan({"com.xiaweiyi8080.dal.manager"})
@Slf4j
public class MyBatisPlusConfiguration {

    @Autowired
    private RedisTemplate redisTemplate;

    // 配置文件里配置的哪些表需要动态表名
    @Value("${xxxxx.tableName:}")
    private String dynamicTableName;

    @Bean
    public MybatisPlusInterceptor dynamicTableNameInterceptor() {
        log.info("------拿到的dynamicTableName={}", dynamicTableName);
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> {
            // 获取参数方法
            Map<String, Object> paramMap = RequestDataHelper.getRequestData();
            if (CollectionUtil.isNotEmpty(paramMap)) {
                paramMap.forEach((k, v) -> log.info(k + "----" + v));
            }
            if (StringUtils.isNotBlank(dynamicTableName) && dynamicTableName.contains(tableName)) {
                log.info("------替换前表名={}", tableName);
                String suffix = "_20220101";
                tableName += suffix;
                log.info("------替换后表名={}", tableName);
            }
            return tableName;
        });
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }

}