时间戳

首页 > 解决方案 > 如何编写一个显示仅分配给登录用户的列表的 SQL 查询？

java - 如何编写一个显示仅分配给登录用户的列表的 SQL 查询？

问题描述

我有一组具有不同角色类型的用户营销人员、管理员、总经理。注册客户帐户时，有一列获取分配给客户的营销人员的名称。我在用户和客户之间创建了@ManyToOne 和@OneToMany 关系。当用户类型营销人员登录到软件时，我希望营销人员只看到注册到已登录营销人员名称的客户。这似乎很复杂，因为首先我需要使用用户名获取登录的营销人员详细信息，然后获取名字和姓氏，然后显示分配给该名称的客户列表。

这是客户实体

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

这是用户实体

@Entity
public class User {

    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    
    private String firstName ;
    private String lastName;
    private String username;
    private String password;
    private String Gender; 
    private String phoneNumber;
    private String email;
    private String 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;

这是包含 JWT 身份验证管理器正在使用的 FindByUsername 方法的用户存储库

@Repository
public interface UserAccountRepository extends JpaRepository <User, Long>  {
    
     Optional<User> findById(Long id);

     User findByUsername(String username);
}

更新：我从查询中得到空响应，我做错了什么？这是更新的客户控制器类

@CrossOrigin(origins = {"http://localhost:3000"})
@RestController
public class CustomerController {

@Autowired
CustomerAccountService customerRepo;

@Autowired
UserAccountService  userRepo;

 @GetMapping(value="marketers/customers")
         public List<Customer> getLlistByMarketerName(){
         Authentication authentication = SecurityContextHolder.getContext().getAuthentication();     
         User loggedInUser =userRepo.findByUserName(authentication.getName());
         System.out.println("logged in user:"+ loggedInUser);
         return customerRepo.findByMarketer(loggedInUser);

我打印了 loggedInUser 变量，但它显示为 null。

标签： javasqlspring-bootspring-securityspring-data-jpa

解决方案

您的 CustomerRepository 应该可以轻松处理：

public interface CustomerRepository extends JpaRepository<Customer, Long> {
  List<Customer> findByMarketer(User marketer);
}

如果您想更明确并显示查询：

public interface CustomerRepository extends JpaRepository<Customer, Long> {
  @Query("select customer from Customer customer join customer.marketer marketer where marketer = :marketer")
  List<Customer> findByMarketer(User marketer);
}

传递的用户（营销人员）是从 UserAccountRepository 获取的当前用户。

推荐阅读