How to select M:N group by JPA Criteria api

Question

I have this two classes with relationship M:N :

package org.heller.jpa.entity;

import java.util.List;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;

@Entity
@Table(name="courses")
public class Course {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Column
    private String name;
    @ManyToMany(mappedBy = "courses")
    private List<Student> students;
    @ManyToOne
    @JoinColumn(name="class_room_id")
    private ClassRoom classRoom;
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public List<Student> getStudents() {
        return students;
    }
    public void setStudents(List<Student> students) {
        this.students = students;
    }
    public ClassRoom getClassRoom() {
        return classRoom;
    }
    public void setClassRoom(ClassRoom classRoom) {
        this.classRoom = classRoom;
    } 
}

package org.heller.jpa.entity;

import java.util.Set;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.Table;

@Entity
@Table(name="Students")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Column
    private String firstName;
    @Column
    private String surname;
    @Column
    private String email;
    @ManyToMany
    @JoinTable(name="course_student", joinColumns = @JoinColumn(name="student_id"), inverseJoinColumns = @JoinColumn(name="course_id"))
    private Set<Course> courses;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }


    public Set<Course> getCourses() {
        return courses;
    }

    public void setCourses(Set<Course> courses) {
        this.courses = courses;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", firstName=" + firstName + ", surname=" + surname + ", email=" + email + "]";
    }
    
}

I try to select all Students which have 3 or more courses witch this code:

private void selectStudentWith3orMoreCourses() {
entityManager.getTransaction().begin();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> cq = cb.createQuery(Student.class);
Root<Student> student = cq.from(Student.class);
cq.select(student);

Join<Student, Course> typeJoin = student.join(Student_.courses);
cq.groupBy(typeJoin.get(Course_.id));

cq.having(cb.ge(cb.count(typeJoin.get(Course_.id)), 3));

TypedQuery<Student> query = entityManager.createQuery(cq);

List<Student> results = query.getResultList();

entityManager.getTransaction().commit();

System.out.println(results);

}

Problem is that Hibernate generate this select, which is wrong:

/* <criteria> */ select
        s1_0.id,
        s1_0.email,
        s1_0.firstName,
        s1_0.surname 
    from
        Students s1_0 
    join
        (course_student c1_0 
    join
        courses c1_1 
            on c1_1.id=c1_0.course_id) 
        on s1_0.id=c1_0.student_id 
    group by
        c1_1.id 
    having
        count(c1_1.id)>=?

Can someone give me a point how to select all students, which has more or equal than 3 courses? I see some examples, but nothing of this examples run. Every examples return strange exception. Thank you for help.

Answer 1

This code works:

entityManager.getTransaction().begin();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> cq = cb.createQuery(Student.class);
Root<Student> student = cq.from(Student.class);

Join<Student, Course> typeJoin = student.join(Student_.courses);


cq.groupBy(student.get(Student_.id));
cq.having(cb.ge(cb.count(typeJoin.get(Course_.id)), 3));

List<Student> results =  entityManager.createQuery(cq).getResultList();

entityManager.getTransaction().commit();

System.out.println(results);

Generate this select:

/* <criteria> */ select
        s1_0.id,
        s1_0.email,
        s1_0.firstName,
        s1_0.surname 
    from
        Students s1_0 
    join
        course_student c1_0 
            on s1_0.id=c1_0.student_id 
    group by
        s1_0.id 
    having
        count(c1_0.course_id)>=?