CODEWITHSUNDEEP
spring-dataspring-data-jpa
Michael Piefel
Michael Piefel

Reputation: 19968

Set the fetch size with Spring Data

For large result sets, it’s important to increase the fetch size. There have been numerous discussions on how to set the fetch size for Spring’s JdbcTemplate. However, we usually use Spring Data for database access. How can I set the fetch size for a Spring Data query, assuming we use JPA with Hibernate as provider?

Upvotes: 10

Views: 33056

Answers (1)

M. Deinum
M. Deinum

Reputation: 124441

It depends on what you want to set. If you want it globally simply add it as a property to your persistence.xml (or what your way of configuration the EntityManagerFactory is). For hibernate that means adding the hibernate.jdbc.fetch_size property.

<property name="hibernate.jdbc.fetch_size" value="50" />

If you want to specify it for certain queries use query hints from JPA on the Query object.

TypedQuery<Foo> q = em.createTypedQuery("some hql here", Foo.class);
q.setHint("org.hibernate.fetchSize", "100");

Or when using Spring Data JPA use a @QueryHints annotation on the interface method. Can be applied to both methods with and without @Query.

@QueryHints(@javax.persistence.QueryHint(name="org.hibernate.fetchSize", value="50"))
List<Foo> findAll();

Links

  1. Hibernate documentation
  2. Spring Data JPA reference | javadoc
  3. JPA 2 Query Hints javadoc
  4. List of query hints for Hibernate

Upvotes: 26

Related Questions