Spring Data JDBC - Pageable on custom Query

Question

On my Project I have a Repository that extends CrudRepository. Inside there I have a custom query:

    public interface CustomerRepository extends CrudRepository<Customer, Long> {
       @Query("select * from person where firstname = :firstname")
       List<Customer> findByFirstname(@Param("firstname") String firstname, Pageable pageable);
    }

in my Service-Class I try to put the List in a Pageable - Object like:

... getPageableCustomer(String firstname, Pageable pageable){
// => By using "Sol" I got 90 matching entries 
List<Customer> custList = customerRepository.findByFirstname(firstname, pageable);

Page<Customer> custPage = new PageImpl<Customer>(custList, pageable, custList.size());

return custPage;
}

the return value includes the complete List "custList". What would be the best way to get a pageable object with specified offset and size?

One option could be to use

customer.subList(fromIndex, toIndex)

but that feels wrong. Also because of Loading all Data inside the list instead of just getting data by size and offset as parameterized with pageable.

Remark: In case of using Page inside the Repository I ´ll get org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 88

There is also a open Improvement on Jira that could be found here: https://jira.spring.io/browse/DATAJDBC-554?filter=-3

hope for some help...

Answer 1

I got a response on the JIRA-Issue from Dirk Luijk (Thx Dirk :))

https://jira.spring.io/browse/DATAJDBC-554?filter=-3

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

"the mistake in your workaround is your custom query. In Spring Data JDBC 2.0 you don't need to use that, except for special queries but they won't support pageables."

Possible Parameters could be found:

https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.query-methods

Thx Dirk,

I also find a workaround to get it running with a custom Query. Just use limit, offset and orderBy as additional Parameter like so:

    @Query("select * from person where firstname = :name order by :order limit :size offset :offset")
    List<Customer> findByFirstNameCustomQuery(@Param("name") String name, Pageable page, @Param("offset") long offset,
            @Param("size") long size, @Param("order") String order);

And than change the call inside the Service like:

List<Customer> custList = customerRepository.findByFirstNameCustomQuery(firstname, pageable, ....