CriteriaAPI get collection size

Question

I have got two entities: first Person (table person);

@Entity
public class Person implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Integer id;
    @Column(name = "name", nullable = false, length = 2147483647)
    private String name;
    @Column(name = "first_name", nullable = false, length = 2147483647)
    private String firstName;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "peopleId")
    private List<PeopleEmail> peopleEmailList;

    //... constuctors
    //... getters setters
}

And class PeopleEmail

@Entity
public class PeopleEmail implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Integer id;
    @NotNull
    @Column(name = "email", nullable = false, length = 2147483647)
    private String email;
    @JoinColumn(name = "people_id", referencedColumnName = "id", nullable = false)
    @ManyToOne(optional = false)
    private Person peopleId;

    //... constuctors
    //... getters setters
}

As you can see both entities are in the one-to-many relation. I want to create another class:

public class PersonAndCompany{
   private String personName;
   private String companyName;
   private int emailCount;

    //... constuctors
    //... getters setters
}

And I would like to write typequery which fill PersonAndCompany.class field with person name and companyName (another class) and email count, where count of person emails is more than 2. I would like to use criteria api. I write some code but I don't know how to add condition where and fill emailcount in PersonAndCompany.class.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<PersonAndCompany> cq = cb.createQuery(PersonAndCompany.class);
Root<Person> person = cq.from(Person.class);
Join<Person, Company> company = person.join(Person_.companyId);
cq.where(cb.greaterThan(cb.size(person.get(Person_.peopleEmailList)), 2));

Selection<PersonAndCompany> select = cb.construct(PersonAndCompany.class,
                        person.get(Person_.firstName),
                        company.get(Company_.name));
cq.select(select);
TypedQuery<PersonAndCompany> query = em.createQuery(cq);
return query.getResultList();

score 1 · Accepted Answer · answered Nov 26 '12 at 14:23

I think it may be possible to include a sub-query in the select clause for some JPA provides and some databases.

Select p.firstName, c.name, (select count(e) from Email e where e.person = p) from Peron p join p.company c where size(p.emails) > 2

Otherwise, you will need to use a count and some sort of group-by. The size check you have in the where clause could then be moved into a having clause.

In JPQL it would be something like, Criteria would be the equivalent,

Select p.id, p.firstName, c.name, count(e) from Peron p join p.company c join p.peopleEmaiList e group by p.id, p.firstName, c.name having count(e) > 2

You could always just read the objects as well, then just get the size() of the collection in Java. You could use a join or batch fetch on the emails and company to avoid n+1 queries.

score 0 · Answer 2 · answered Jul 16 '14 at 13:15

when i wanted to get derived or calculated values ionto a different structure i had more luck with TupleQueries, like this:

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery(); 
// add cq.from and joins 
// add cq.where predicates 
cq.select( cb.tuple(   
  person.get(Person_.firstName).alias("person"),  
  company.get(Company_.name).alias("company"),  
  // and maybe ...
  cb.size(person.get(Person_.peopleEmailList)).alias("emailcount") );
// ...
...
TypedQuery<Tuple> typedQuery = getEntityManager().createQuery(cq);
for(Tuple t : typedQuery.getResultList()) {
  PersonAndCompany ret = new PersonAndCompany();
  ret.setPersonName( t.get("person", String.class) );
  ret.setCompanyName( t.get("company", String.class) );
  ret.setEmailCount( t.get("emailcount", Integer.class) );
}
return ret;

hope that helps. Good luck ;)

CriteriaAPI get collection size

2 Answers2