java - Getting values from a collection with a complex JPA query -

i trying materials purchase order (po) id. there few collections in path , 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 materialrespository

 @query("select m 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 want able list of materials required po

-------update---------

i got below query work on mysql workbench returns inventory when need actual material object access getters. not sure if solvable 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 %?1", nativequery = true)          list<integer> getlistofmaterialsforpo(long id); 

also thing has me wondering is there mysql type work bench jpa. in eclipse have below add-on:

however have never used it. thought helpful add post.

take time learn jpql. it's not hard. jpql uses associations make joins. query need ias simple as

select material po po join po.partlist popart join popart.part part join part.materials material po.id = :poid 

and please, give meaninful names classes, , respect java naming conventions.