[错误报告]: 使用数据权限插件后，每个left join on后面都错误拼接了要的查询条件

[linmiao911]

确认

• [X] 我使用的版本是最新版, 并且使用插件确认过项目里无依赖版本冲突
• [X] 我已经在 issue 中搜索过, 确认问题没有被提出过
• [X] 我已经修改标题, 将标题中的 描述 替换为遇到的问题

当前程序版本

3.5.7

问题描述

数据权限插件校验后没有正确出现在where后

自定义插件如下 `

public class CustomDataPermissionHandler implements MultiDataPermissionHandler {

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

@Override
public Expression getSqlSegment(Table table, Expression where, String mappedStatementId) {
    try {
        Class<?> clazz = Class.forName(mappedStatementId.substring(0, mappedStatementId.lastIndexOf(StringPool.DOT)));
        String methodName = mappedStatementId.substring(mappedStatementId.lastIndexOf(StringPool.DOT) + 1);
        Method[] methods = clazz.getDeclaredMethods();
        for (Method method : methods) {
            if (method.getName().equals(methodName)) {
                DataScope annotation = method.getAnnotation(DataScope.class);
                // 如果没有注解或者是超级管理员，直接返回
                if (annotation == null || SecurityUtil.isRoot()) {
                    return null;
                }

                return CCJSqlParserUtil.parseCondExpression("1=1");

                // return dataScopeFilter(annotation.companyAlias(), annotation.deptAlias(), annotation.userAlias(), where);
            }

        }

    } catch (ClassNotFoundException | JSQLParserException e) {
        throw new RuntimeException(e);
    }

    return null;
}

/**
 * 数据权限过滤
 * @param companyAlias 公司表的别名
 * @param deptAlias 部门表的别名
 * @param userAlias 用户表的别名
 * @param where     原始条件
 * @return 结果
 */
@SneakyThrows
public static Expression dataScopeFilter( String companyAlias, String deptAlias, String userAlias, Expression where) {
    // 获取当前用户的数据权限
    Integer dataScope = Objects.requireNonNull(SecurityUtil.getUser()).getDataScope();
    DataScopeEnum dataScopeEnum = IBaseEnum.getEnumByValue(dataScope, DataScopeEnum.class);

    Long deptId, companyId, userId = null;
    String appendSqlStr = null;
    switch (dataScopeEnum) {
        case ALL_DATA:
            return null;
        case DATA_SCOPE_COMPANY:
            companyId = SecurityUtil.getCompanyId();
            appendSqlStr = companyAlias + StringPool.EQUALS + companyId;
            break;
        case DATA_SCOPE_DEPT_ALL:
            deptId = SecurityUtil.getDeptId();
            appendSqlStr = deptAlias + " IN ( SELECT id FROM sys_dept WHERE id = " + deptId + " OR FIND_IN_SET( " + deptId + " , tree_path ) )";
            break;
        case DATA_SCOPE_DEPT:
            deptId = SecurityUtil.getDeptId();
            appendSqlStr = deptAlias + StringPool.EQUALS + deptId;
            break;
        case DATA_SCOPE_OWNER:
            userId = SecurityUtil.getUserId();
            appendSqlStr = userAlias + StringPool.EQUALS + userId;
            break;
        default:
            throw new RuntimeException("数据权限异常");
    }

    if (StrUtil.isBlank(appendSqlStr)) {
        return null;
    }

    Expression appendExpression = CCJSqlParserUtil.parseCondExpression(appendSqlStr);
    if (where == null) {
        return appendExpression;
    }

    return new AndExpression(where, appendExpression);
}

}

`

mapper.xml如下： `

SELECT sp.id performanceId, c.`name` companyName, e.nickname, d.`name` deptName, sp.custom_name, dp.`name` payName, sp.amount, sp.remark, sp.create_time FROM sys_performance sp LEFT JOIN dict_payment dp ON dp.id=sp.pay_id LEFT JOIN sys_employee e ON sp.employee_id = e.id LEFT JOIN sys_company c ON c.id=e.company_id LEFT JOIN sys_dept d ON e.dept_id = d.id sp.del_flag=1 AND e.nickname LIKE CONCAT(#{req.nickname}, '%') AND sp.custom_name LIKE CONCAT(#{req.customName}, '%') GROUP BY sp.id ORDER BY sp.create_time DESC

`

错误 虽然where条件后同样拼接了查询条件，但是每个外连接的后面同样出现了条件，项目使用mp3.5.7 , druid-spring-boot-starter1.2.23, springboot 3.3.3

详细堆栈日志

