Get Count and result in one query JPA

Question

I'm new to Hibernate and JPA. I'm trying to implement pagination with filtering. Right now for this reason I use 2 query:

        query = em.createQuery("SELECT l from LoanAccount l where l."+filterColumn +" LIKE :filterValue  and l.assignedBranchKey=:TaskID order by l." + orderByColumnID +" " + orrderByASC);
        q = em.createQuery("SELECT count(l.id) from LoanAccount l where l."+filterColumn +" LIKE :filterValue and l.assignedBranchKey=:TaskID");
        query.setParameter("filterValue", "%"+filterValue+"%");
        q.setParameter("filterValue", "%"+filterValue+"%");
        q.setParameter("TaskID", TaskID);
        query.setParameter("TaskID", TaskID);

        query.setFirstResult(page*count-count);
        query.setMaxResults(count);


        result.setMaxResults((Long)q.getSingleResult());
        result.setData(query.getResultList());

        return result;

So I'm setting first and last results in first query so I can't get list and get it's size. That's why I created second query and use it without setting first and last result - to get exact count of rows for pagination.

Page - which page is user sees now

count - how many rows should be in that page

is it possible to combine this 2 query ? if yes how should I do that?

Answer 1

Hey you could also use ScrollableResults

Here is an example (code is not tested):

String hql = "SELECT l from LoanAccount l where l."+filterColumn +" LIKE :filterValue  and l.assignedBranchKey=:TaskID order by l." + orderByColumnID +" " + orrderByASC;
Query query = session.createQuery(hql);
query.setParameter("filterValue", "%"+filterValue+"%");
query.setParameter("TaskID", TaskID);
int pageSize = 10;

ScrollableResults resultScroll = query.scroll(ScrollMode.FORWARD_ONLY);
resultScroll.first();
resultScroll.scroll(0);
List<LoanAccount> loanAccountPage = Lists.newArrayList();
int i = 0;
while (pageSize > i++) {
    loanAccountPage .add((LoanAccount ) resultScroll.get(0));
    if (!resultScroll.next())
        break;
}

Answer 2

You actually can't combine these 2 queries at once. But you can save some code / mistakes by using this JpaUtils library to generate the count query :

Long count = JpaUtils.count(entityManager, criteriaQuery);

More info here => https://stackoverflow.com/a/9246377/5611906