Set the fetch size with Spring Data

Question

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?

Answer 1

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