Mybatis实现根据参数替换表名

1、创建ThreadLocal 用来存储动态参数

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 当前线程持有者
 */
public class ThreadLocalHolder {

    private final static Logger logger = LoggerFactory.getLogger(ThreadLocalHolder.class);

    private static final ThreadLocal<String> tenantIdThreadLocal = new ThreadLocal<>();


    public static String getTenantId() {
        logger.info("getTenantId thread id:{}, tenantId:{}", Thread.currentThread().getId(), tenantIdThreadLocal.get());
        return tenantIdThreadLocal.get();
    }

    public static void setTenantId(String id) {
        logger.info("setTenantId thread id:{}, tenantId:{}", Thread.currentThread().getId(), id);
        tenantIdThreadLocal.set(id);
    }

    public static void removeTenantId() {
        logger.info("removeTenantId thread id:{}", Thread.currentThread().getId());
        tenantIdThreadLocal.remove();
    }

}

2、创建Mybatis拦截器 用于替换表名 当表名称中携带指定字符串进行替换

import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.springframework.stereotype.Component;

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


/**
 * MyBatis插件
 * <p>
 * 用于根据租户id切换数据表
 */
@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
@Component
public class MyBatisPlugin implements Interceptor {


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof RoutingStatementHandler) {
            String tenantId = ThreadLocalHolder.getTenantId();
            if (null != tenantId) {
                StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
                BoundSql boundSql = statementHandler.getBoundSql();
                //获取到原始sql语句
                String sql = boundSql.getSql();
                //替换表名  替换指定字符串
                String mSql = sql.replaceAll("_tenant", "_" + tenantId);
                //通过反射修改sql语句
                Field field = boundSql.getClass().getDeclaredField("sql");
                field.setAccessible(true);
                field.set(boundSql, mSql);
            }
        }
        return invocation.proceed();
    }

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

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

3、使用方式

    /**
     * 列表
     */
    @GetMapping("/list")
    @ApiOperation(value = "分页查询", notes = "分页查询", httpMethod = "GET")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "tenantId", value = "需要替换的名称", example = "2022", required = true),
    })
    public AjaxResult list(String tenantId) {
        ThreadLocalHolder.setTenantId(tenantId);

        //TODO 例如原表名称为 cp_base_data_2022
        
        //TODO 在写sql时我们可以使用 cp_base_data_tenantId 
        
        //TODO 当执行sql时遇到字符串‘_tenantId’就会替换为'_2022' 最终表名为 cp_base_data_2022


        ThreadLocalHolder.removeTenantId();
        return null;
    }