需求

基于登录用户权限，过滤查询数据，区分租户、主体、个人

实现

自定义拦截器

public class PermissionQueryInterceptor implements InnerInterceptor {

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds,
                            ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        try {
            //上下文用户信息
            UserVO vo = UserContext.get();
            if (vo == null) {
                return;
            }
            String tenantId = vo.getTenantId();
            String mainId = vo.getMainId();
            String createUserId = vo.getUserId();
            Integer permissionLevel = vo.getPremissLevel() == null ? PermissionEnum.PublicUser.getCode() : vo.getPremissLevel();
            // 检查是否需要添加权限条件
            if (tenantId == null && mainId == null && createUserId == null) {
                return;
            }
            // 处理SQL
            processSql(boundSql, tenantId, mainId, createUserId, permissionLevel);
        } catch (Exception e) {
            // 记录异常但不中断查询，避免因权限拦截器问题导致业务异常
            log.warn("权限拦截器处理SQL时发生异常，将跳过权限控制: {}", e.getMessage(), e);
        } finally {
            //可手动设置忽略条件，如果想做细一点，对每条sql都过滤，加在这，不然就外面set后再clear
            PermissionContext.clear();
        }
    }

    /**
     * 处理SQL，添加权限控制条件
     *
     * @param boundSql  SQL绑定对象
     * @param tenantId  租户ID
     * @param mainId    主体ID
     * @throws JSQLParserException SQL解析异常
     */
    private void processSql(BoundSql boundSql, String tenantId, String mainId, String createUserId, Integer permissionLevel) throws JSQLParserException {
        String originalSql = boundSql.getSql();
        if (originalSql == null || originalSql.trim().isEmpty()) {
            return;
        }
        // 解析SQL
        Statement statement = CCJSqlParserUtil.parse(originalSql);
        if (!(statement instanceof Select select)) {
            return; // 非查询语句不处理
        }
        SelectBody selectBody = select.getSelectBody();
        // 处理普通SELECT语句
        if (selectBody instanceof PlainSelect) {
            processPlainSelect((PlainSelect) selectBody, tenantId, mainId, createUserId, permissionLevel);
            PluginUtils.mpBoundSql(boundSql).sql(select.toString());
        }
        // 处理UNION等复合查询
        else if (selectBody instanceof SetOperationList setOperationList) {
            List<SelectBody> selectBodyList = setOperationList.getSelects();
            for (SelectBody body : selectBodyList) {
                if (body instanceof PlainSelect) {
                    processPlainSelect((PlainSelect) body, tenantId, mainId, createUserId, permissionLevel);
                }
            }
            PluginUtils.mpBoundSql(boundSql).sql(select.toString());
        }
    }

    /**
     * 处理普通SELECT语句
     *
     * @param plainSelect SELECT语句对象
     * @param tenantId    租户ID
     * @param mainId      主体ID
     */
    private void processPlainSelect(PlainSelect plainSelect, String tenantId, String mainId, String createUserId, Integer permissionLevel) {
        try {
        	 	//处理join的情况
            List<Table> tables = getAllTables(plainSelect);
            Expression permissionCondition = buildPermissionCondition(tenantId, mainId, createUserId, permissionLevel, tables);
            if (permissionCondition == null) {
                return;
            }
            // 如果没有WHERE条件，直接设置
            if (plainSelect.getWhere() == null) {
                plainSelect.setWhere(permissionCondition);
            } else {
                // 如果已有WHERE条件，用AND连接
                AndExpression andExpression = new AndExpression(
                        new Parenthesis(plainSelect.getWhere()),
                        permissionCondition
                );
                plainSelect.setWhere(andExpression);
            }
        } catch (Exception e) {
            log.warn("处理PlainSelect时发生异常: {}", e.getMessage());
            // 发生异常时不修改原SQL，保证查询可以正常执行
        }
    }

    /**
     * 构建权限控制条件表达式
     *
     * @param tenantId 租户ID
     * @param mainId   主体ID
     * @return 权限条件表达式
     */
    private Expression buildPermissionCondition(String tenantId, String mainId, String createUserId, Integer permissionLevel, List<Table> tables) {
        Expression result = null;
        try {
            boolean withTenantId = false;
            boolean withMain = false;
            boolean withCreateUser = false;
            //根据自己的逻辑判断用户权限需要过滤哪些
            ......
            //遍历表添加过滤条件
            for (Table table : tables){
                String tableNameWithAlias = getTableNameWithAlias(table);
                // 构建租户条件
                if (PermissionContext.tenantSearch() && StrUtil.isNotBlank(tenantId)) {
                    EqualsTo tenantCondition = new EqualsTo();
                    tenantCondition.setLeftExpression(new Column(tableNameWithAlias + "." + UserConstant.TENANT_ID_KEY));
                    tenantCondition.setRightExpression(new StringValue(tenantId));
                    if(result == null){
                        result = tenantCondition;
                    }else{
                        result = new AndExpression(result, tenantCondition);
                    }
                }
                // 构建主体条件
                if (withMain && PermissionContext.mainSearch() && StrUtil.isNotBlank(mainId)) {
                    // 使用IN条件: main_id IN (mainId, Constant.EMPTY_MAIN_ID)
                    String mainConditionStr = String.format("%s IN ('%s', '%s')", tableNameWithAlias + "." + UserConstant.MAIN_ID_KEY, mainId, Constant.EMPTY_MAIN_ID);
                    Expression mainCondition = CCJSqlParserUtil.parseCondExpression(mainConditionStr);
                    if (result == null) {
                        result = mainCondition;
                    } else {
                        result = new AndExpression(result, mainCondition);
                    }
                }
                if(withCreateUser && PermissionContext.createUserSearch() && StrUtil.isNotBlank(createUserId)){
                    EqualsTo createUserIdCondition = new EqualsTo();
                    createUserIdCondition.setLeftExpression(new Column(tableNameWithAlias + "." + UserConstant.CREATE_USER_ID_KEY));
                    createUserIdCondition.setRightExpression(new StringValue(createUserId));
                    if (result == null) {
                        result = createUserIdCondition;
                    } else {
                        result = new AndExpression(result, createUserIdCondition);
                    }
                }
            }
        } catch (Exception e) {
            log.warn("构建权限条件时发生异常: {}", e.getMessage());
            return null;
        }
        return result;
    }

    @Override
    public void setProperties(Properties properties) {
        // 可以接收配置参数
    }

    private List<Table> getAllTables(PlainSelect plainSelect) {
        List<Table> tables = new ArrayList<>();
        try {
            // 添加主表
            FromItem fromItem = plainSelect.getFromItem();
            if (fromItem instanceof Table) {
                tables.add((Table) fromItem);
            }
            // 添加JOIN表
            List<Join> joins = plainSelect.getJoins();
            if (joins != null) {
                for (Join join : joins) {
                    FromItem rightItem = join.getRightItem();
                    if (rightItem instanceof Table) {
                        tables.add((Table) rightItem);
                    }
                }
            }
        } catch (Exception e) {
            log.warn("获取所有表时发生异常: {}", e.getMessage(), e);
        }
        return tables;
    }

    /**
     * 获取带别名的表名
     * @param table 表对象
     * @return 表名（如果有别名则使用别名）
     */
    private String getTableNameWithAlias(Table table) {
        if (table.getAlias() != null && table.getAlias().getName() != null) {
            return table.getAlias().getName();
        }
        return table.getName();
    }

}

