跳至主要內容

mybatisPlus

wangdx大约 6 分钟

参考网站

Mybatis-plus 数据权限 DataPermissionInterceptor 实现open in new window

数据权限

源码分析

  1. 继承抽象类 JsqlParserSupport 并重写 processSelect 方法。JSqlParser 是一个 SQL 语句解析器，它将 SQL 转换为 Java 类的可遍历层次结构。plus 中也引入了 JSqlParser 包，processSelect 可以对 Select 语句进行处理。
  2. 实现 InnerInterceptor 接口并重写 beforeQuery 方法。InnerInterceptor 是 plus 的插件接口，beforeQuery 可以对查询语句执行前进行处理。
  3. DataPermissionHandler 作为数据权限处理器，是一个接口，提供 getSqlSegment 方法添加数据权限 SQL 片段。
  4. 由上可知，我们只需要实现 DataPermissionHandler 接口，并按照业务规则处理 SQL，就可以实现数据权限的功能。
  5. DataPermissionInterceptor 为 mybatis-plus 3.4.2 版本以上才有的功能。
package com.baomidou.mybatisplus.extension.plugins.inner;

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.handler.DataPermissionHandler;
import lombok.*;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.SQLException;
import java.util.List;

/**
 * 数据权限处理器
 *
 * @author hubin
 * @since 3.4.1 +
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@SuppressWarnings({"rawtypes"})
public class DataPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor {
    private DataPermissionHandler dataPermissionHandler;

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        if (InterceptorIgnoreHelper.willIgnoreDataPermission(ms.getId())) return;
        PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
        mpBs.sql(parserSingle(mpBs.sql(), ms.getId()));
    }

    @Override
    protected void processSelect(Select select, int index, String sql, Object obj) {
        SelectBody selectBody = select.getSelectBody();
        if (selectBody instanceof PlainSelect) {
            this.setWhere((PlainSelect) selectBody, (String) obj);
        } else if (selectBody instanceof SetOperationList) {
            SetOperationList setOperationList = (SetOperationList) selectBody;
            List<SelectBody> selectBodyList = setOperationList.getSelects();
            selectBodyList.forEach(s -> this.setWhere((PlainSelect) s, (String) obj));
        }
    }

    /**
     * 设置 where 条件
     *
     * @param plainSelect  查询对象
     * @param whereSegment 查询条件片段
     */
    protected void setWhere(PlainSelect plainSelect, String whereSegment) {
        Expression sqlSegment = dataPermissionHandler.getSqlSegment(plainSelect.getWhere(), whereSegment);
        if (null != sqlSegment) {
            plainSelect.setWhere(sqlSegment);
        }
    }
}

使用案例

mybatis-plus 在 gitee 的仓库中，有人询问了如何使用 DataPermissionInterceptor，下面有人给出了例子，一共分为两步，一是实现 dataPermissionHandler 接口，二是将实现添加到 mybstis-plus 的处理器中。他的例子中是根据不同权限类型拼接 sql。

通用的方案是在所有的表中增加权限相关的字段，如部门、门店、租户等。实现 dataPermissionHandler 接口时较方便，可直接添加这几个字段的条件，无需查询数据库。

DATA_SCOPE_CUSTOM

d.dept_id IN (SELECT dept_id FROM sys_role_dept WHERE role_id = 1515)

DATA_SCOPE_DEPT

d.dept_id = 1515

DATA_SCOPE_DEPT_AND_CHILD
ELECT COUNT(*) AS total FROM emp AS t WHERE d.dept_id IN (SELECT dept_id FROM sys_dept WHERE dept_id = 232323 OR find_in_set(232323, ancestors))

DATA_SCOPE_SELF

SELECT COUNT(*) AS total FROM emp AS t WHERE u.create_id = 151515

/**
 * 自定义数据权限
 *
 * @Author PXL
 * @Version 1.0
 * @Date 2021-02-07 16:52
 */
public class DataPermissionHandlerImpl implements DataPermissionHandler {

