Criteria query for unidirectional one-to-many relationship

Question

So, I have the following entities:

@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 unidirectional one-to-many relationship. I need to look for a POS by posId and supplierId. That is, find a supplier with the specified supplierId and then find a pos in the supplier's list of pos's that has the specified posId. How do I write a criteria query for this?

I tried using subqueries. My idea was to create a subquery that would fetch all POS's of a Supplier with a given supplierId. Then the main query would search within those POS's for a POS with the given posId.

The problem was I couldn't write a query that would fetch a Suppliers list of POSs. Apparently you can't write a query of type 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);

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

Any ideas?

Daemon

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

Daemon

To be careful, with what you are doing here, Hibernate do a cross join, I have expected something else. For me your solution doesn't works well. -1 – Counteraccusation

Answer 2

I found very simple solution without subquery. Start from Suppler, join POS through posList and then 'select' POS.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<POS> query = cb.createQuery(POS.class);

Root<Supplier> supplierRoot = query.from(Supplier.class);
ListJoin<Supplier, POS> posList = supplierRoot.joinList(Supplier_.posList);
query
    .select(posList)
    .where(
        cb.equal(supplierRoot.get(Supplier_.suppliertId), supplierId),
        cb.equal(posList.get(POS_.posId), posId)
    );

With Hibernate 5.2.11 it generated nice query with two inner joins through N->M table very similar to manually written code ;-). Accepted answer is I guess wrong because it skips "posList" relation. It will select POS objects which are not in relation with specified Supplier.

Printable

This should be the accepted answer for the newer and most useful generation of hibernate. – Fabrication

How are you even able to do a select on "posList"? "posList" is a ListJoin and "select" expects a Selection... – Inspired

Answer 3

You can do this with subqueries. SQL equivalent to the jpql "select p from POS p where p.id in (select sp.id from Supplier s join s.posList sp where s.id = :supplierId)"

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

Banzai

I was unable to test if this works because I don't have experience with criteria queries. Perhaps someone who is more versed in the subject could help. – Daemon

Did you follow the link? It has an example. – Banzai

I read the link. But I couldn't write the subquery, it wouldn't compile. I'll elaborate in the question itself. – Daemon