JPA CriteriaBuilder find entity which has elements with certain attributes in collection

Question

I have an entity which contains a list of elements and now I want to search over attributes of these elements. This constraint should be "and" connected. Please see these simple example:

@Entity
public class Parent {

    @Column
    @Enumerated(EnumType.STRING)
    private City city;

    @OneToMany(...)
    private List<Children> childrens;
}

@Entity
public class Children {

    @Column 
    @Enumerated(EnumType.STRING)
    private School school;

    @Column
    private Integer yearInSchool;

}

Now I want to find Parents in a certain city, lets say "BigCity" with children in School "AwesomeSchool" which are in class/ year 6. I want to get the search result only via CriteriaBuilder.

So far I got:

final CriteriaBuilder c = getCriteriaBuilder();
final CriteriaQuery<Parent> query = c.createQuery(Parent.class);
final Root<Parent> r = query.from(Parent.class);
query.select(r)
        .where(c.and(c.equal(r.get("city"), City.BigCity)),
               c.equal(r.get("childrens").get("school"), School.AwesomeSchool),
               c.equal(r.get("childrens").get("yearInSchool"), 6));

Unfortunately there are two problems here: - it looks like I can't call get("school") on the list attribute - this will return all parents with children which are either in "AwesomeSchool" or are 6 years in the school.

Can you help me please? I thought about using a join, but there the same question is: how can I define the where part of the join so that it considers that both attributes (school and yearInSchool) have to be fulfilled at the same time. I found similar posts about querying for objects whose children fulfill one condition - but here the children has to fulfill two conditions at the same time.

Update 1 If I use a join to assert e.g. the "school" of one child, I get so far concerning the predicate:

Predicate predicate = r.join("childrens").get("school").in(School.AwesomeSchool)

How can I reuse this joined object to assert is also for the second filter condition?

as you have it currently you will satisfy one condition on the child at a time, so child 1 may satisfy one condition but child 2 another condition. you want the same child to satisfy both conditions, hence need to use a join and make use of the joined object in the second and third where clauses. Worth writing it as plain JPQL before doing the Criteria part. — Neil Stockton, Apr 19 '16 at 15:01
Hi Neil, thank you for your comment. Yes you totally got my problem. I updated the question with a join for one condition. How can I reuse this object to filter for the second condition? — Christian, Apr 19 '16 at 15:21

score 5 · Answer 1 · answered Apr 19 '16 at 15:26

5

You need to JOIN and then use the JOIN object you got when forming the join when forming the WHERE clauses.

Join childrenJoin = r.join("childrens");

query.where(c.and(c.equal(r.get("city"), City.BigCity)),
               c.equal(childrenJoin.get("school"), School.AwesomeSchool),
               c.equal(childrenJoin.get("yearInSchool"), 6));

Perhaps you mean your JPQL to be :

SELECT p FROM Parent p JOIN p.childrens c 
WHERE p.city = :theCity AND c.school = :theSchool AND c.yearInSchool = 6

answered Apr 19 '16 at 15:26

Neil Stockton

11,383
3
34
29

Thank you. I'll try this. But as I read this, wouldn't this also return a Parent "P" if this parent has two children, one in "AwesomeSchool" and class 10 and the second in "SomeotherSchool" and class 6? Because if I look at the parent, the condition is fulfilled: Parent P has a child in AwesomeSchool and a child which is in year 6. – Christian Apr 19 '16 at 15:40

JPA CriteriaBuilder find entity which has elements with certain attributes in collection

1 Answers1