Request criteria for a one-to-many one-way relationship

Question

So, I have the following objects:

@Entity public class Supplier { @Column(name = "SUPPLIERID") private BigInteger supplierId; @OneToMany @JoinColumn(name = "ID_SUPP", foreignKey = @ForeignKey(name = "fk_POIS_SUPP")) private List<POS> posList; ... } @Entity public class POS { @Column(name = "POSID") private BigInteger posId } 

So, POS does not have a reference to Supplier , which means that we have a one-to-many relationship. I need to search for POS posId and supplierId . That is, find the supplier with the specified supplierId , and then find pos in the list of pos suppliers that has the specified posId. How to write a query criteria for this?

I tried using subqueries. My idea was to create a subquery that would retrieve all the POS from the Supplier with the given supplierId . Then the main request will search within the POS for the POS with the given posId .

The problem was that I could not write a query that would take a list of Supplier POS s. Apparently, you cannot write a query like List<POS> :

 CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<POS> outerQuery = cb.createQuery(POS.class); Root<POS> outerQueryRoot = outerQuery.from(POS.class); Subquery<POS> subquery = outerQuery.subquery(POS.class); Root<Supplier> subqueryRoot = subquery.from(Supplier.class); subquery.where(cb.equal(subqueryRoot.get(Supplier_.supplierId), supplierId)); subquery.select(subqueryRoot.get(Supplier_.posList); 

In this last line, I get a compilation error Expression<POS> does not match Expression<List<POS>> . And I can't change the type of the subquery because Java does not allow class generic literals ( List<POS>.class ).

Any ideas?

Answer 1

I finally found the answer, just use two roots :

  CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<POS> cq = cb.createQuery(POS.class); Root<POS> posRoot = cq.from(POS.class); Root<Supplier> supplierRoot = cq.from(Supplier.class); cq.where(cb.and( cb.equal(supplierRoot.get(Supplier_.suppliertId), supplierId), cb.equal(posRoot.get(POS_.posId), posId))); cq.select(posRoot); 

Answer 2

You can do this using subqueries. Jpql equivalent SQL "select p from POS p, where p.id in (select sp.id from Supplier s join s.posList sp, where s.id =: providerId)"

See JPA2 API Criteria: select ... in (select where)