java - 使用复杂的 JPA 查询从集合中获取值

Question

我正在尝试通过其 ID 从采购订单 (Po) 中获取 Material 。路径和连接表中有一些集合。

Po.java

@Entity
@Table(name = "T_PO")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Po implements Serializable {

    @ManyToOne
    @JoinColumn(name = "customer_id", referencedColumnName = "id")
    private Customer customer;

    @OneToMany(mappedBy = "po", targetEntity = Po_part.class)
    private List<Po_part> partList;

部分.java

@Entity
@Table(name = "T_PART")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Part implements Serializable {

    @ManyToMany
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    @JoinTable(name = "T_PART_MATERIAL",
        joinColumns = @JoinColumn(name="parts_id", referencedColumnName="ID"),
        inverseJoinColumns = @JoinColumn(name="materials_id", referencedColumnName="ID"))
    private Set<Material> materials = new HashSet<>();

    @OneToMany(mappedBy="part",targetEntity=Po_part.class)
    private List<Po_part> partList;

Po_part.java

@Entity
@Table(name = "T_PO_PART")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Po_part implements Serializable {

    @ManyToOne
    private Part part;

    @ManyToOne
    private Po po;

Material .java

@Entity
@Table(name = "T_MATERIAL")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Material implements Serializable {

    @ManyToOne
    @JoinColumn(name = "supplier_id", referencedColumnName = "id")
    private Supplier supplier;

    @ManyToOne
    @JoinColumn(name = "supplier_id_2", referencedColumnName = "id")
    private Supplier supplier2;

MaterialRespository 中的 JPA 查询

 @Query("SELECT m FROM Material m "
            + "JOIN Po po "
            + "JOIN po.partList po_part "
            + "JOIN po_part.part parts "
            + "JOIN parts.materials materials "
            + "WHERE po.id = ?1")
     List<Material> getListOfMaterialsForPo(Long id);

最终的结果是我希望能够得到一份Po所需的 Material list

--------更新------------

我在 mySQL workbench 上获得了以下查询，但它仅在我确实需要实际的 Material 对象来访问其 getter 时才返回库存。不确定这是否可以用 SQL 解决。

查询:

 @Query(value="SELECT materials.inventory_count materials "
                    +"FROM hillcresttooldie.t_po po "
                    +"join hillcresttooldie.t_po_part po_part "
                    +"on po_part.po_id = po.id "
                    +"join hillcresttooldie.t_part part "
                    +"on part.id = po_part.part_id "
                    +"join hillcresttooldie.T_PART_MATERIAL material "
                    +"on material.materials_id = part.id "
                    +"join hillcresttooldie.T_MATERIAL materials "
                    +"on material.materials_id = materials.id "
                    +"where po.id LIKE %?1", nativeQuery = true)
         List<Integer> getListOfMaterialsForPo(Long id);

另一件真正让我想知道的事情是，是否有用于 JPA 的 mySQL 类型工作台。在 Eclipse 中，我有以下附加组件:

不过我没用过。认为将其添加到帖子中会有所帮助。

Answer 1

花一些时间实际学习 JPQL。这并不难。 JPQL 使用关联来进行连接。您需要的查询非常简单

select material from Po po
join po.partList popart
join popart.part part
join part.materials material
where po.id = :poId

请为您的类起有意义的名称，并遵守 Java 命名约定。