如何通过 JPA 从连接的第二个表中检索特定行

Question

我有两张表，一张是客户，另一张是客户部门。Customer 与 customerDepartment 具有一对多的关系。

我有一个特定的搜索条件，我需要在其中搜索部门名称，如果它等于我需要检索包括客户在内的所有客户部门行。

这就是我试图得到的结果

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 中是不可能的，我有什么办法可以做到这一点。谢谢您的帮助

Answer 1

是的，我是这么认为的。我评论说“我认为你的查询没问题，但是当结果被编组为 JSON 时，所有相关的部门都会被检索。你应该在编组之前查看你的 sql 输出并调试和检查查询结果，看看是否是案子。”&nbsp;我继续玩弄它，我或多或少是正确的。问题是您没有custDept使用查询获取集合，因此当客户为您的其余响应编组时，将执行附加查询以获取值，而附加查询只查询所有内容。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 ?&nbsp;限制 ？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=?如果您只想要查询提供的内容，则需要进行提取，以便在custDept编组之前初始化集合。您的查询还存在其他问题。你应该使用一个 sql 参数:deptName并且你应该声明它，你应该提供一个countQuery因为你要返回一个Page.public interface CustomerRepository extends JpaRepository<Customer,Integer>{&nbsp; &nbsp; @Query(value="select DISTINCT c from Customer c left join fetch c.custDept cd where cd.deptName like %:deptName% ",&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; countQuery = "select count ( DISTINCT c ) from Customer c left join c.custDept cd where cd.deptName like %:deptName% ")&nbsp; &nbsp; public Page<Customer> findByName(@Param("deptName") String deptName, Pageable pageable);为我工作。现在只执行了原始查询，结果是正确的。{"content": [&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; "customerNo": 1,&nbsp; &nbsp; &nbsp; &nbsp; "custDept": [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "deptName": "it"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; }],最后请注意，最好根据 spring 文档在您的实体中使用Integerfor 。@Id