Spring data findAll() does not fetch eagerly

Question

I have two entities with unidirectional one to many relationship.

@Entity
public class Basket {

    @Id
    @GeneratedValue
    private Long id;

    private int capacity;
}

@Entity
public class Item {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    @ManyToOne
    private Basket basket;
}

I save couple of objects:

    Basket basket1 = new Basket(100);
    Basket basket2 = new Basket(200);
    Basket basket3 = new Basket(300);
    basketRepository.save(asList(basket1, basket2, basket3));

    Item item1 = new Item("item1", basket1);
    Item item11 = new Item("item11", basket1);
    Item item2 = new Item("item2", basket2);
    Item item22 = new Item("item22", basket2);
    Item item3 = new Item("item3", basket3);
    Item item33 = new Item("item33", basket3);
    itemRepository.save(asList(item1, item11, item2, item22, item3, item33));

    // Loading one item. Basket fetched eagerly.
    itemRepository.findOne(1L);

    // Loading many items. Baskets are not loaded (n+1 select problem).
    itemRepository.findAll();

@ManyToOne annotation uses eager fetch by default. When I load one Item using findOne(), Hibernate generates query with left outer join and Basket is fetched in the same query. However when I use findAll(), Hibernate first fetches all Items and then executes N selects (one per each Basket), so that it leads to (n+1) select problem. Why Hiberante doesn't eagerly fetch Basket objects with findAll() method and how to fix this?

Madhusudana Reddy Sunnapu · Answer 1 · 2018-03-09T12:56:56.067

13

From JPA 2.0 spec, @ManyToOne are by default EAGER.

Now, when you use findAll() it is equivalent to firing a JPQL query like entityManager.createQuery(...) and it by default loads the items first and subsequently for each item it loads the basket entity and causing N+1 problem.

You can follow one of the two approaches:

Override the default query used by specifying @Query annotation on the findAll method and use the query with join like select i from Item i left join fetch i.basket.
Use @NamedEntityGraph with name say basket on Item class and specify which part of the Item graph needs to be loaded eagerly. On the findAll method, use @EntityGraph(value = "basket"). Note that as per spring jpa entity graph, we can also use attributePath to define ad-hoc entity graphs via @EntityGraph without the need of having to explicitly add @NamedEntityGraph to your domain types.

edited Mar 09 '18 at 12:56

answered Mar 09 '18 at 11:55

Madhusudana Reddy Sunnapu

8,064
2
18
29

Or override 'findAll' method and just add `@EntityGrath(attributePaths = "basket")` Spring annotation to make the method load 'basket' eagerly. – Cepr0 Mar 09 '18 at 12:27
Oh yeah..it is `@EntityGraph(value = "basket", type = EntityGraphType.FETCH)`. I was trying it outside spring repo so using setHint manually on entityManger. Updating the answer. – Madhusudana Reddy Sunnapu Mar 09 '18 at 12:36
The second param is not necessary because it's used by [default](https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/EntityGraph.html#type--). And `value` param is for 'named grath'. We must use `attributePaths` param here. – Cepr0 Mar 09 '18 at 12:39
yeah, but default it is FETCH. Not sure if it is value or attributePath, I couldn't try with spring repo right now. – Madhusudana Reddy Sunnapu Mar 09 '18 at 12:42
Got it now. As per https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.entity-graph, we can use `attributePath` to define ad-hoc entity graphs via `@EntityGraph` without the need of having to explicitly add `@NamedEntityGraph` to your domain types. – Madhusudana Reddy Sunnapu Mar 09 '18 at 12:52
This answer is more specific and gives more solutions. ``@EntityGraph`` is definitly the way to go, you shouldn't use ``@Query`` to override default find implementation. – Gonzalo Jan 14 '20 at 12:42

score 4 · Accepted Answer · answered Mar 09 '18 at 12:27

You can override findAll method with @Query annotation in your repository. Below is the sample code

public interface ItemRepository extends CrudRepository<Item, Long> {
    @Override
    @Query("select item from Item item left join fetch item.basket")
    Iterable<Item> findAll();
}

Then you can log your sql queries to see that only one query is made

Hibernate: select item0_.id as id1_1_0_, basket1_.id as id1_0_1_, item0_.basket_id as basket_i3_1_0_, item0_.name as name2_1_0_, basket1_.capacity as capacity2_0_1_ from item item0_ left outer join basket basket1_ on item0_.basket_id=basket1_.id

And before it was

2018-03-09 13:26:52.269  INFO 4268 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select item0_.id as id1_1_, item0_.basket_id as basket_i3_1_, item0_.name as name2_1_ from item item0_
Hibernate: select basket0_.id as id1_0_0_, basket0_.capacity as capacity2_0_0_ from basket basket0_ where basket0_.id=?
Hibernate: select basket0_.id as id1_0_0_, basket0_.capacity as capacity2_0_0_ from basket basket0_ where basket0_.id=?
Hibernate: select basket0_.id as id1_0_0_, basket0_.capacity as capacity2_0_0_ from basket basket0_ where basket0_.id=?

Spring data findAll() does not fetch eagerly

2 Answers2