Does OneToMany(fetch=FetchType.EAGER) executes N+1 queries

Question

I am developing an application by using spring boot, spring data and Hibernate. I have an entity "Client" that has a relationship "onetomany" with another entity "Reservation", as follows:

@Entity
public class Client implements Serializable {

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

    @OneToMany( mappedBy = "client", fetch=FetchType.EAGER)
    @Fetch(value = FetchMode.JOIN)
    private Set<Reservation> reservations = new HashSet<>();
}

I have implemented JpaRepository inteface so I can manipulate the client data:

public interface ClientRepository extends JpaRepository<Client, Long> {}

In a service class, I have implemented a method to find all the clients in my Database:

@Service
public class ClientService {

@Autowired
private ClientRepository clientRepository;

@Transactional(readOnly = true)
public List<Client> findAll() {

    return clientRepository.findAll();
}}

The execution of findAll() works well and returns all the clients and their reservations. However, im my sql log, I have N+1 queries that have been executed (N the count of clients), although that I have set fetch=FetchType.EAGER in my Client entity. I thought that hibernate will create a single query in which it joins all necessary data to retreive the clients and their reseravations.

So, I am forced to explicitly join client and reservation with a query:

    @Query("select client from Client client left join fetch client.reservations")
public List<Client> findAllEager();

I have also found another alternative which permits to execute only two queries to retrieve all the clients and their reservations:

    @OneToMany(mappedBy = "client", fetch = FetchType.EAGER)
@Fetch(value = FetchMode.SUBSELECT)

I note that from this discussion JPA eager fetch does not join, it appears that @OneToMany(fetch=FetchType.EAGER) @Fetch(value = FetchMode.JOIN) will execute only one query to retrieve the result which is not my case.

Does OneToMany(fetch=FetchType.EAGER) executes N+1 queries to fetch data?

Answer 1

@OneToMany(fetch=FetchType.EAGER) only defines when the related entities are fetched , it does not defines how the entities are fetched. (i.e whether they are fetched by a single joined SQL or many separated SQL) ,so it may execute N+1 queries.

@Fetch(value = FetchMode.JOIN) defines how the related entities are fetched However, it only works when the entities are fetched directly using ID (i.e through entityManager.find(Client.class, 100) but will have no effect on JPQL query or Criteria API. (As said by Hibernate user guide as below ):

The reason why we are not using a JPQL query to fetch multiple Department entities is because the FetchMode.JOIN strategy would be overridden by the query fetching directive.

To fetch multiple relationships with a JPQL query, the JOIN FETCH directive must be used instead.

Therefore, FetchMode.JOIN is useful for when entities are fetched directly, via their identifier or natural-id.

Spring data internally use Criteria API for the findAll() query , so @Fetch(value = FetchMode.JOIN) will not have any effect. You have to explicitly use the JOIN FETCH query to avoid the N+1 queries.