Spring data JDBC query creation with pagination complains IncorrectResultSizeDataAccessException: Incorrect result size

Question

I'm struggling to trying the pagination feature, as described in the reference document.
This is my table schema:

CREATE TABLE cities
(
    id      int PRIMARY KEY,
    name    varchar(255),
    pref_id int
);

Repository:

public interface CityRepository extends CrudRepository<CityEntity, Integer> {

  Page<CityEntity> findAll(Pageable pageable);

  // get all cities in the prefecture
  Page<CityEntity> findByPrefId(Integer prefId, Pageable pageable);
}

Test code:

Page<CityEntity> allCities = repository.findAll(PageRequest.of(0, 10));
Page<CityEntity> cities = repository.findByPrefId(1, PageRequest.of(0, 10));

findAll works well, but findByPrefId throws the following error:

Incorrect result size: expected 1, actual 10
org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 10
    at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:100)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:237)
    at org.springframework.data.jdbc.repository.query.AbstractJdbcQuery.lambda$singleObjectQuery$1(AbstractJdbcQuery.java:115)
    at org.springframework.data.jdbc.repository.query.PartTreeJdbcQuery.execute(PartTreeJdbcQuery.java:98)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130)
...

If I change the method signature into List<CityEntity> findByPrefId(Integer prefId, Pageable pageable), it works.

Am I missing something? I'm using the latest version of spring-data-jdbc (2.0.2.RELEASE).

Answer 1

I don't know about the technicality, but this is what I learned from experience.

In your case, if the total number of cities is lesser than the pageable.getPageSize(), then your repository will return a List<>.

But if total number of cities is bigger than the pageable.getPageSize() then your repository will return a Page<>.

Knowing that, this is what I did to work around it.

    Long amount = repository.countByPrefId(prefId);
    if(pagination.getPageSize()>amount ) {
       List<CityEntity> list = repository.findByPrefId(prefId);
    } else {
       Page<CityEntity> pages = repository.findByPrefId(person, PageRequest.of(0, 10));
    }

This also means that in your repository you'll have two differents methods, one with Pageable as a parameter and one with only PrefId as a parameter.