# 多租户

从2.1.0开始支持多租户，fastmybatis支持两种多租户方式：表字段隔离、表隔离。

# 表字段隔离

将数据放在同一张表中，通过一个租户字段（如：tenant_id）进行区分。

假设数据库表结构如下：

CREATE TABLE `tenant_demo`  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `tenant_id` varchar(64) NOT NULL COMMENT '租户id',
    `name` varchar(64) NOT NULL COMMENT '名称',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB COMMENT = '多租户表演示';

创建一个ThreadLocal用来存放tenantId

public class TenantContext {

    private static ThreadLocal<String> tenantLocal = new ThreadLocal<>();

    public static void setTenantId(String tenantId) {
        tenantLocal.set(tenantId);
    }

    public static String getTenantId() {
        return tenantLocal.get();
    }

    public static void remove() {
        tenantLocal.remove();
    }
}

创建一个拦截器，每次请求都获取tenantId

public class TenantInterceptor implements HandlerInterceptor {

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        // 先从header取
        String tenantId = request.getHeader("tenantId");
        // 如果没有再从参数里面取
        if (StringUtils.isEmpty(tenantId)) {
            tenantId = request.getParameter("tenantId");
        }
        if (StringUtils.hasText(tenantId)) {
            TenantContext.setTenantId(tenantId);
        }
        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
        TenantContext.remove();
    }
}

创建几个配置类，开启多租户设置

@Configuration
public class WebConfig implements WebMvcConfigurer {

    static {
        // 设置多租户id数据库字段，不设置默认：tenant_id
        TenantQuery.setDefaultTenantColumnName("tenant_id");
        // 添加多租户条件，每次new TenantQuery()时都会触发下面这段代码
        TenantQuery.setDefaultTenantQueryListener(query -> {
            String tenantId = TenantContext.getTenantId();
            if (StringUtils.hasText(tenantId)) {
                query.eq(query.getTenantColumnName(), tenantId);
            } else {
                // 【可选】如果没有传tenantId，则添加一个 1=2 条件，从而查不到数据
                query.oneEqTwo();
            }
        });
    }

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(new TenantInterceptor());
    }
}

至此配置完毕，最后编写测试接口


/**
 * 演示多租户
 * <pre>
 *     关键配置在WebConfig、TenantInterceptor
 * </pre>
 * @author xxx
 */
@RestController
@RequestMapping("tenant")
public class TenantDemoController {

    private static Logger log = LoggerFactory.getLogger(TenantDemoController.class);

    @Autowired
    private TenantDemoService tenantDemoService;

    /**
     * 分页查询
     * <p>
     *     http://localhost:8080/tenant/page?pageIndex=1&tenantId=a100001
     * </p>
     * @param param
     * @return
     */
    @GetMapping("/page")
    public PageInfo<TenantDemo> page(PageParam param) {
        TenantQuery query = param.toTenantQuery();
        log.info("是否设置了多租户id：{}, tenantId -> {}", query.existTenantValue(), query.getTenantValue());
        return tenantDemoService.page(query);
    }

    /**
     * http://localhost:8080/tenant/list?tenantId=a100001
     * http://localhost:8080/tenant/list?tenantId=a100001&name=Jim
     * @return
     */
    @GetMapping("/list")
    public List<TenantDemo> list(@RequestParam(required = false) String name) {
        // 使用TenantQuery表示使用了多租户查询
        TenantQuery query = new TenantQuery();
        // 添加其它查询条件
        query.eq(name != null, "name", name);
        log.info("是否设置了多租户id：{}, tenantId -> {}", query.existTenantValue(), query.getTenantValue());
        return tenantDemoService.list(query);
    }

    /**
     * 不走多租户
     * http://localhost:8080/tenant/normal?tenantId=a100001
     * @return
     */
    @GetMapping("/normal")
    public List<TenantDemo> normal() {
        // 不使用TenantQuery，走正常查询
        Query query = new Query();
        return tenantDemoService.list(query);
    }
    
}

# 表隔离

示例代码 (opens new window)

通过表进行数据隔离，每张表有不同的后缀做区分，如：user_log_a10001, user_log_a10002

