Mybatis Plus租户字段隔离查询所有租户数据方法

1、TenantLineInnerInterceptor

TenantLineInnerInterceptor类是mybatis plus中的租户拦截器,TenantLineInnerInterceptor的构造方法需要传入TenantLineHandler对象，所以得实现一下TenantLineHandler接口

    @Autowired
    private SecurityFrameworkService securityFrameworkService;
    
    @Bean
    public MyTenantLineInnerInterceptor tenantLineInnerInterceptor(TenantProperties properties,
                                                                   MybatisPlusInterceptor interceptor) {

        MyTenantLineInnerInterceptor inner = new MyTenantLineInnerInterceptor(new TenantDatabaseInterceptor(properties)
                ,properties,securityFrameworkService);
        MyBatisUtils.addInterceptor(interceptor, inner, 0);
        return inner;
    }

package cn.iocoder.yudao.framework.tenant.core.db;

import cn.hutool.core.collection.CollUtil;
import cn.iocoder.yudao.framework.security.core.service.SecurityFrameworkService;
import cn.iocoder.yudao.framework.tenant.config.TenantProperties;
import cn.iocoder.yudao.framework.tenant.core.context.TenantContextHolder;
import cn.iocoder.yudao.framework.tenant.core.service.TenantFrameworkService;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitor;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.parser.SimpleNode;
import net.sf.jsqlparser.schema.Column;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class TenantDatabaseInterceptor implements TenantLineHandler {

    private final Set<String> ignoreTables = new HashSet<>();

    public TenantDatabaseInterceptor(TenantProperties properties
    ) {
        properties.getIgnoreTables().forEach(table -> {
            ignoreTables.add(table.toLowerCase());
            ignoreTables.add(table.toUpperCase());
        });
        ignoreTables.add("DUAL");

    }

    @Override
    public Expression getTenantId() {
        return new LongValue(TenantContextHolder.getRequiredTenantId());

    }

    @Override
    public boolean ignoreTable(String tableName) {
        return TenantContextHolder.isIgnore() // 情况一，全局忽略多租户
                || CollUtil.contains(ignoreTables, tableName); // 情况二，忽略多租户的表
    }

}

getTenantId方法是获取当前上下文的租户Id，TenantLineInnerInterceptor中会将租户id带入到sql中,
ignoreTable 集合中是不区分租户的表，TenantLineInnerInterceptor中会判断如果不区分租户则不会在sql中加入租户id

2、重写TenantLineInnerInterceptor类的processSelect方法
实现关键在于当访问的账号是系统租户时，给查询语句增加一个条件，所以在重写TenantLineInnerInterceptor类时可以增加一下自己系统中的SecurityService，判断当前访问的是不是系统租户，当然也可以通过其他方式判断

MyTenantLineInnerInterceptor：

package cn.iocoder.yudao.framework.tenant.config;

import cn.iocoder.yudao.framework.security.core.service.SecurityFrameworkService;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import java.util.List;
import java.util.Set;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.WithItem;
public class MyTenantLineInnerInterceptor extends TenantLineInnerInterceptor {
    TenantProperties properties;

    SecurityFrameworkService securityFrameworkService;


    public MyTenantLineInnerInterceptor(final TenantLineHandler tenantLineHandler,TenantProperties properties
    ,SecurityFrameworkService securityFrameworkService) {
        super(tenantLineHandler);
        this.properties=properties;
        this.securityFrameworkService=securityFrameworkService;

    }

    protected void processSelect(Select select, int index, String sql, Object obj) {
        String whereSegment = (String) obj==null?"":(String) obj;
        Set<String> ignoreTables = properties.getIgnoreTables();
        if (select.getSelectBody() instanceof PlainSelect) {
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            String mainTable=plainSelect.getFromItem().toString();
            Expression originalWhere = plainSelect.getWhere();
            //管理员看所有租户数据，但租户状态必须是正常状态
            if(!ignoreTables.contains(mainTable)&&securityFrameworkService.hasPermission("super:user")){
                Expression expression=null;
                try {
                    expression = CCJSqlParserUtil.parseCondExpression("tenant_id IN (SELECT id from ct.system_tenant st where st.status=0 and expire_time >now())");
                } catch (JSQLParserException e) {
                    throw new RuntimeException(e);
                }
                if (originalWhere == null) {
                    plainSelect.setWhere(expression);
                } else {
                    plainSelect.setWhere(new AndExpression(originalWhere, expression));
                }
            }
        }
        this.processSelectBody(select.getSelectBody(), whereSegment);
        List<WithItem> withItemsList = select.getWithItemsList();
        if (!CollectionUtils.isEmpty(withItemsList)) {
            withItemsList.forEach((withItem) -> {
                this.processSelectBody(withItem, whereSegment);
            });
        }
    }

}

3、系统租户如何操作其他租户数据

public static void execute(Long tenantId, Runnable runnable) {
        Long oldTenantId = TenantContextHolder.getTenantId();
        Boolean oldIgnore = TenantContextHolder.isIgnore();
        try {
            TenantContextHolder.setTenantId(tenantId);
            TenantContextHolder.setIgnore(false);
            // 执行逻辑
            runnable.run();
        } finally {
            TenantContextHolder.setTenantId(oldTenantId);
            TenantContextHolder.setIgnore(oldIgnore);
        }
    }