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