spring data @ManyToOne not return all records-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Hmm, I think your first :coursename should be replaced with s.course.name. Because as your query looks now, at least in my mind, the first part of the where clause checks if the search term is null...

CodePudding user response：

If you want to return all students where course is null or course matches courseName parameter, change to this

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