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

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

    Hi,

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

    query.setFirstResult(pageable.getOffset());
    query.setMaxResults(pageable.getPageSize());

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

    query.setFirstResult(100);
    query.setMaxResults(50);

    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 org.springframework.data.jpa.repository.support.Si mpleJpaRepository is incompatible with java.lang.Iterable findAll() in org.springframework.data.repository.CrudRepository


    hope you can help

    thanks
    Last edited by emmettwalsh; Jun 3rd, 2012, 07:27 PM. Reason: on closer look
Working...
X