CODEWITHSUNDEEP
javajpaone-to-many
hello_world_infinity
hello_world_infinity

Reputation: 4830

JPA Criteria Builder OneToMany Restrictions

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

Upvotes: 7

Views: 20949

Answers (3)

stove
stove

Reputation: 576

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.

Upvotes: 0

V G
V G

Reputation: 19002

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

Upvotes: 10

Antoniossss
Antoniossss

Reputation: 32527

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").

Upvotes: 3

Related Questions