0

I am quite new to JPA, and Criteria API. Currently my goal is to get the max() of a count() agregate.

Criteria code:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery criteriaQ = builder.createQuery();

Root<Student> s = criteriaQ.from(Student.class);
criteriaQ.groupBy(s);

criteriaQ.multiselect(s, builder.count(s.get(Student_.assistedCourse)));

Relationship:

@OneToMany(mappedBy="ta")
Student_.assistedCourse
@ManyToOne 
Course_.ta

In short, the goal is to get the student that has assisted in the most course. The output should be an Object[] containing the highest number of course assisted and the student entity instance, who assisted that number of course.

With normal SQL, I can do a subquery with some alias. But Criteria is a bit confusing to me in how to connect multiple queries together. What is the best practice for case like this?

Thanks in advance.

MikeNQ
  • 653
  • 2
  • 15
  • 29
  • If you just need a hint, check out [this answer](http://stackoverflow.com/a/14407538/870122). If you want an answer, post some more info including the entity relationships and the desired output... – perissf May 05 '14 at 08:49
  • Thanks, the hint you provided was really helpful. But I still can't apply it to the problem. Could you provide an answer to it? I also add info about relationship and output, Sorry about that. – MikeNQ May 05 '14 at 12:13
  • You don't need a subquery then. Just sort the results properly and use `setMaxResults(1)` – perissf May 05 '14 at 14:24
  • Thanks, but for case of multiple record having the maxed value, it will not return all the record. – MikeNQ May 05 '14 at 16:35

0 Answers0