    @Override
    public Expression getSqlSegment(Expression where, String mappedStatementId) {
        try {
            Class<?> clazz = Class.forName(mappedStatementId.substring(0, mappedStatementId.lastIndexOf(".")));
            String methodName = mappedStatementId.substring(mappedStatementId.lastIndexOf(".") + 1);
            Method[] methods = clazz.getDeclaredMethods();
            for (Method method : methods) {
                DataPermission annotation = method.getAnnotation(DataPermission.class);
                if (ObjectUtils.isNotEmpty(annotation) && (method.getName().equals(methodName) || (method.getName() + "_COUNT").equals(methodName))) {
                    // 获取当前的用户
                    LoginUser loginUser = SpringUtils.getBean(TokenService.class).getLoginUser(ServletUtils.getRequest());
                    if (ObjectUtils.isNotEmpty(loginUser) && ObjectUtils.isNotEmpty(loginUser.getUser()) && !loginUser.getUser().isAdmin()) {
                        return dataScopeFilter(loginUser.getUser(), annotation.value(), where);
                    }
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return where;
    }


    /**
     * 构建过滤条件
     *
     * @param user 当前登录用户
     * @param where 当前查询条件
     * @return 构建后查询条件
     */
    public static Expression dataScopeFilter(SysUser user, String tableAlias, Expression where) {
        Expression expression = null;
        for (SysRole role : user.getRoles()) {
            String dataScope = role.getDataScope();
            if (DataScopeAspect.DATA_SCOPE_ALL.equals(dataScope)) {
                return where;
            }
            if (DataScopeAspect.DATA_SCOPE_CUSTOM.equals(dataScope)) {
                InExpression inExpression = new InExpression();
                inExpression.setLeftExpression(buildColumn(tableAlias, "dept_id"));
                SubSelect subSelect = new SubSelect();
                PlainSelect select = new PlainSelect();
                select.setSelectItems(Collections.singletonList(new SelectExpressionItem(new Column("dept_id"))));
                select.setFromItem(new Table("sys_role_dept"));
                EqualsTo equalsTo = new EqualsTo();
                equalsTo.setLeftExpression(new Column("role_id"));
                equalsTo.setRightExpression(new LongValue(role.getRoleId()));
                select.setWhere(equalsTo);
                subSelect.setSelectBody(select);
                inExpression.setRightExpression(subSelect);
                expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, inExpression) : inExpression;
            }
            if (DataScopeAspect.DATA_SCOPE_DEPT.equals(dataScope)) {
                EqualsTo equalsTo = new EqualsTo();
                equalsTo.setLeftExpression(buildColumn(tableAlias, "dept_id"));
                equalsTo.setRightExpression(new LongValue(user.getDeptId()));
                expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, equalsTo) : equalsTo;
            }
            if (DataScopeAspect.DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope)) {
                InExpression inExpression = new InExpression();
                inExpression.setLeftExpression(buildColumn(tableAlias, "dept_id"));
                SubSelect subSelect = new SubSelect();
                PlainSelect select = new PlainSelect();
                select.setSelectItems(Collections.singletonList(new SelectExpressionItem(new Column("dept_id"))));
                select.setFromItem(new Table("sys_dept"));
                EqualsTo equalsTo = new EqualsTo();
                equalsTo.setLeftExpression(new Column("dept_id"));
                equalsTo.setRightExpression(new LongValue(user.getDeptId()));
                Function function = new Function();
                function.setName("find_in_set");
                function.setParameters(new ExpressionList(new LongValue(user.getDeptId()) , new Column("ancestors")));
                select.setWhere(new OrExpression(equalsTo, function));
                subSelect.setSelectBody(select);
                inExpression.setRightExpression(subSelect);
                expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, inExpression) : inExpression;
            }
            if (DataScopeAspect.DATA_SCOPE_SELF.equals(dataScope)) {
                EqualsTo equalsTo = new EqualsTo();
                equalsTo.setLeftExpression(buildColumn(tableAlias, "create_by"));
                equalsTo.setRightExpression(new StringValue(user.getUserName()));
                expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, equalsTo) : equalsTo;
            }
        }
        return ObjectUtils.isNotEmpty(where) ? new AndExpression(where, new Parenthesis(expression)) : expression;
    }

    /**
     * 构建Column
     *
     * @param tableAlias 表别名
     * @param columnName 字段名称
     * @return 带表别名字段
     */
    public static Column buildColumn(String tableAlias, String columnName) {
        if (StringUtils.isNotEmpty(tableAlias)) {
            columnName = tableAlias + "." + columnName;
        }
        return new Column(columnName);
    }
}

