Menu
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?
712
Limiting query results in JPA is slightly different to SQL; we don't include the limit keyword directly into our JPQL. Instead, we just make a single method call to Query#maxResults, or include the keyword first or top in our Spring Data JPA method name. As always, the code is available over on GitHub.
setMaxResults limits the number of results the query will ever get. setFetchSize tells the jdbc driver how many rows to return in one chunk, for large queries.
The @Table annotation allows you to specify the details of the table that will be used to persist the entity in the database. The @Table annotation provides four attributes, allowing you to override the name of the table, its catalog, and its schema, and enforce unique constraints on columns in the table.
The @Query annotation can only be used to annotate repository interface methods. The call of the annotated methods will trigger the execution of the statement found in it, and their usage is pretty straightforward. The @Query annotation supports both native SQL and JPQL.
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
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
