聊聊 SaaS 多租户系统数据隔离实现方案

public class TenantLineInnerInterceptor extends JsqlParserSupport implements InnerInterceptor {
    // 多租户处理器
    private TenantLineHandler tenantLineHandler;

    // 改SQL，添加多租户id条件
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        if (!InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) {
            MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
            mpBs.sql(this.parserSingle(mpBs.sql(), (Object)null));
        }
    }

    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
        MappedStatement ms = mpSh.mappedStatement();
        SqlCommandType sct = ms.getSqlCommandType();
        if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
            if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) {
                return;
            }

            MPBoundSql mpBs = mpSh.mPBoundSql();
            mpBs.sql(this.parserMulti(mpBs.sql(), (Object)null));
        }

    }
  
  // 碍于篇幅问题，下面省略的代码就是继承抽象类JsqlParserSupport解析SQL然后添加多租户id条件的，可以自行查看源码
  ......
}

public class TenantDatabaseHandler implements TenantLineHandler {
    private final Set<String> ignoreTables = new HashSet<>();

    public TenantDatabaseHandler(TenantProperties properties) {
        // 将配置文件配置的忽略表名同步大小写，适配不同写法
        properties.getIgnoreTables().forEach(table -> {
            ignoreTables.add(table.toLowerCase());
            ignoreTables.add(table.toUpperCase());
        });
    }

    /**
     * 获取租户字段名
     * <p>
     * 默认字段名叫: tenant_id，我这里使用org_id
     *
     *     @return 租户字段名
     */
    @Override
     public String getTenantIdColumn() {
        return "org_id";
    }


    @Override
    public Expression getTenantId() {
        // 这里通过登录信息上下文返回租户id给多租户拦截器增强SQL使用
        return new LongValue(RequestUserHolder.getCurrentUser().getOrgId());
    }

    @Override
    public boolean ignoreTable(String tableName) {
        // 忽略多租户的表
        return CollUtil.contains(ignoreTables, tableName);
    }
}

@ConfigurationProperties(prefix = "ptc.tenant")
@Data
public class TenantProperties {


    /**
     * 全局控制是否开启多租户功能
     */
    private Boolean enable = Boolean.TRUE;

    /**
     * 需要忽略多租户的表
     *
     * 即默认所有表都开启多租户的功能，所以记得添加对应的 tenant_id 字段哟
     */
    private Set<String> ignoreTables = Collections.emptySet();
}

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(TenantProperties properties) {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        // 必须保证多租户插件在分页插件之前，这个是 MyBatis-plus 的规定
        if (properties.getEnable()) {
            mybatisPlusInterceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantDatabaseHandler(properties)));
        }
        // 分页插件
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mybatisPlusInterceptor;
    }

    <select id="getUserList" resultType="com.plasticene.textile.entity.User">
        select u.* from user u
        left join user_role r on u.id = r.user_id
        <where>
            <if test="query.status != null">
                and u.status = #{query.status}
            </if>
            <if test="query.roleId != null">
                and r.role_id = #{query.roleId}
            </if>
            <if test="query.keyword != null">
                and ((u.name like concat('%',#{query.keyword},'%')) or (u.mobile like concat(#{query.keyword},'%')))
            </if>
            <if test="query.startEntryTime != null">
                and u.entry_time >= #{query.startEntryTime}
            </if>
            <if test="query.endEntryTime != null">
                <![CDATA[ and u.entry_time <= #{query.endEntryTime}]]>
            </if>

        </where>
        group by u.id
        order by u.id desc
    </select>

public PageResult<UserDTO> getList(UserQuery query) {
        Page<UserDTO> page = new Page<>(query.getPageNo(), query.getPageSize());
        List<User> userList = userDAO.getUserList(page, query);
        List<UserDTO> userDTOS = toUserDTOList(userList);
        return new PageResult<>(userDTOS, page.getTotal(), page.getPages());
    }

[1658720355293990912] [DEBUG] [2023-05-17 14:25:25.504] [http-nio-16688-exec-1@23652]  com.plasticene.textile.dao.UserDAO.getUserList debug : ==>  Preparing: SELECT u.* FROM user u LEFT JOIN user_role r ON u.id = r.user_id AND r.org_id = 3 WHERE u.org_id = 3 GROUP BY u.id ORDER BY u.id DESC LIMIT ?
[1658720355293990912] [DEBUG] [2023-05-17 14:25:25.505] [http-nio-16688-exec-1@23652]  com.plasticene.textile.dao.UserDAO.getUserList debug : ==> Parameters: 20(Long)

ptc:
  tenant:
    ignore-tables: user

select u.* from `user` u
        left join user_role r on u.id = r.user_id

public interface UserDAO extends BaseMapperX<User> {

    @InterceptorIgnore(tenantLine = "true")
    List<User> getUserList(IPage<UserDTO> userPage, @Param("query") UserQuery query);
}

select u.* from user u
        left join user_role ur on u.id = ur.user_id

Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: select u.* from user u
left join user_role ur on u.id = ur.user_id
group by u.id
order by u.id desc
at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39)
at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:52)
at com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor.beforeQuery(TenantLineInnerInterceptor.java:65)
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:78)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy178.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
... 101 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "ur" <K_ISOLATION>
at line 2, column 29.