2.

// 自定义数据权限
interceptor.addInnerInterceptor(new DataPermissionInterceptor(new DataPermissionHandlerImpl()));


可以看到DataPermissionHandler 接口使用中，传递来的参数是什么。

参数 含义
where 为当前 sql 已有的 where 条件
mappedStatementId 为 mapper 中定义的方法的路径


@InterceptorIgnore注解
拦截忽略注解 @InterceptorIgnore

属性名	类型	默认值	描述
tenantLine	String	“”	行级租户
dynamicTableName	String	“”	动态表名
blockAttack	String	“”	攻击 SQL 阻断解析器,防止全表更新与删除
illegalSql	String	“”	垃圾SQL拦截

案例

1.
/**
 * @ClassName MyDataPermissionHandler
 * @Description 自定义数据权限处理
 * @Author FangCheng
 * @Date 2022/4/2 14:54
 **/
@Component
public class MyDataPermissionHandler implements DataPermissionHandler {
    @Autowired
    @Lazy
    private UserRepository userRepository;

    @Override
    public Expression getSqlSegment(Expression where, String mappedStatementId) {
        try {
            Class<?> clazz = Class.forName(mappedStatementId.substring(0, mappedStatementId.lastIndexOf(".")));
            String methodName = mappedStatementId.substring(mappedStatementId.lastIndexOf(".") + 1);
            Method[] methods = clazz.getDeclaredMethods();
            for (Method method : methods) {
                if (!methodName.equals(method.getName())) {
                    continue;
                }
                // 获取自定义注解，无此注解则不控制数据权限
                CustomDataPermission annotation = method.getAnnotation(CustomDataPermission.class);
                if (annotation == null) {
                    continue;
                }
                // 自定义的用户上下文，获取到用户的id
                ContextUserDetails contextUserDetails = UserDetailsContextHolder.getContextUserDetails();
                String userId = contextUserDetails.getId();
                User user = userRepository.selectUserById(userId);
                // 如果是特权用户，不控制数据权限
                if (Constants.ADMIN_RULE == user.getAdminuser()) {
                    return where;
                }
                // 员工用户
                if (UserTypeEnum.USER_TYPE_EMPLOYEE.getCode().equals(user.getUsertype())) {
                	// 员工用户的权限字段
                    String field = annotation.field().getValue();
                    // 单据类型
                    String billType = annotation.billType().getFuncno();
                    // 操作类型
                    OperationTypeEnum operationType = annotation.operation();
                    // 权限字段为空则为不控制数据权限
                    if (StringUtils.isNotEmpty(field)) {
                        List<DataPermission> dataPermissions = userRepository.selectUserFuncnoDataPermission(userId, billType);
                        if (dataPermissions.size() == 0) {
                            // 没数据权限，但有功能权限则取所有数据
                            return where;
                        }
                        // 构建in表达式
                        InExpression inExpression = new InExpression();
                        inExpression.setLeftExpression(new Column(field));
                        List<Expression> conditions = null;
                        switch(operationType) {
                            case SELECT:
                                conditions = dataPermissions.stream().map(res -> new StringValue(res.getStkid())).collect(Collectors.toList());
                                break;
                            case INSERT:
                                conditions = dataPermissions.stream().filter(DataPermission::isAddright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList());
                                break;
                            case UPDATE:
                                conditions = dataPermissions.stream().filter(DataPermission::isModright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList());
                                break;
                            case APPROVE:
                                conditions = dataPermissions.stream().filter(DataPermission::isCheckright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList());
                                break;
                            default:
                                break;
                        }
                        if (conditions == null) {
                            return where;
                        }
                        conditions.add(new StringValue(Constants.ALL_STORE));
                        ItemsList itemsList = new ExpressionList(conditions);
                        inExpression.setRightItemsList(itemsList);
                        if (where == null) {
                            return inExpression;
                        }
                        return new AndExpression(where, inExpression);

                    } else {
                        return where;
                    }
                } else {
               		 // 供应商用户的权限字段
                    String field = annotation.vendorfield().getValue();
                    if (StringUtils.isNotEmpty(field)) {
                    	// 供应商如果控制权限，则只能看到自己的单据。直接使用EqualsTo
                        EqualsTo equalsTo = new EqualsTo();
                        equalsTo.setLeftExpression(new Column(field));
                        equalsTo.setRightExpression(new StringValue(userId));
                        if (where == null) {
                            return equalsTo;
                        }
                        // 创建 AND 表达式 拼接Where 和 = 表达式
                        return new AndExpression(where, equalsTo);
                    } else {
                        return where;
                    }
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return where;
    }
}

2.

/**
 * @ClassName MybatisConfig
 * @Description mybatis配置
 * @Author FangCheng
 * @Date 2022/4/2 15:32
 **/
@Configuration
public class MybatisConfig {
    @Autowired
    private MyDataPermissionHandler myDataPermissionHandler;
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        // 添加数据权限插件
        DataPermissionInterceptor dataPermissionInterceptor = new DataPermissionInterceptor();
        // 添加自定义的数据权限处理器
        dataPermissionInterceptor.setDataPermissionHandler(myDataPermissionHandler);
        interceptor.addInnerInterceptor(dataPermissionInterceptor);
        // 分页插件
        //interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.SQL_SERVER));
        return interceptor;
    }
}

3.
/**
 * @ClassName CustomDataPermission
 * @Description 自定义数据权限注解
 * @Author FangCheng
 * @Date 2022/4/6 10:24
 **/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface CustomDataPermission {
    PermissionFieldEnum field();
    PermissionFieldEnum vendorfield();
    BillTypeEnum billType();
    OperationTypeEnum operation();
}
4.

/**
 * @ClassName ApplyHMapper
 * @Description 维修申请单主表
 * @Author FangCheng
 * @Date 2022/4/6 13:06
 **/
@Mapper
public interface ApplyHMapper extends BaseMapper<ApplyHPo> {
    /**
     * @Description:
     * @author FangCheng
     * @Date: 2022/4/1 15:26
     * @methodName selectApplyHs
     */
    @CustomDataPermission(field = PermissionFieldEnum.FIELD_STKID,
            vendorfield = PermissionFieldEnum.FIELD_EMPTY,
            billType = BillTypeEnum.APPLY_BILL,
            operation = OperationTypeEnum.SELECT)
    Page<ApplyHPo> selectApplyHs(IPage<ApplyHPo> page, @Param(Constants.WRAPPER) QueryWrapper<ApplyHPo> queryWrapper);

    /**
     * @Description:
     * @author FangCheng
     * @Date: 2022/4/1 15:26
     * @methodName selectApplyHsForVendor
     */
    Page<ApplyHPo> selectApplyHsForVendor(IPage<ApplyHPo> page, @Param("vendorid") String vendorid, @Param(Constants.WRAPPER) QueryWrapper<ApplyHPo> queryWrapper);

    /**
     * @Description:
     * @author FangCheng
     * @Date: 2022/4/1 15:26
     * @methodName selectApplyH
     */
    @CustomDataPermission(field = PermissionFieldEnum.FIELD_STKID,
            vendorfield = PermissionFieldEnum.FIELD_EMPTY,
            billType = BillTypeEnum.APPLY_BILL,
            operation = OperationTypeEnum.SELECT)
    ApplyHPo selectApplyH(@Param("billNo") String billNo);

    /**
     * @Description:
     * @author FangCheng
     * @Date: 2022/4/1 15:26
     * @methodName selectApplyH
     */
    @InterceptorIgnore
    ApplyHPo selectApplyHNoPermission(@Param("billNo") String billNo);

    /**
     * @Description:
     * @author FangCheng
     * @Date: 2022/4/1 15:26
     * @methodName saveApplyH
     */
    void saveApplyH(ApplyHPo applyHPo);
}
上次编辑于:
MIT Licensed | Copyright © 2024-present WDX