Open Artur- opened 1 year ago
We could enhance the FetchableFluentQuery
API to cover that usecase returning a Slice
instead of a Page
.
could anybody tell me that has this problem solved or how could I realize this feature?
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.
yeah, finally, I also ended up with a custom repository method implemention.
Just like https://gist.github.com/tcollins/0ebd1dfa78028ecdef0b. displayed.
`
@Override
public Slice
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);
}`
Please also cover this for QuerydslPredicateExecutor.findBy(Predicate predicate, Function<FluentQuery.FetchableFluentQuery<S>, R> queryFunction)
.
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
We could enhance the
FetchableFluentQuery
API to cover that usecase returning aSlice
instead of aPage
.
Any updates on this? Thanks.
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.
@ 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?
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 aList<T> findAll(Pageable pageable)
as it will conflict withfindAll
that returns aPage<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 isPage<T> findAll(Specification<T> spec, Pageable pageable)
which again prevents adding aList<T> findAll(Specification<T> spec, Pageable pageable)
. Also it seems that thefindBy
"hack" cannot be used as a method likeList<T> findBy(Specification<T> filter, Pageable pageable);
will throwIs there another way to accomplish this or could it be added as a feature?