LEFT JOIN FETCH not working - Spring data JPA

Question

I am facing an issue during using LEFT JOIN FETCH. Please refer to my below entities and repositories.

Consider a scenario, where optional courses will not be deleted from table at any point of time. But the student information can be deleted. There is no primary key and foreign key relationship between two tables in database. Just we have common column "STUDENT_ID".

@NamedEntityGraphs({
@NamedEntityGraph(
        name = "Student.optionalCourse",
        attributeNodes = @NamedAttributeNode("optionalCourse")
    )
})
@Table("STUDENT")
class Student {

    @Id
    @column_name(STUDENT_ID)
    private integer studentId;

    @column_name(STUDENT_AGE)
    private int studentAge;

    @OneToMany(fetch = FetchType.LAZY, mappedBy="student")
      private List<OptionalCourse> optionalCourse;

}


@NamedEntityGraphs({
@NamedEntityGraph(
        name = "OptionalCourse.student",
        attributeNodes = @NamedAttributeNode("student")
    )
})
@Table("OPTIONAL_COURSES")
class OptionalCourse {

    @Id
    @column_name(ID)
    private Integer id;

    @column_name(STUDENT_ID)
    private Integer studentId;

    @column_name(SUBJECT_NAME)
    private String subjectName;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "STUDENT_ID" , referencedColumnName="STUDENT_ID", insertable = false, updatable = false )
    private Student student;

}


@Repository
@Transactional(rollbackFor = Exception.class)
public interface OptionalCourseRepository extends JpaRepository<OptionalCourse,Integer> {

  @Query(value = "SELECT oc FROM OptionalCourse oc LEFT JOIN FETCH oc.student where oc.studentId > :studentId",
      countQuery = "SELECT count(oc.id) FROM OptionalCourse oc where oc.studentId > :studentId")
  public Page<OptionalCourse> findOptionalSubjectPagesByStudentId(@Param("studentId") Integer studentId, Pageable pageable);

}

Service Method Without Transactional:

public List<CustomBeanClass> retrieveRpdLogRequestsList(Integer studentId, Integer pageIndex, Integer pageResultsSize) {

    Pageable pageWithIndexAndSize = Pageable.unpaged();
    
    if(Objects.nonNull(pageIndex) && Objects.nonNull(pageResultsSize) && pageIndex >= 0 && pageResultsSize > 0) {
      
      pageWithIndexAndSize = PageRequest.of(pageIndex, pageResultsSize);
    }
    
    Page<OptionalCourse> pageData = optionalCourseRepository.findOptionalSubjectPagesByStudentId(studentId, pageWithIndexAndSize);
    
    List<OptionalCourse> pageDataList = pageData.toList();

    pageDataList.forEach(course -> {

          OptionalCourse oc = course;

          //When trying to fetch student info from optional course, when student info is not there then we are getting below error
          oc.getStudent(); **//com.sun.jdi.InvocationException: Exception occurred in target VM occurred invoking method hibernate & org.hibernate.LazyInitializationException: could not initialize proxy [com.xxx.xxx.Student#550] - no Session**
    });
}

Service Method With Transactional:

@Transactional(readOnly = true, propagation = Propagation.REQUIRED)
public List<CustomBeanClass> retrieveRpdLogRequestsList(Integer studentId, Integer pageIndex, Integer pageResultsSize) {

    Pageable pageWithIndexAndSize = Pageable.unpaged();
    
    if(Objects.nonNull(pageIndex) && Objects.nonNull(pageResultsSize) && pageIndex >= 0 && pageResultsSize > 0) {
      
      pageWithIndexAndSize = PageRequest.of(pageIndex, pageResultsSize);
    }
    
    Page<OptionalCourse> pageData = optionalCourseRepository.findOptionalSubjectPagesByStudentId(studentId, pageWithIndexAndSize);
    
    List<OptionalCourse> pageDataList = pageData.toList();

    pageDataList.forEach(course -> {

          OptionalCourse oc = course;

          //When trying to fetch student info from optional course, when student info is not there then we are getting below error
          oc.getStudent(); **// EntityNotFoundException - Its trying to fetch the dependent entity student here**
    });
}

Even though i have used LEFT JOIN FETCH, why it is not returning null straightaway during non existence of student record (i.e. OptionalCourse to Student association) when i make a call to dependent entity (i.e) oc.getStudent(); The query generated was with correct syntax as LEFT OUTER JOIN and in SQLDeveloper it is giving expected results. we are using Oracle database.

What mistake i have done? I was not allowed to use EAGER FETCH strategy. Please help to resolve this issue via Lazy Fetch itself. Thanks in advance !!!!

Christian Beikov Christian Beikov · Accepted Answer · 2021-07-07T06:34:59

I guess you are using the OSIV (open session in view) anti-pattern which is enabled by default in Spring? This is a common problem with that approach. The problem is that your first level cache (persistence context) contains the entity with an uninitialized proxy for the student field. Now, even if you select that entity again and join fetch the association, Hibernate will not replace the object because it has to retain object identity and also will not replace the proxy. To solve this, you will have to clear the persistence context before doing the query by using EntityManager.clear()

LEFT JOIN FETCH not working - Spring data JPA

1 Answers