How to set fetch-size in Spring-Data-JDBC

Question

I use Spring-Data-JDBC (not JPA) and want to set the fetch-size.

I know that the the classic org.springframwork.jdbc.core.JdbcTemplate has a setFetchSize(int) method, but I have Spring-Data-JDBC repository:

org.springframework.data.repository.Repository;
org.springframework.data.jdbc.repository.query.Query;

/** This is a Spring Data <b>JDBC</b> (JDBC not JPA!). */      
public interface UserDao extends  Repository<User, Long> {
  
    @Query(value = UserRowMapper.SELECT_BY_NAME_STATEMENT
           rowMapperClass = UserRowMapper.class)
    Optional<User> findByName(String name);
}

and this is the relevant part of the configuration:

@Configuration
@EnableJdbcRepositories 
public class AppConfigSpringDataJdbc extends AbstractJdbcConfiguration {
   @Autowired
   private DataSource dataSource;

   @Bean
   public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
       return new NamedParameterJdbcTemplate(this.dataSource);
   }
}

So my question is: How/Where to set the sql query fetch-size when using Spring-Data-JDBC, either by query or in general?

(The common solutions for Spring-Data-JPA will not work, because there is no persistence.xml or javax.persistence.QueryHintannotation because it is JDBC)

In general you can set it by accessing inner jdbc template of `NamedParameterJdbcTemplate` - `jdbcTemplate.getJdbcTemplate().setFetchSize(100);` — Alex, Apr 15 '22 at 13:55

score 3 · Accepted Answer · answered Apr 15 '22 at 14:09

3

You can create NamedParameterJdbcTemplate using custom JdbcTemplate.

@Bean
JdbcOperations jdbcOperations(DataSource dataSource) {
    final var jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.setFetchSize(100);
    return jdbcTemplate;
}

@Bean
NamedParameterJdbcOperations namedParameterJdbcOperations(JdbcOperations operations) {
    return new NamedParameterJdbcTemplate(operations);
}

answered Apr 15 '22 at 14:09

Alex

706
7
16

I did what you recommend in the comment to the question: `@BeanNamedParameterJdbcOperations operations() { var namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource); namedParameterJdbcTemplate.getJdbcTemplate().setFetchSize(this.jdbcRepositoryFetchSize); return namedParameterJdbcTemplate; }` – Ralph Apr 16 '22 at 07:56
@Ralph I'd prefer variant in answer cause it is cleaner and more possibility to customize `jdbcTemplate` – Alex Apr 16 '22 at 09:39

How to set fetch-size in Spring-Data-JDBC

1 Answers1