CODEWITHSUNDEEP
javajpa
Mike3355
Mike3355

Reputation: 12061

Getting values from a collection with a complex JPA query

I am trying to get the materials from a purchase order (Po) by its id. There are a few collections in the path and join tables.

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;

Part.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;

JPA Query in the MaterialRespository

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

The end result is I want to be able to get a list of materials required for a Po

-------UPDATE---------

I got the below Query to work on mySQL workbench but it only returns the inventory when I really need the actual Material object to access its getters. Not sure if this is solvable with SQL.

Query:

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

Also another thing that really has me wondering is is there mySQL type work bench for JPA. In Eclipse I have the below add-on:

enter image description here

enter image description here

However I have never used it. Thought it would be helpful to add this to the post.

Upvotes: 4

Views: 196

Answers (1)

JB Nizet
JB Nizet

Reputation: 692181

Take some time to actually learn JPQL. It's not that hard. JPQL uses associations to make joins. The query you need ias as simple as

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

And please, give meaninful names to your classes, and respect the Java naming conventions.

Upvotes: 1

Related Questions