注册拦截器：这里需要注意下，如果使用了分页插件或者其他会生成sql的条件，这个拦截器放在他们前面注册，否则拦截不到分页语句

@Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //优先添加 否则count语句走不到这个过滤器
        interceptor.addInnerInterceptor(new PermissionQueryInterceptor());
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(getDatabaseType()));
        return interceptor;
    }

上下文：

/**
 * @author zfl
 */
public class PermissionContext {

    private static final ThreadLocal<Boolean> TENANT_SEARCH_IGNORE = new ThreadLocal<>();

    private static final ThreadLocal<Boolean> MAIN_SEARCH_IGNORE = new ThreadLocal<>();

    private static final ThreadLocal<Boolean> CREATE_USER_SEARCH_IGNORE = new ThreadLocal<>();

    public static void ignoreTenantSearch() {
        TENANT_SEARCH_IGNORE.set(true);
    }

    public static void ignoreMainSearch() {
        MAIN_SEARCH_IGNORE.set(true);
    }

    public static void ignoreCreateUserSearch() {
        CREATE_USER_SEARCH_IGNORE.set(true);
    }

    public static Boolean tenantSearch() {
        return TENANT_SEARCH_IGNORE.get() == null || !TENANT_SEARCH_IGNORE.get();
    }

    public static Boolean mainSearch() {
        return MAIN_SEARCH_IGNORE.get() == null || !MAIN_SEARCH_IGNORE.get();
    }

    public static Boolean createUserSearch() {
        return CREATE_USER_SEARCH_IGNORE.get() == null || !CREATE_USER_SEARCH_IGNORE.get();
    }

    public static void clear() {
        TENANT_SEARCH_IGNORE.remove();
        MAIN_SEARCH_IGNORE.remove();
        CREATE_USER_SEARCH_IGNORE.remove();
    }
}