3

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).

Ozawa Tomohiko
  • 157
  • 1
  • 1
  • 7

2 Answers2

3

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.

Javier Heisecke
  • 1,162
  • 1
  • 11
  • 28
  • This answer is unhelpful. See @bonnarooster answer that explains a workaround and a link to the bug in the spring data jdbc project. – jbaranski Dec 29 '20 at 17:25
1

I believe the accepted answer is referring to Spring Data JPA which does work by returning pages based on a count query derived from the custom query OR manually set via countQuery, no reason for the if/else.

However this flat out does not work in Spring Data JDBC.

https://jira.spring.io/browse/DATAJDBC-554

Workaround provided in link but for reference:

    interface FooRepository extends PagingAndSortingRepository<FooEntity, Long> {
        List<FooEntity> findAllByBar(String bar, Pageable pageable);
        Long countAllByBar(String bar);
    }

And then combining those 2 queries like this:

    List<FooEntity> fooList = repository.findAllByBar("...", pageable);
    Long fooTotalCount = repository.countAllByBar("...");

    Page<FooEntity> fooPage = PageableExecutionUtils.getPage(fooList, pageable, () -> fooTotalCount);
Bonnarooster
  • 31
  • 3