"N+1 selects problem" with JpaRepository method despite using @Fetch(FetchMode.JOIN)

Question

I encountered "N+1 selects problem" despite using @Fetch(FetchMode.JOIN) in a very complex software, for simplicity I created an example published on GitHub. The problem is that I can’t use the JOIN mode to acquire data, the behavior always generates the “N+1 selects problem”. I've a very large dataset in the main table, in my case N is about 10/15k, executing JOIN is mandatory.

Here's the code ( full and executable on GitHub )

public class Student {
    @Id
    private String id;
    private String firstName;
    private String lastName;
//    @ManyToOne
//    @JoinColumn(name = "university_id")
//    private University university;
}

public class University {
    @Id
    private String id;
    private String name;
    private String address;
    @ToString.Exclude
    @OneToMany/*(fetch = FetchType.EAGER)*/
    @Fetch(FetchMode.JOIN)
    private Set<Student> students = new HashSet<>();
}

public interface StudentRepository extends JpaRepository<Student, String> {
}

public interface UniversityRepository extends JpaRepository<University, String> {
}

@RunWith(SpringRunner.class)
@SpringBootTest(classes = SpringBootTestClass.class)
@DataJpaTest
@Log4j2
public class FetchTypeTest {
    @Autowired
    private UniversityRepository repository;

    @Test
    public void findAll() {
        List<University> universityList = repository.findAll(new PageRequest(0, 100)).getContent();
        for (University university : universityList) {
            log.info("{} [{}]", university.getName(), university.getAddress());
            for (Student student : university.getStudents()) {
                log.info("{}, {}", student.getLastName(), student.getFirstName());
            }
        }
    }
}

The expectation is to see 1 and only 1 query that (left outer) join University and Student, but executing the test log show 3 select: 1 on University, 2 on Student (there are 2 universities and 5 student for each university) despite FetchMode.JOIN usage. I tried every passible combination of FetchMode / FetchType, but nothin works

Dependencies:

• Java 8
• spring-boot:1.5.8
• spring-boot-starter-data-jpa:1.5.8
• spring-data-jpa:1.11.18
• hibernate-core:5.0.12
• hibernate-entitymanager:5.0.12

Answer 1

FindAll* methods of spring-data-jpa implemented Queries that ignore fetch modes. When you write a query, you are telling what is joined and what is not joined.

Fetch mode is only taken into account when entity is loaded with methods like findById or when navigating through some other entity graph and loading its associations.

This is the refer

The fetch strategy defined in the mapping document affects:

retrieval via get() or load()

retrieval that happens implicitly when an association is navigated

Criteria queries

HQL queries if subselect fetching is used.

You can define a method like this

  @Query(value = "SELECT u FROM University u LEFT JOIN FETCH u.students s order by u.name")
  Page<Region> findAllUniversitiesWithPagination(Pageable pageable);

But note that this method is paginating in memory

HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory

Following this post to solve that problem.