Add the possibility to fetch using Specification and Pageable but without doing a count query

[Artur-]

If you use Page<T> findAll(Pageable pageable) then Spring Data will execute the select plus the count query for every call. To get rid of the count query when you do not need the total count, you can add a find by without any filters: List<T> findBy(Pageable pageable). You cannot add a List<T> findAll(Pageable pageable) as it will conflict with findAll that returns a Page<T>.

However when you use a Specification for filtering you still would want the same possibility: to get rid of the count query.
The available findAll is Page<T> findAll(Specification<T> spec, Pageable pageable) which again prevents adding a List<T> findAll(Specification<T> spec, Pageable pageable). Also it seems that the findBy "hack" cannot be used as a method like List<T> findBy(Specification<T> filter, Pageable pageable); will throw

org.springframework.dao.InvalidDataAccessApiUsageException: At least 1 parameter(s) provided but only 0 parameter(s) present in query

Is there another way to accomplish this or could it be added as a feature?

[christophstrobl]

We could enhance the FetchableFluentQuery API to cover that usecase returning a Slice instead of a Page.

[Dylan0405]

could anybody tell me that has this problem solved or how could I realize this feature?

[gedankennebel]

We also tried and searched everything to use Specifiactions along with a sql limit. Unfortunatley no method seems to offer us this usecase.
Eventually we ended up with a custom repository method implemention, described like in the spring jpa documenation.
In the implemenation we extended from SimpleJpaRepository and could make use of the getQuery(..) method.

   @Override
    public List<Event> findAllEvents(Specification<Event> specification, Sort sort, int limit) {
        var query = getQuery(specification, sort);

        if (limit > 0) {
            query.setMaxResults(limit);
        }
        return query.getResultList();
    }

Note that Event is our entity object and nothing Spring related.

[Dylan0405]

yeah, finally, I also ended up with a custom repository method implemention.
Just like https://gist.github.com/tcollins/0ebd1dfa78028ecdef0b. displayed.
`
@OverRide
public Slice findAllSliced(Specification spec, Pageable pageable) {
TypedQuery query = getQuery(spec, pageable.getSort());

    query.setFirstResult((int) pageable.getOffset());
    int extraSize = pageable.getPageSize() + 1;
    query.setMaxResults(extraSize);

    List<T> result = query.getResultList();
    boolean hasNext = result.size() == extraSize;

    if(hasNext){
        result.remove(extraSize - 1);
    }
    return new SliceImpl<>(result, pageable, hasNext);
}`

[membersound]

Please also cover this for QuerydslPredicateExecutor.findBy(Predicate predicate, Function<FluentQuery.FetchableFluentQuery<S>, R> queryFunction).

[ManeeshSharma17]

yeah, finally, I also ended up with a custom repository method implemention. Just like https://gist.github.com/tcollins/0ebd1dfa78028ecdef0b. displayed. ` @OverRide public Slice findAllSliced(Specification spec, Pageable pageable) { TypedQuery query = getQuery(spec, pageable.getSort());

    query.setFirstResult((int) pageable.getOffset());
    int extraSize = pageable.getPageSize() + 1;
    query.setMaxResults(extraSize);

    List<T> result = query.getResultList();
    boolean hasNext = result.size() == extraSize;

    if(hasNext){
        result.remove(extraSize - 1);
    }
    return new SliceImpl<>(result, pageable, hasNext);
}`

This is escaping some records in the result set

[kmeyer-mbs]

We could enhance the FetchableFluentQuery API to cover that usecase returning a Slice instead of a Page.

Any updates on this? Thanks.

[max-buildops]

If you use MySQL (other DBs might have it as well), your second query could be a super cheap one: SELECT FOUND_ROWS();
Assuming your original statement has LIMIT as part of your select (SELECT ... LIMIT pageSize), above subsequent query returns a number of rows that would be returned if there was no LIMIT clause.
https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_found-rows

Hope this helps.

[membersound]

@ Spring-Team: could the found_rows() function be a better alternative to the implicit executed count()? Maybe by activation with a parameter on the Repository or class-method?

[mp911de]

Fixed via #3727