Spring Data JPA自定义查询返回空：董事总经理登录可见同分支营销员客户问题

我有一个User实体，角色类型包括营销人员（Marketer）、董事总经理（Managing Director）和总经理（General Manager）。当角色为董事总经理的用户登录时，我希望该用户仅能查看分配给与其分支ID相同的营销人员类型用户的客户。

我在customer repository中编写了自定义查询，但返回结果为空，代码如下：

@Query("SELECT customer from Customer customer join customer.marketer marketer " + "where marketer.branch = :director") 
List<Customer> findByUserBranch(User director);

User实体代码 #

@Entity
@JsonIgnoreProperties({"hibernateLazyInitializer","handler"})
public class User {

    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    
    private String firstName ;
    private String lastName;
    
    @Column(name="user_name", unique=true)
    private String userName;
    
    private String password;
    private String Gender; 
    private String phoneNumber;
    private String email;
    
    @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
    @ManyToOne(targetEntity = Branch.class, 
     fetch = FetchType.LAZY )
    @JoinColumn(name="branch_id") 
    private Branch branch;
    
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date createdDate;
    
    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(
    name = "users_roles", 
    joinColumns = @JoinColumn(name = "user_id"),
    inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private Set<UserRole> userRole = new HashSet<>();
    
    @Enumerated(EnumType.STRING)
    private UserStatus status;
    
    @JsonBackReference
    @OneToMany(mappedBy="marketer",cascade = CascadeType.ALL, targetEntity=Customer.class)
    private List <Customer> customer;
}

控制器类代码 #

@GetMapping(value="branch/customers") 
public List<Customer> getListByBranch()
{ Authentication authentication =
SecurityContextHolder.getContext().getAuthentication(); 
User loggedInUser = userRepo.findByUserName(authentication.getName()); return customerRepo.findByBranch(loggedInUser); 
}

补充：Customer类代码 #

@Entity
@JsonIgnoreProperties({"hibernateLazyInitializer","handler"})
public class Customer implements Serializable {

    private static final long serialVersionUID = 8348682056500740593L;
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    private String userName;
    private String password;
    private String firstName ;
    private String lastName;
    private String gender;
    private String Address; 
    private String maritalStatus;
    private String category;
    private String motherMaidenName;
    private String idType;
    private String  idNumber;
    private String phoneNumber;
    private String email;
    
    @Column(nullable = true, length = 64)
    private String photos;
    
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date dateOfBirth;
    
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date registrationDate;
    
    @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
    @ManyToOne(targetEntity = User.class, 
     fetch = FetchType.LAZY )
    @JoinColumn(name="marketer_id") 
    private User marketer ;
    
    @JsonBackReference
    @OneToMany(mappedBy="customer_id",cascade = CascadeType.ALL, targetEntity=Investment.class)
    private List<Investment> investment;
}

问题排查点 #

1. 查询条件类型不匹配：原代码判断marketer.branch = :director，前者是Branch实体类型，后者是User实体类型，不同类型对象永远不可能相等，是查询返回空的核心原因。
2. 缺少角色过滤：没有额外筛选marketer的角色为Marketer，会把同分支下其他角色用户关联的客户也查询出来，不符合需求。
3. 方法名不统一：Repository中定义的方法名为findByUserBranch，但控制器中调用的是findByBranch，两者不一致会直接抛出方法不存在异常。

修正后的Repository代码 #

@Query("SELECT customer FROM Customer customer " +
       "JOIN customer.marketer marketer " +
       "JOIN marketer.userRole role " +
       "WHERE marketer.branch = :#{#director.branch} " +
       "AND role.roleName = 'Marketer'")
List<Customer> findByBranch(@Param("director") User director);

如果你的UserRole实体中存储角色名称的字段不是roleName，替换为实际字段名即可。如果Branch是懒加载导致属性获取异常，可以提前在业务层初始化director的branch属性，或者将Branch关联的fetch策略修改为EAGER。

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