假设有如下两张表：

CREATE TABLE `user_log_a10002` (
  `id` bigint(18) unsigned NOT NULL AUTO_INCREMENT COMMENT '注释',
  `user_id` int(10) DEFAULT NULL COMMENT '注释',
  `log` varchar(200) DEFAULT NULL COMMENT '注释',
  `log_date` datetime DEFAULT NULL COMMENT '注释',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '注释',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_log_a10001` (
  `id` bigint(18) unsigned NOT NULL AUTO_INCREMENT COMMENT '注释',
  `user_id` int(10) DEFAULT NULL COMMENT '注释',
  `log` varchar(200) DEFAULT NULL COMMENT '注释',
  `log_date` datetime DEFAULT NULL COMMENT '注释',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '注释',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建一个ThreadLocal用来存放tenantId

public class TenantContext {

    private static ThreadLocal<String> tenantLocal = new ThreadLocal<>();

    public static void setTenantId(String tenantId) {
        tenantLocal.set(tenantId);
    }

    public static String getTenantId() {
        return tenantLocal.get();
    }

    public static void remove() {
        tenantLocal.remove();
    }
}

创建一个拦截器，每次请求都获取tenantId

public class TenantInterceptor implements HandlerInterceptor {

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        // 先从header取
        String tenantId = request.getHeader("tenantId");
        // 如果没有再从参数里面取
        if (StringUtils.isEmpty(tenantId)) {
            tenantId = request.getParameter("tenantId");
        }
        if (StringUtils.hasText(tenantId)) {
            TenantContext.setTenantId(tenantId);
        }
        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
        TenantContext.remove();
    }
}

创建一个配置类，启用拦截器

@Configuration
public class WebConfig implements WebMvcConfigurer {

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(new TenantInterceptor());
    }
}

设置实体类@Table.name属性

@Table(name = "user_log_$tenantId$")
public class UserLog {
    ...
}

其中 $tenantId$ 表示后缀，这里做一个占位符，没有其它作用。

创建一个mybatis拦截器


/**
 * 一个简单的分表插件
 */
@Intercepts({@Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class, Integer.class})})
public class UserLogInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
        StatementHandler delegate = getFieldValue(handler, "delegate");
        MappedStatement mappedStatement = getFieldValue(delegate, "mappedStatement");
        BoundSql boundsql = handler.getBoundSql();
        String sqlId = mappedStatement.getId();

        if (StringUtils.startsWith(sqlId, "com.myapp.dao.UserLogMapper.")) {
            String sql = boundsql.getSql();
            // 获取tenantId
            String tenantId = TenantContext.getTenantId();
            sql = StringUtils.replace(sql, "$tenantId$", tenantId);
            setFieldValue(boundsql, "sql", sql);
        }
        return invocation.proceed();
    }

    private <T> T getFieldValue(Object handler, String name) {
        Field delegateField = ReflectionUtils.findField(handler.getClass(), name);
        delegateField.setAccessible(true);
        return (T) ReflectionUtils.getField(delegateField, handler);
    }

    private void setFieldValue(Object obj, String fieldName, Object fieldValue) {
        Field field = ReflectionUtils.findField(obj.getClass(), fieldName);
        if (field != null) {
            try {
                field.setAccessible(true);
                field.set(obj, fieldValue);
            } catch (IllegalArgumentException | IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }

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

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

关键在下面三句：

String tenantId = TenantContext.getTenantId();
sql = StringUtils.replace(sql, "$tenantId$", tenantId);
setFieldValue(boundsql, "sql", sql);

意思是替换sql语句中的 $tenantId$ 内容，变成租户id，如：SELECCT name FROM user_log_$tenantId$变成:SELECCT name FROM user_log_a1001

这种就是一个简单的分表插件

# 强制使用多租户查询（2.9.5）

如果数据库所有表都有租户字段，这时可以开启强制使用多租户查询

在启动前调用

FastmybatisConfig.FORCE_TENANT_QUERY = true;

开启后，如果使用new Query()将会报错，提示必须使用new TenantQuery()或Query.createTenant()

← 实体类枚举字段 ActiveRecord模式 →