如何實作一個JPQL查詢來檢索所有具有特定型別的用戶（使用MANYTOMANY關系實作）？

我正在處理一個 Spring Boot 專案，但我對 Hibernate/JPA 不太了解，我對如何使用它來執行以下查詢有以下疑問。

我有這兩個物體類：

User：它包含代表我門戶上用戶的記錄：

@Entity
@Table(name = "portal_user")
@Getter
@Setter
public class User implements Serializable {
     
    private static final long serialVersionUID = 5062673109048808267L;
    
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    
    @Column(name = "first_name")
    @NotNull(message = "{NotNull.User.firstName.Validation}")
    private String firstName;
    
    @Column(name = "middle_name")
    private String middleName;
    
    @Column(name = "surname")
    @NotNull(message = "{NotNull.User.surname.Validation}")
    private String surname;
    
    @Column(name = "sex")
    @NotNull(message = "{NotNull.User.sex.Validation}")
    private char sex;
    
    @Column(name = "birthdate")
    @NotNull(message = "{NotNull.User.birthdate.Validation}")
    private Date birthdate;
    
    @Column(name = "tax_code")
    @NotNull(message = "{NotNull.User.taxCode.Validation}")
    private String taxCode;
    
    @Column(name = "e_mail")
    @NotNull(message = "{NotNull.User.email.Validation}")
    private String email;
    
    //private String test;
    
    @Column(name = "pswd")
    @NotNull(message = "{NotNull.User.pswd.Validation}")
    private String pswd;
    
    @Column(name = "contact_number")
    @NotNull(message = "{NotNull.User.contactNumber.Validation}")
    private String contactNumber;
    
    @Temporal(TemporalType.DATE)
    @Column(name = "created_at")
    private Date createdAt;
    
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "user", orphanRemoval = true)
    @JsonManagedReference
    private Set<Address> addressesList = new HashSet<>();
    
    @ManyToMany(cascade = { CascadeType.MERGE })
    @JoinTable(
        name = "portal_user_user_type", 
        joinColumns = { @JoinColumn(name = "portal_user_id_fk") }, 
        inverseJoinColumns = { @JoinColumn(name = "user_type_id_fk") }
    )
    Set<UserType> userTypes;
    

    public User() {
        super();
        // TODO Auto-generated constructor stub
    }


    public User(String firstName, String middleName, String surname, char sex, Date birthdate, String taxCode,
            String email, String pswd, String contactNumber, Date createdAt) {
        super();
        this.firstName = firstName;
        this.middleName = middleName;
        this.surname = surname;
        this.sex = sex;
        this.birthdate = birthdate;
        this.taxCode = taxCode;
        this.email = email;
        this.pswd = pswd;
        this.contactNumber = contactNumber;
        this.createdAt = createdAt;
    }

}

每個用戶可以與此reppresented一個或多個角色相關聯多對多關系：

@ManyToMany(cascade = { CascadeType.MERGE })
@JoinTable(
    name = "portal_user_user_type", 
    joinColumns = { @JoinColumn(name = "portal_user_id_fk") }, 
    inverseJoinColumns = { @JoinColumn(name = "user_type_id_fk") }
)
Set<UserType> userTypes;

如您所見，該欄位與名為portal_user_user_type的 DB 表相關聯，該表僅包含用戶表的 PK 和由以下物體類表示的user_type表的 PK ：

@Entity
@Table(name = "user_type")
@Getter
@Setter
public class UserType implements Serializable {
    
    private static final long serialVersionUID = 6904959949570501298L;

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    
    @Column(name = "type_name")
    private String typeName;
    
    @Column(name = "description")
    private String description;
    
    //@OneToMany(mappedBy = "userType")
    //@JsonManagedReference
    //private Set<User_UserType> userToUserTypeAssociation = new HashSet<>();
    
    @ManyToMany(cascade = { CascadeType.MERGE })
    @JoinTable(
        name = "user_type_operation", 
        joinColumns = { @JoinColumn(name = "fk_user_type_id") }, 
        inverseJoinColumns = { @JoinColumn(name = "fk_operation_id") }
    )
    Set<Operation> operations;
    

    public UserType() {
        super();
        // TODO Auto-generated constructor stub
    }
    
    public UserType(String typeName, String description) {
        super();
        this.typeName = typeName;
        this.description = description;
    }

}

Now the previous class is mapping the user_type database typological table that can contains only a specific set of values. For example having typeName like ADMIN or AGENT or CLIENT.

Into my Spring Boot project I have this repository interface implementing JpaRepository interface:

public interface UsersRepository extends JpaRepository<User, Integer> {
    
    User findByemail(String email);

}

At the moment it contains onlya query implemented using the "query by method name" tecnique.

Now I have to implement another more complex query based on the previous entity classes:

I have to retrieve all the User instance having a specific UserType.typeName value.

例如：檢索UserType.typeName等于ADMIN 的所有用戶記錄。

是否可以使用按方法名稱樣式的查詢來實作它，還是使用 JPQL 更好。如果我如何創建這樣的查詢？（在 JPQL 中也可以）

uj5u.com熱心網友回復：

您可以使用@Query 批注來撰寫查詢，或者您可以使用 findByUserTypes_TypeNameIn(List typeNameList) 獲取用戶型別名稱串列或 findByUserTypes_TypeName(String typeName) 獲取特定型別名稱。

標籤：爪哇 弹簧靴 休眠 弹簧数据-jpa jpql

上一篇：安全測驗（三） 服務器安全 滲透測驗 常規服務器安全漏洞 高危埠暴露、弱口令密碼、暴力破解、服務器提權、Mysql資料庫提權等 淺談《社會工程學》實戰案例

下一篇：如何完全中止輸出流下載？