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

飞翔的佩奇

已于 2022-08-03 16:42:13 修改

阅读量9.8k

点赞数 7

分类专栏：随手记文章标签： mybatis java 数据库 spring ruoyi

于 2022-08-02 14:11:28 首次发布

本文链接：https://blog.csdn.net/weixin_43860634/article/details/126119195

版权

随手记专栏收录该内容

44 篇文章 4 订阅

订阅专栏

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;
    }
}