ruoyi若依框架@DataScope注解使用以及碰到的一些问题

1️⃣、项目使用场景:

根据不同角色的登录用户,查看不同的列表数据

2️⃣、思路

因为使用的是ruoyi若依框架,所以第一个想到的就是使用@DataScope注解,用来控制数据权限的

3️⃣、具体使用方法

使用起来很简单:
1、在你的实现类的方法上加上该注解 @DataScope(deptAlias = “d”)

	@Override
    @DataScope(deptAlias = "d")
    public List<BackupQueueManage> selectBackupQueueManageList(BackupQueueManage backupQueueManage) {
        List<BackupQueueManage> backupQueueManages = backupQueueManageMapper.selectBackupQueueManageList(backupQueueManage);
        for (BackupQueueManage manage : backupQueueManages) {
            String linkScriptId = manage.getLinkScriptId();
            if(StringUtils.isBlank(linkScriptId)){
                linkScriptId = configService.selectConfigByKey(DB2_BACKUP_SCRIPT);
            }
            ......
        }
        return backupQueueManages;
    }

2、在mybatis的xml文件中加上 ${params.dataScope}


<sql id="selectBackupQueueManageVo">
        select queue_id, queue_name from backup_queue_manage d
    </sql>
<
select id="selectBackupQueueManageList" parameterType="BackupQueueManage" resultMap="BackupQueueManageResult">
        <include refid="selectBackupQueueManageVo"/>
        <where>  
            <if test="queueName != null  and queueName != ''"> and queue_name like concat('%', #{queueName}, '%')</if>
            <if test="scheduleType != null  and scheduleType != ''"> and schedule_type = #{scheduleType}</if>
            <if test="executorTime != null  and executorTime != ''"> and executor_time = #{executorTime}</if>
            <if test="dbIds != null  and dbIds != ''"> and db_ids = #{dbIds}</if>
            <if test="jobId != null "> and job_id = #{jobId}</if>
            <if test="isOpen != null  and isOpen != ''"> and is_open = #{isOpen}</if>
            <if test="dbType != null  and dbType != ''"> and db_type = #{dbType}</if>

            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
    </select>

4️⃣、遇到的问题

问题1、我们访问页面的时候,发现如下报错信息

Error querying database. Cause: java.sql.SQLSyntaxErrorException: Unknown column 'd.dept_id' in 'IN/ALL/ANY subquery' 
The error may exist in URL [jar:file:/opt/hidata/hidbm-vue/paas.jar!/BOOT-INF/classes!/mapper/BackupQueueManageMapper.xml] ### 
The error may involve com.hidata.devops.selfops.mapper.BackupQueueManageMapper.selectBackupQueueManageList-Inline ### The error occurred while setting parameters 
SQL: SELECT count(0) FROM backup_queue_manage WHERE (d.dept_id IN (SELECT dept_id FROM sys_dept WHERE dept_id = 100 OR find_in_set(NULL, ancestors)))
 ### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'd.dept_id' in 'IN/ALL/ANY subquery' ; bad SQL grammar []; 
nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'd.dept_id' in 'IN/ALL/ANY subquery'

原因是因为:我们的表中没有加dept_id字段

问题2、报错信息如下:

 Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN 
( SELECT dept_id FROM sys_dept WHERE dept_id = 101 or find_in_set( 101 , ance' at line 4  The error may exist in URL
 [jar:file:/opt/hidata/hidbm-vue/paas.jar!/BOOT-INF/classes!/mapper/BackupQueueManageMapper.xml]  The error may involve
 com.hidata.devops.selfops.mapper.BackupQueueManageMapper.selectBackupQueueManageList-Inline  The error occurred while 
 setting parameters  SQL: select count(0) from ( select queue_id, queue_name, schedule_type, executor_time, db_ids, job_id, create_time, is_open,link_script_id
 ,db_type, create_by, update_time, update_by from backup_queue_manage WHERE (.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = 101 or find_in_set( 101 , ancestors ) )) ) tmp_count  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN ( SELECT dept_id FROM sys_dept WHERE dept_id = 101 or find_in_set( 101 , ance' at line 4 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for 
the right syntax to use near 'IN ( SELECT dept_id FROM sys_dept WHERE dept_id = 101 or find_in_set( 101 , ance' at line 4

通过报错信息,我们发现这么一句 WHERE (.dept_id ,说明sql语句中没有加别名,只要加上即可:select queue_id, queue_name from backup_queue_manage d

5️⃣、若依前台配置用户对应的角色权限

(1)打开角色管理菜单,创建角色,选择该角色可以查看的菜单权限
在这里插入图片描述
(2)、分配该角色对应的数据权限
在这里插入图片描述
(3)、新增用户,并关联你的角色
在这里插入图片描述

6️⃣、若依@DataScope注解底层代码实现

(1)mysql表sys_role中,有一个data_scope字段,该字段为角色对应的数据权限标识
在这里插入图片描述
(2)dataScope表示的含义

 /**
     * 全部数据权限
     */
    public static final String DATA_SCOPE_ALL = "1";

    /**
     * 自定数据权限
     */
    public static final String DATA_SCOPE_CUSTOM = "2";

    /**
     * 部门数据权限
     */
    public static final String DATA_SCOPE_DEPT = "3";

    /**
     * 部门及以下数据权限
     */
    public static final String DATA_SCOPE_DEPT_AND_CHILD = "4";

(3)后台根据登录用户角色的dataScope动态的拼接sql语句,来进行数据范围的过滤

 public static void dataScopeFilter(JoinPoint joinPoint, SysUser user, String alias)
    {
        StringBuilder sqlString = new StringBuilder();

        for (SysRole role : user.getRoles())
        {
            String dataScope = role.getDataScope();
            if (DATA_SCOPE_ALL.equals(dataScope))
            {
                sqlString = new StringBuilder();
                break;
            }
            else if (DATA_SCOPE_CUSTOM.equals(dataScope))
            {
                sqlString.append(StringUtils.format(
                        " OR {}.dept_id IN ( SELECT dept_id FROM sys_role_dept WHERE role_id = {} ) ", alias,
                        role.getRoleId()));
            }
            else if (DATA_SCOPE_DEPT.equals(dataScope))
            {
                sqlString.append(StringUtils.format(" OR {}.dept_id = {} ", alias, user.getDeptId()));
            }
            else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope))
            {
                String deptChild = user.getDept().getParentId() + "," + user.getDeptId();
                sqlString.append(StringUtils.format(
                        " OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or ancestors LIKE '%{}%' )",
                        alias, user.getDeptId(), deptChild));
            }
        }

        if (StringUtils.isNotBlank(sqlString.toString()))
        {
            BaseEntity baseEntity = (BaseEntity) joinPoint.getArgs()[0];
            baseEntity.getParams().put(DATA_SCOPE, " AND (" + sqlString.substring(4) + ")");
        }
    }

(4)、整体逻辑,是采用面向切面编程的思想(AOP),通过注解式开发,来达到数据过滤的作用,关键核心代码在DataScopeAspect类中,具体如下:

package com.ruoyi.framework.aspectj;

import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.Signature;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import com.ruoyi.common.annotation.DataScope;
import com.ruoyi.common.core.domain.BaseEntity;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.framework.util.ShiroUtils;
import com.ruoyi.system.domain.SysRole;
import com.ruoyi.system.domain.SysUser;

/**
 * 数据过滤处理
 * 
 * @author ruoyi
 */
@Aspect
@Component
public class DataScopeAspect
{
    /**
     * 全部数据权限
     */
    public static final String DATA_SCOPE_ALL = "1";

    /**
     * 自定数据权限
     */
    public static final String DATA_SCOPE_CUSTOM = "2";

    /**
     * 部门数据权限
     */
    public static final String DATA_SCOPE_DEPT = "3";

    /**
     * 部门及以下数据权限
     */
    public static final String DATA_SCOPE_DEPT_AND_CHILD = "4";

    /**
     * 数据权限过滤关键字
     */
    public static final String DATA_SCOPE = "dataScope";

    // 配置织入点
    @Pointcut("@annotation(com.ruoyi.common.annotation.DataScope)")
    public void dataScopePointCut()
    {
    }

    @Before("dataScopePointCut()")
    public void doBefore(JoinPoint point) throws Throwable
    {
        handleDataScope(point);
    }

    protected void handleDataScope(final JoinPoint joinPoint)
    {
        // 获得注解
        DataScope controllerDataScope = getAnnotationLog(joinPoint);
        if (controllerDataScope == null)
        {
            return;
        }
        // 获取当前的用户
        SysUser currentUser = ShiroUtils.getSysUser();
        if (currentUser != null)
        {
            // 如果是超级管理员,则不过滤数据
            if (!currentUser.isAdmin())
            {
                dataScopeFilter(joinPoint, currentUser, controllerDataScope.tableAlias());
            }
        }
    }

    /**
     * 数据范围过滤
     * 
     * @param joinPoint 切点
     * @param user 用户
     * @param alias 别名
     */
    public static void dataScopeFilter(JoinPoint joinPoint, SysUser user, String alias)
    {
        StringBuilder sqlString = new StringBuilder();

        for (SysRole role : user.getRoles())
        {
            String dataScope = role.getDataScope();
            if (DATA_SCOPE_ALL.equals(dataScope))
            {
                sqlString = new StringBuilder();
                break;
            }
            else if (DATA_SCOPE_CUSTOM.equals(dataScope))
            {
                sqlString.append(StringUtils.format(
                        " OR {}.dept_id IN ( SELECT dept_id FROM sys_role_dept WHERE role_id = {} ) ", alias,
                        role.getRoleId()));
            }
            else if (DATA_SCOPE_DEPT.equals(dataScope))
            {
                sqlString.append(StringUtils.format(" OR {}.dept_id = {} ", alias, user.getDeptId()));
            }
            else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope))
            {
                String deptChild = user.getDept().getParentId() + "," + user.getDeptId();
                sqlString.append(StringUtils.format(
                        " OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or ancestors LIKE '%{}%' )",
                        alias, user.getDeptId(), deptChild));
            }
        }

        if (StringUtils.isNotBlank(sqlString.toString()))
        {
            BaseEntity baseEntity = (BaseEntity) joinPoint.getArgs()[0];
            baseEntity.getParams().put(DATA_SCOPE, " AND (" + sqlString.substring(4) + ")");
        }
    }

    /**
     * 是否存在注解,如果存在就获取
     */
    private DataScope getAnnotationLog(JoinPoint joinPoint)
    {
        Signature signature = joinPoint.getSignature();
        MethodSignature methodSignature = (MethodSignature) signature;
        Method method = methodSignature.getMethod();

        if (method != null)
        {
            return method.getAnnotation(DataScope.class);
        }
        return null;
    }
}

  • 1
    点赞
  • 8
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:1024 设计师:我叫白小胖 返回首页
评论

打赏作者

飞翔的佩奇

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值