join 2 columns and get only matched vlaues in hibernate

Question

I have 2 tables:

 course: id, name
 student: id, course_id, age

Tables are tied with oneToMany annotation

I am trying to write a hibernate query against course that will return courses and students that are x years 0ld. That's my attempt:

SELECT c from Course c RIGHT JOIN  c.student p ON p.age = 20 

It returns a course that has at least one student that is 20. when there are more students that are not 20, they are included in the result set as well. Is there a way to restrict the joined table values?

Edit: These are my entities:

public class Course implements Serializable {

     private static final long serialVersionUID = 646349111982187812L;

     @Id
     @Column(name = "id", unique=true, nullable=false)
     private String id;
     @Column(name = "name", unique=false, nullable=false)
     private String name;

     @OneToMany(cascade={CascadeType.ALL, CascadeType.REMOVE}, 
     fetch=FetchType.LAZY, mappedBy = "Course")  
     @OrderBy("age")
     @JsonManagedReference
     private Set<Student> students; 

 getters and setters ...
}

public class Student implements Serializable {

     private static final long serialVersionUID = 646349221337813L;


     @Id
     @GeneratedValue(strategy = GenerationType.AUTO)
     private Long id;

     @Column(name = "age", unique=false, nullable=true)
     private String age;

     @ManyToOne()
     @JsonBackReference
     private Course course; 
}

I am trying to use this method from my crudrepository to get the expected result:

 @Query(?????)
 public List<Course> findCoursesWithAdults(@Param("age")int age)

Answer 1

SELECT c.name, s.id
FROM course c
INNER JOIN student s ON c.id = s.course_id
WHERE s.age = 20

Answer 2

it sounds like you want an inner join instead of a right join

SQL

SELECT distinct c.* from Course c inner join Student p on (c.id=p.course_id and p.age=20)

or with hql

select distinct c from Course c inner join c.student p with p.age=20

in order to get the information you are requesting, you will either have to limit the Set in Course using @Where ("age = 20" ) or simply deal with a list of students instead of courses.

select p from Course c inner join c.student p with p.age=20

you can reference the attached course object through any one of the getCourse methods on the student objects. You could also use "group by" here to help you order things.

or you can use a filter within the hibernate Entity...

@Transient public List<Students> getStudents(Integer age){ List<Students> tmp = new ArrayList<>(); for(Student s: getStudents()) if(s.getAge().equals(age))tmp.add(s); return tmp; }

one more edit and im done... there is another way to do this that i didnt mention..

a select wrapper query.

create a wrapper object for your course and student

public class CourseWrapper(){ private Course c; private List<Students> p = new ArrayList<>(); ....constructor ... getters ...setters }

select new CourseWrapper(c, students) from Course c left outer join c.students p with p.age=20' more info here