2024-09-18 17:06:29.256 [DEBUG] [,] 20640 --- [  XNIO-1 task-2] com.baomidou.mybatisplus.extension.plugins.inner.DataPermissionInterceptor (processParser)[85]: SQL to parse, SQL: SELECT
            sp.id performanceId,
            c.`name` companyName,
            e.nickname,
            d.`name` deptName,
            sp.custom_name,
            dp.`name` payName,
            sp.amount,
            sp.remark,
            sp.create_time
        FROM
            sys_performance sp
                LEFT JOIN dict_payment dp ON dp.id=sp.pay_id
                LEFT JOIN sys_employee e ON sp.employee_id = e.id
                LEFT JOIN sys_company c ON c.id=e.company_id
                LEFT JOIN sys_dept d ON e.dept_id = d.id
         WHERE sp.del_flag=1 
        GROUP BY sp.id
        ORDER BY sp.create_time DESC LIMIT ?
2024-09-18 17:06:29.256 [DEBUG] [,] 20640 --- [  XNIO-1 task-2] com.baomidou.mybatisplus.extension.plugins.inner.DataPermissionInterceptor (processParser)[98]: parse the finished SQL: SELECT sp.id performanceId, c.`name` companyName, e.nickname, d.`name` deptName, sp.custom_name, dp.`name` payName, sp.amount, sp.remark, sp.create_time FROM sys_performance sp LEFT JOIN dict_payment dp ON dp.id = sp.pay_id AND 1 = 1 LEFT JOIN sys_employee e ON sp.employee_id = e.id AND 1 = 1 LEFT JOIN sys_company c ON c.id = e.company_id AND 1 = 1 LEFT JOIN sys_dept d ON e.dept_id = d.id AND 1 = 1 WHERE sp.del_flag = 1 AND 1 = 1 GROUP BY sp.id ORDER BY sp.create_time DESC LIMIT ?
==>  Preparing: SELECT sp.id performanceId, c.`name` companyName, e.nickname, d.`name` deptName, sp.custom_name, dp.`name` payName, sp.amount, sp.remark, sp.create_time FROM sys_performance sp LEFT JOIN dict_payment dp ON dp.id = sp.pay_id AND 1 = 1 LEFT JOIN sys_employee e ON sp.employee_id = e.id AND 1 = 1 LEFT JOIN sys_company c ON c.id = e.company_id AND 1 = 1 LEFT JOIN sys_dept d ON e.dept_id = d.id AND 1 = 1 WHERE sp.del_flag = 1 AND 1 = 1 GROUP BY sp.id ORDER BY sp.create_time DESC LIMIT ?
==> Parameters: 10(Long)
<==    Columns: performanceId, companyName, nickname, deptName, custom_name, payName, amount, remark, create_time
2024-09-18 17:06:29.257 [ INFO] [,] 20640 --- [  XNIO-1 task-2] p6spy (logSQL)[60]: #1726650389257 | took 0ms | statement | connection 2| url jdbc:mysql://127.0.0.1:3306/shengtian_manager?autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&failOverReadOnly=false&allowMultiQueries=true
SELECT sp.id performanceId, c.`name` companyName, e.nickname, d.`name` deptName, sp.custom_name, dp.`name` payName, sp.amount, sp.remark, sp.create_time FROM sys_performance sp LEFT JOIN dict_payment dp ON dp.id = sp.pay_id AND 1 = 1 LEFT JOIN sys_employee e ON sp.employee_id = e.id AND 1 = 1 LEFT JOIN sys_company c ON c.id = e.company_id AND 1 = 1 LEFT JOIN sys_dept d ON e.dept_id = d.id AND 1 = 1 WHERE sp.del_flag = 1 AND 1 = 1 GROUP BY sp.id ORDER BY sp.create_time DESC LIMIT ?
SELECT sp.id performanceId, c.`name` companyName, e.nickname, d.`name` deptName, sp.custom_name, dp.`name` payName, sp.amount, sp.remark, sp.create_time FROM sys_performance sp LEFT JOIN dict_payment dp ON dp.id = sp.pay_id AND 1 = 1 LEFT JOIN sys_employee e ON sp.employee_id = e.id AND 1 = 1 LEFT JOIN sys_company c ON c.id = e.company_id AND 1 = 1 LEFT JOIN sys_dept d ON e.dept_id = d.id AND 1 = 1 WHERE sp.del_flag = 1 AND 1 = 1 GROUP BY sp.id ORDER BY sp.create_time DESC LIMIT 10;
<==        Row: 3, 测试公司1, 员工账号2, 测试部门1, 员工测试录入, 测试支付, 10568.68, 员工测试录入, 2024-09-18 12:40:35
<==        Row: 2, null, 超级管理员, null, 测试用户, 测试支付, 1000.99, 测试用户, 2024-09-18 02:36:13
<==        Row: 1, 测试公司1, 员工账号1, 测试部门1, 张三, 微信扫码支付, 100.79, 测试, 2024-09-17 21:24:06
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@45114f59]

[linmiao911]

好吧，我不应该使用MultiDataPermissionHandler，替换为DataPermissionHandler后正常