JPA Criteria Builder OneToMany Restrictions

Question

I have a Parent with a OneToMany associations with a Child Table.

I'm trying to write a query with CriteriaBuilder to restrict the results returned from the Child table.

I'm adding a Predicate, something like

cb.equal(parent.get("children").get("sex"), "MALE")

If the Parent has a son or SON and Daughter it's returning that parent but also returning all the children they have.

Hibernate fires off the first query with my predicates but the second query to get the children only uses the JoinColumn in the where clause it doesn't include

cb.equal(parent.get("children").get("sex"), "MALE").

Thoughts?

I am using a SetJoin

children = parent.joinSet("children", JoinType.LEFT)

CLARIFICATION:

public static Specification<Parent> findPlanBenefits(Integer parentId) {
    return (parent, query, cb) -> {
        Predicate predicates = cb.conjunction();
        List<Expression<Boolean>> expressions = predicates.getExpressions();

        //Parent Criteria
        expressions.add(cb.equal(parent.get("parentId"), parentId));

        //Children Criteria
        SetJoin<Parent, Children> children = parent.joinSet("children", JoinType.LEFT);

        Predicate sex = cb.equal(children.get("sex"), "MALE");
        children.on(sex);

        return predicates;
    };
}

Maritime

Please fix the code formatting, removing the <code> tags and the arrows. Leave an empty line before the code, and use the indentations to have it correctly formatted. – Lassa

Answer 1

I am afraid, the JOIN ON does not work as you expect in your answer. JOIN ON only tells how to join, and NOT how relationships are loaded.

So, in order to solve your problem you will have to filter the children after they are loaded, or fetch manually all male children with a separate query.

In order to check how JOIN ON works, you could try also the corresponding JPQL query.

---

UPDATE

OP told that the JPQL queryselect p from parent p join fetch children c where p.parentId = :parentId and c.sex = "MALE" works.

The corresponding CriteriaQuery would look like:

CriteriaQuery<Parent> criteria = cb.createQuery((Class<Parent>) Parent.class);
Root<Parent> parent = criteria.from(Parent.class);

criteria.select((Selection<T>) parent);
SetJoin<Parent, Children> children = parent.joinSet("children", JoinType.LEFT);

Predicate sexPredicate = cb.equal(children.get("sex"), "MALE");
parent.fetch(children);
//parent.fetch("children");//try also this

criteria.where(sexPredicate);

Catchascatchcan

select p from parent p join fetch children c where p.parentId = :parentId and c.sex = "MALE" Works, how do I do the equivalent via Specification/CriteriaQuery? – Maritime

I have just changed the sample code, because the fetch part was missing. – Catchascatchcan

But I am afraid that does not work either. Do you want the parent to have loaded only its male children? – Catchascatchcan

Yes, that's what the JPQL does. – Maritime

The relationship is eager, I tried the fetch it does not work. – Maritime

Does also my creteria query work? It seems the JPQL really works as you expect, because Hiberante is designed so. I was not aware of this feature before :) I suppose also that the relationship is lazy... – Catchascatchcan

Let us continue this discussion in chat. – Maritime

Answer 2

When you create a JOIN (especially when property is collection type, not SingularAttribute, you have to use it to build the criteria, so use

cb.equal(children.get("sex"), "MALE").

instead of

cb.equal(parent.get("children").get("sex"), "MALE").

Pulvinate

Actually that's what I did: SetJoin<Parent, Child> children = parent.joinSet("children", JoinType.LEFT); cb.equal(children.get("sex", "MALE") But it still only puts that predicate on the first query to return the parent object. – Maritime

I simply don't understand your problem, code with comment where problem occures is much better than description. – Pulvinate

Answer 3

For the future referrence, this is from another post, that helped me (link): Instead of parent.joinSet use fetch and then cast it to join:

Join<Parent, Children> join = (Join<Parent, Children>)parent.fetch(Parent_.children);

As mentioned in the post linked above it is not perfect solution but it saved me a lot of headaches.

Luminal