Announcement Announcement Module
No announcement yet.
findAll(Spec, Pgble) - possible to avoid count query bt still pass offset and limit? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • findAll(Spec, Pgble) - possible to avoid count query bt still pass offset and limit?


    Im using JpaSpecificationExecutor and its working out great as I call one findAll(Specifications.where()) method instead of probably 20 slightly different queries or lots of messy concatenated hql strings.

    However, as Im querying a massive table the count query seems to be causing a slight bottleneck . (I extracted generated sql and ran all queries manually to prove this).

    select count(*) as col_0_0_ from ql_category_item_event categoryit0_ where categoryit0_.categoryId=1 and categoryit0_.photoId is not null limit 25;
    - takes 3 SECONDS

    select * from ql_category_item_event categoryit0_ where categoryit0_.categoryId=1 and (categoryit0_.photoId is not null) order by categoryit0_.createDate desc limit 25,25;
    takes 3 MILLISECONDS

    In reality I dont really need to know the number of pages, i just want to page for page 0,1,2 with maxresults X, if results come back great if not I can handle this from the caller..

    I know theres a List<T> findAll(Specification<T> spec); version of the method, but how can I pass an offset and page number so that I can retrieve the results ?

    is it possible to define a Specification that specifys the limit and offset ?

    after looking some more it seems it all boils down to this method of SimpleJPARepository:

    private Page<T> readPage(TypedQuery<T> query, Pageable pageable, Specification<T> spec) {


    Long total = getCountQuery(spec).getSingleResult();
    List<T> content = total > pageable.getOffset() ? query.getResultList() : Collections.<T> emptyList();

    return new PageImpl<T>(content, pageable, total);

    so I need to implement a method like this somehow..

    public Page<?> findAllWithoutImplicitCount(Specification<?> spec, Pageable pageable) {

    TypedQuery<CategoryItemEvent> query = getQuery(spec, pageable);


    Iterable<?> content = query.getResultList()

    return new PageImpl<T>(content, pageable, 9999999) //i.e. dont care about count, can be infinite

    Im following examples on how to implement a custom repo, but I keep getting following error:

    The return type of java.util.List findAll() in mpleJpaRepository is incompatible with java.lang.Iterable findAll() in

    hope you can help

    Last edited by emmettwalsh; Jun 3rd, 2012, 06:27 PM. Reason: on closer look