[FIXED] spring data @ManyToOne not return all records

Issue

I have two entity and many to one relation Student Course

    @Entity
    public class Student {

        @Id
        private Long id;

        @ManyToOne
        @JoinColumn(name = "COURSE_ID")
        private Course course;

    }

    @Entity
    public class Course{

        @Id
        private Long id;

        @Column
        private String name;

    }

each student can have just one or not have any course it means in database for that student course_id column is null

my problem is when get list of student and want add condition on name of course like :

@Query("select s from Student s where :courseName is null or :courseName = s.course.name")
    List<Student> search(@Param("courseName") String courseName)

this list return only student have course and not contain all of student store in table

Solution

If you don't explicitly specifiy the join, JPA uses an implicit inner join, this is why you don't get all expected records.

Indeed when you do s.course.name, JPA actually inner join Student and Course entities.

So if you want to retrieve null values, you can explicitly specifiy a left join (left join returns all rows from the left table, even if there are no matches in the right table) :

select s from Student s left join s.course c where :courseName is null or :courseName = c.name

Answered By - Yann39
Answer Checked By - Clifford M. (JavaFixing Volunteer)