hibernatenamed-query

Hibernate complex query for more then two entity


I am trying to make hibernate query for searching and filtering my entity but I can't to find any solution. I have three bounded entities.

group entity

public class Group  {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private int id;
    
    @Column
    private String name;
    
    @EqualsAndHashCode.Exclude 
    @OneToMany(cascade={CascadeType.PERSIST, CascadeType.DETACH, CascadeType.MERGE, CascadeType.REFRESH})
    @JoinTable(
            name="students_groups",
            joinColumns=@JoinColumn(name="group_id"),
            inverseJoinColumns=@JoinColumn(name="student_id")
            )
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<Student> students; 
}

student entity

public class Student  {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private int id;
    
    @Column(name="personal_number")
    private int personalNumber;
    
    @Column(name="first_name")
    private String firstName;
    
    @Column(name="last_name")
    private String lastName;
    
    @Column
    private LocalDate birthday;
    
    @Column
    private String email;
    
    @Enumerated(EnumType.STRING)
    @Column(columnDefinition="enum('MALE','FEMALE')")
    private Gender gender;
    
    @Column
    private String address;
    
    @EqualsAndHashCode.Exclude 
    @OneToMany( cascade={CascadeType.PERSIST, CascadeType.DETACH, CascadeType.MERGE, CascadeType.REFRESH})
    @JoinTable(
            name="students_courses",
            joinColumns=@JoinColumn(name="student_id"),
            inverseJoinColumns=@JoinColumn(name="course_id")
            )
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<Course> courses;
    
    public Student(int id) {
        this.id = id;
    }

    public String getGender() {
        if(gender==null)
            gender = Gender.valueOf("MALE");
        return gender.toString();
    }

    public void setGender(String gender) {
        if(gender==null)
                gender = "MALE";
        this.gender = Gender.valueOf(gender);
    }
}

and course entity


public class Course {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private int id;
    
    @Column
    private String name;
    
    @Column
    private String description;
}

I needed to find group by group.id and course.id I try make query like this

Query<Group> query = session.createQuery("from Group as g join g.students as s join s.courses as c where c.id like :courseId and g.id like :groupId", Group.class);

but it doesn't work :( I will be very thanks for help me with this problem and sorry for my bad english :)


Solution

  • Try to correct your query in this way:

    Query<Group> query = session.createQuery("select g from Group g join g.students s join s.courses c where c.id = :courseId and g.id = :groupId", Group.class);
    

    Please note that:

    1. you can use like predicate only with string_expression.

    2. The TRUE and FALSE values are deprecated for @LazyCollection since you should use the JPA FetchType attribute of the @ElementCollection, @OneToMany, or @ManyToMany collection.