数据库查询权限信息

HeJin大约 2 分钟Spring全家桶SpringSecurity认证授权

定义mapper接口

Menu实体类

这里使用的是IDEA的插件easy-code生成的，比较方便。

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("sys_menu")
public class Menu implements Serializable {
    
    @TableId
    private Long id;
    
    /**
     * 菜单名
     */     
    private String menuName;
    
    /**
     * 路由地址
     */     
    private String path;
    
    /**
     * 组件路径
     */     
    private String component;
    
    /**
     * 菜单状态(0显示1隐藏)
     */     
    private String visible;
    
    /**
     * 菜单状态(0正常1停用)
     */     
    private String status;
    
    /**
     * 权限标识
     */     
    private String perms;
    
    /**
     * 菜单图标
     */     
    private String icon;
    
         
    private Long createBy;
    
         
    private Date createTime;
    
         
    private Long updateBy;
    
         
    private Date updateTime;
    
    /**
     * 是否删除(0未删除1已删除)
     */     
    private Integer delFlag;
    
    /**
     * 备注
     */     
    private String remark;
}

mapper接口

public interface MenuMapper extends BaseMapper<Menu> {

    /**
     * 根据用户ID查询权限信息
     * @param userId 用户ID
     * @return 权限信息
     */
    List<String> selectPermsById(Long userId);

}

mapper文件编写

需要在配置文件中指定mapper文件位置。

mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml

MenuMapper.xml

可以使用IDEA插件从mapper接口生成mapper文件。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.sanfen.mapper.MenuMapper">

    <select id="selectPermsById" resultType="java.lang.String">
        SELECT
            DISTINCT m.perms
        FROM
            sys_user u
                LEFT JOIN sys_user_role ur ON u.id = ur.user_id
                LEFT JOIN sys_role r ON ur.role_id = r.id
                LEFT JOIN sys_role_menu rm ON r.id = rm.role_id
                LEFT JOIN sys_menu m ON rm.menu_id = m.id
        WHERE
            u.id = #{userId}
          AND u.status = 0
          AND r.status = 0
          AND m.status = 0
    </select>

</mapper>

测试mapper接口

@Autowired
private MenuMapper menuMapper;

@Test
void test2() {
    List<String> perms = menuMapper.selectPermsById(2L);
    System.out.println(perms);
}

结果：

[system:dept:list, system:test:list]

修改UserDetailsServiceImpl

修改UserDetailsServiceImpl，从数据库获取权限信息，之前是写死的。

@Service
public class UserDetailsServiceImpl implements UserDetailsService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private MenuMapper menuMapper;

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        // 查询用户信息
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getUserName, username);
        User user = userMapper.selectOne(queryWrapper);
        // 如果没有查询到用户,就抛出异常
        if (Objects.isNull(user)){
            throw new RuntimeException("用户名或者密码错误");
        }
        //从数据库查询对应的权限信息
        List<String> permissionList = menuMapper.selectPermsById(user.getId());

        return new LoginUser(user, permissionList);
    }
}

测试数据库查询的权限

测试接口

@RestController
public class HelloController {

    @GetMapping("/test")
    @PreAuthorize("hasAuthority('system:test:list')")
    public String test(){
        return "hello SpringSecurity";
    }

}