FetchMode join makes no difference for ManyToMany relations in spring JPA repositories

Question

I'm trying to do this:

//...
class Person {
    @ManyToMany(fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Set<Group> groups;
//...
}

it generates n+1 queries when I do personRepository.findAll(); through a Spring JPA repository, just as if I didn't have any @Fetch set. (One query first to get all the persons, and then one query per person to fetch the groups).

Using @Fetch(FetchMode.SUBSELECT) works, though! It only generates 2 queries. (One for all persons, and then one for the groups). So hibernate reacts to some fetch parameters, just not the JOIN.

I have also tried removing the EAGER fetching with no luck.

//...
class Person {
    @ManyToMany()
    @Fetch(FetchMode.JOIN)
    private Set<Group> groups;
//...
}

I am using Spring JPA, and this is the code for my repository:

public interface PersonRepository extends JpaRepository<Person, Long> {
}

Does JOIN just not work through Spring JPA, or am I doing something wrong?

I think you should now write an HQL query with join fetch to get result in single query.. — DarkHorse, Oct 03 '13 at 11:48

score 22 · Answer 1 · edited May 23 '17 at 12:33

Going through many forums and blogs to read for your problem (I guess you might have done that before posting it here) I too think that

@Fetch(FetchMode.JOIN) will be ignored if you use the Query interface (e.g.: session.createQuery()) but it will be properly used if you use the Criteria interface.

This is practically a bug in Hibernate which was never resolved. It is unfortunate because a lot of applications use the Query interface and cannot be migrated easily to the Criteria interface.

If you use the Query interface you always have to add JOIN FETCH statements into the HQL manually.

References Hibernate Forum Spring Forum Similar Question 1

Pieter · Answer 2 · 2013-11-30T03:32:01.853

I also couldn't get @Fetch(FetchMode.JOIN) to work when using JPA (although it works fine when using the hibernate Criteria api) and I also couldn't find any examples explaining why, but I can think of a few workarounds.

The most straightforward way to load the Groups eagerly, is to use JPQL:

public interface PersonRepository extends JpaRepository<Person, String>{
  @Query(value = "select distinct p from Person p left join fetch p.groups")
  List<Person> getAllPersons();
}

As you are using spring-data-jpa, you could also load the Groups eagerly by using a Specification. (As of 1.4.x you can chain specs that return null).

final Specification<Person> fetchGroups = new Specification<Person>() {
    @Override
    public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        root.fetch("groups", JoinType.LEFT);
        query.distinct(true);
        return null;
    }
};

If neither of these is an option for you, your best bet is probably to use @Fetch(FetchMode.SUBSELECT).

Another option is to use @Fetch(FetchMode.SELECT) in combination with @BatchSize. @BatchSize helps to solve the problem of the n+1 queries. By tweaking the batch size you can reduce the amount of queries executed to CEIL(n/batch_size)+1.

@Entity
@Table(name = "persons")
public class Person {
  @Id
  String name;

  @ManyToMany(fetch = FetchType.EAGER)
  @BatchSize(size = 20)
  Set<Group> groups = new HashSet<>();
}

@Entity
@Table(name = "groups")
public class Group {
  @Id
  String name;

  @ManyToMany(mappedBy = "groups", fetch = FetchType.LAZY)
  Set<Person> persons = new HashSet<>();
}

public interface PersonRepository extends JpaRepository<Person, String>{}

This mapping results in the following sql when you run personRepository.findAll(); on a database containing 10 persons and @BatchSize set to 5.

Hibernate: 
select
    person0_.name as name1_ 
from
    persons person0_
Hibernate: 
select
    groups0_.persons_name as persons1_1_1_,
    groups0_.groups_name as groups2_1_,
    group1_.name as name0_0_ 
from
    persons_groups groups0_ 
inner join
    groups group1_ 
        on groups0_.groups_name=group1_.name 
where
    groups0_.persons_name in (
        ?, ?, ?, ?, ?
    )
Hibernate: 
select
    groups0_.persons_name as persons1_1_1_,
    groups0_.groups_name as groups2_1_,
    group1_.name as name0_0_ 
from
    persons_groups groups0_ 
inner join
    groups group1_ 
        on groups0_.groups_name=group1_.name 
where
    groups0_.persons_name in (
        ?, ?, ?, ?, ?
    )

Note that @BatchSize also works for collections mapped with FetchType.LAZY.

@Fetch(FetchMode.JOIN) isn't the same as fetch = FetchType.EAGER. While it implies eager loading, it uses a join as the mechanism to get the associated data, which avoids the n+1 selects issue. — , Nov 26 '13 at 02:04
@WillieWheeler I updated my post as it was a little bit confusing. I said that _"FetchMode.JOIN **seems to behave** as FetchType.EAGER"_, but I didn't claim they are the same. FetchType.EAGER only implies eager fetching and it doesn't say anything about the actual fetching mode. I should have said that _"FetchMode.JOIN seems to behave as Fetchmode.SELECT in combination with FetchType.EAGER"_ (when using JPA) — Pieter, Nov 30 '13 at 03:58

FetchMode join makes no difference for ManyToMany relations in spring JPA repositories

2 Answers2

Linked