Spring多对多实体关联场景下如何单次查询获取指定用户的所有权限集合

一、可通过单次查询实现需求 #

有两种成熟的实现方案，优先推荐JPQL写法，不需要绑定特定数据库，适配JPA规范：

方案1：JPQL查询（推荐） #

可以直接在UserRepository中添加如下方法，从用户实体出发直接关联查询权限：

@Query("SELECT DISTINCT p FROM User u JOIN u.roles r JOIN r.privileges p WHERE u.login = :login")
Set<Privilege> findAllPrivilegesByUserLogin(@Param("login") String login);

如果只需要权限名称不需要完整实体，可以调整返回值和查询字段，减少数据传输：

@Query("SELECT DISTINCT p.name FROM User u JOIN u.roles r JOIN r.privileges p WHERE u.login = :login")
Set<String> findAllPrivilegeNamesByUserLogin(@Param("login") String login);

注：加DISTINCT是为了避免同一个权限属于多个用户角色时，查询结果出现重复数据

方案2：修正后的原生SQL #

你写的原生SQL逻辑是对的，但有两处问题需要修正：

1. 原SQLSELECT P.NAME但返回类型是Set<Privilege>，类型不匹配会报错，要么查全量字段返回实体，要么修改返回类型为字符串集合
2. 不需要用LEFT JOIN，用INNER JOIN就能拿到用户实际拥有的权限，还能避免NULL无效数据提升查询效率

修正后代码：

// 返回全量Privilege实体
@Query(value = "SELECT DISTINCT p.* FROM user u " +
        "INNER JOIN user_roles ur ON u.id = ur.user_id " +
        "INNER JOIN privilege_roles pr ON ur.role_id = pr.role_id " +
        "INNER JOIN privilege p ON pr.privilege_id = p.id " +
        "WHERE u.login = :login", nativeQuery = true)
Set<Privilege> getAllMyPriviliges(@Param("login")String login);

// 仅返回权限名称
@Query(value = "SELECT DISTINCT p.name FROM user u " +
        "INNER JOIN user_roles ur ON u.id = ur.user_id " +
        "INNER JOIN privilege_roles pr ON ur.role_id = pr.role_id " +
        "INNER JOIN privilege p ON pr.privilege_id = p.id " +
        "WHERE u.login = :login", nativeQuery = true)
Set<String> getAllMyPrivilegeNames(@Param("login")String login);

二、现有代码可优化点 #

1. 性能优化 #

• 原有实现是典型的N+1查询问题：先查用户、再查角色列表、最后循环每个角色查权限，用户有N个角色就会执行N+2次数据库查询，单次查询只需要1次请求，性能提升非常明显
• 去掉不必要的序列化/反序列化流程：不需要先查DTO取ID再反向查询，关联查询可以一步拿到目标数据

如果暂时不想调整DAO层查询，也可以利用你已经配置的EAGER抓取策略，直接从用户实体取关联数据，不需要额外查数据库：

UserDto userDto = userService.findByLogin(login);
Set<PrivilegeDto> authorities = userDto.getRoles().stream()
        .flatMap(role -> role.getPrivileges().stream())
        .collect(Collectors.toSet());

2. 代码问题修复 #

• Role实体的users关联配置有误：cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType}, 最后一个CascadeType没有指定枚举值，会编译报错
• 业务层代码存在语法错误：比如roleService(findByUserId(方法调用写法错误、userDto.getId().stream()写法不符合逻辑，应该从userDto的角色集合取ID
• 双向关联存在ToString死循环风险：User、Role、Privilege三个实体互相有双向关联，且都加了@ToString注解，除了Role的privileges加了@Exclude之外，其他关联字段都没有排除，调用toString时会出现栈溢出，建议给所有双向关联字段加@ToString.Exclude
• 抓取策略不合理：Role的privileges、User的roles都配置了FetchType.EAGER，每次查User都会自动关联查角色和权限，很多业务场景不需要这些关联数据，会产生多余查询，建议改为FetchType.LAZY，需要关联数据时用FETCH JOIN查询

内容的提问来源于stack exchange，提问作者Snopek1337