我有兩張表，一張是客戶，另一張是客戶部門。Customer 與 customerDepartment 具有一對多的關(guān)系。我有一個特定的搜索條件，我需要在其中搜索部門名稱，如果它等于我需要檢索包括客戶在內(nèi)的所有客戶部門行。這就是我試圖得到的結(jié)果public interface CustomerRepository extends JpaRepository<Customer,Integer>{ @Query(value="select DISTINCT c from Customer c left join c.custDept cd where cd.deptName like %?1% ") Page<Customer> findByName(String name, Pageable pageable);}顧客@Entity@Table(name="customer")public class Customer implements Serializable{ private static final long serialVersionUID = 1L; @Id @Column(name= "customer_no",updatable = false, nullable = false) @GeneratedValue(strategy = GenerationType.SEQUENCE) private int customerNo; @Column(name= "customer_name") private String customerName; @Column(name= "industry") private String industry; @JsonManagedReference @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, fetch=FetchType.LAZY) private Set<CustomerDepartment> custDept;}客戶部：@Entity@Table(name = "customer_department")public class CustomerDepartment implements Serializable{ private static final long serialVersionUID = 1L; @Id @Column(name = "dept_id",updatable = false, nullable = false) @GeneratedValue(strategy = GenerationType.SEQUENCE) private int depId; @Column(name = "dept_name") private String deptName; @Column(name = "primary_contact") private String primaryContact; @JsonBackReference @ManyToOne(fetch=FetchType.LAZY) @JoinColumn(name = "customer_no", nullable = false) private Customer customer;}如果這在 JPA 中是不可能的，我有什么辦法可以做到這一點。謝謝您的幫助
1 回答
元芳怎么了
TA貢獻1798條經(jīng)驗 獲得超7個贊
是的，我是這么認為的。我評論說“我認為你的查詢沒問題，但是當(dāng)結(jié)果被編組為 JSON 時，所有相關(guān)的部門都會被檢索。你應(yīng)該在編組之前查看你的 sql 輸出并調(diào)試和檢查查詢結(jié)果，看看是否是案子?！?nbsp;我繼續(xù)玩弄它，我或多或少是正確的。
問題是您沒有custDept使用查詢獲取集合，因此當(dāng)客戶為您的其余響應(yīng)編組時，將執(zhí)行附加查詢以獲取值，而附加查詢只查詢所有內(nèi)容。
2019-05-25 14:29:35.566 DEBUG 63900 --- [nio-8080-exec-2] org.hibernate.SQL：選擇不同的 customer0_.customer_no 作為 customer1_0_，customer0_.customer_name 作為 customer2_0_，customer0_.industry 作為 industry3_0_ 來自客戶 customer0_ left outer join customer_department custdept1_ on customer0_.customer_no=custdept1_.customer_no where custdept1_.dept_name like ? 限制 ？
2019-05-25 14:29:35.653 DEBUG 63900 --- [nio-8080-exec-2] org.hibernate.SQL：選擇 custdept0_.customer_no 作為 customer4_1_0_，custdept0_.dept_id 作為 dept_id1_1_0_，custdept0_.dept_id 作為 dept_1_1_1_1_1 .customer_no 作為 customer4_1_1_，custdept0_.dept_name 作為 dept_nam2_1_1_，custdept0_.primary_contact 作為 primary_3_1_1_ 來自 customer_department custdept0_ where custdept0_.customer_no=?
如果您只想要查詢提供的內(nèi)容，則需要進行提取，以便在custDept編組之前初始化集合。您的查詢還存在其他問題。你應(yīng)該使用一個 sql 參數(shù):deptName并且你應(yīng)該聲明它，你應(yīng)該提供一個countQuery因為你要返回一個Page.
public interface CustomerRepository extends JpaRepository<Customer,Integer>{
@Query(value="select DISTINCT c from Customer c left join fetch c.custDept cd where cd.deptName like %:deptName% ",
countQuery = "select count ( DISTINCT c ) from Customer c left join c.custDept cd where cd.deptName like %:deptName% ")
public Page<Customer> findByName(@Param("deptName") String deptName, Pageable pageable);
為我工作?，F(xiàn)在只執(zhí)行了原始查詢，結(jié)果是正確的。
{
"content": [
{
"customerNo": 1,
"custDept": [
{
"deptName": "it"
}
]
}
],
最后請注意，最好根據(jù) spring 文檔在您的實體中使用Integerfor 。@Id
添加回答
舉報
0/150
提交
取消