Spring data findAll() does not fetch eagerly
Solution 1
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=?
Solution 2
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 likeselect i from Item i left join fetch i.basket
.Use @NamedEntityGraph with name say
basket
onItem
class and specify which part of theItem
graph needs to be loaded eagerly. On thefindAll
method, use@EntityGraph(value = "basket")
. Note that as per spring jpa entity graph, we can also useattributePath
to define ad-hoc entity graphs via@EntityGraph without the need of having to explicitly add @NamedEntityGraph to your domain types
.
k13i
Updated on June 25, 2022Comments
-
k13i almost 2 years
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 useseager fetch
by default. When I load oneItem
usingfindOne()
, Hibernate generates query withleft outer join
andBasket
is fetched in the same query. However when I usefindAll()
, Hibernate first fetches allItems
and then executesN selects
(one per eachBasket
), so that it leads to(n+1) select problem
. Why Hiberante doesn't eagerly fetchBasket
objects withfindAll()
method and how to fix this? -
Cepr0 about 6 yearsOr override 'findAll' method and just add
@EntityGrath(attributePaths = "basket")
Spring annotation to make the method load 'basket' eagerly. -
Madhusudana Reddy Sunnapu about 6 yearsOh 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. -
Cepr0 about 6 yearsThe second param is not necessary because it's used by default. And
value
param is for 'named grath'. We must useattributePaths
param here. -
Madhusudana Reddy Sunnapu about 6 yearsyeah, 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 about 6 yearsGot it now. As per docs.spring.io/spring-data/jpa/docs/current/reference/html/…, 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. -
Gonzalo over 4 yearsThis 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.