Create criteria statement, get the result and the row count with one query

Question

I'm trying to fetch results and also the row count inside a single query for pagination.

This is what I got so far:

public List<User> getUsers(int page, int size, boolean somecondition) {


    Criteria criteria = getSession().getCurrentSession().createCriteria(User.class);

    ProjectionList projectionList = Projections.projectionList();

    projectionList.add(Projections.property("name").as("name"));
    projectionList.add(Projections.property("createdAt").as("createdAt"));
    projectionList.add(Projections.property("id").as("id"));
    projectionList.add(Projections.rowCount());
    projectionList.add(Projections.groupProperty("id"));

    criteria.setProjection(projectionList);

    // pseudo Restrictions added
    if (somecondition) {
        criteria.add(Restrictions.isNotNull("email"));
    }

    if (somecondition) {
        criteria.add(Restrictions.isNotNull("name"));
    }

    // Now here do something to get the row count without doing a second query
    long count;

    ScrollableResults scrollableResults = criteria.scroll();
    scrollableResults.last();
    count = scrollableResults.getRowNumber() + 1;
    scrollableResults.close();

    log.info("users count : {}", count);

    criteria.setMaxResults(size);
    criteria.setFirstResult(page);

    criteria.setResultTransformer(new AliasToBeanResultTransformer(User.class));
    List<User> users = criteria.list();

    users.forEach(u -> log.info(u.toString()));

    return users;
}

I read that I could use ScrollableResults to get the row count without a second query, but that does not work, I still see in the console that there are 2 queries.

I'm not trying to get the total results, it's important to get the count of the results that satisfy the conditions.

Any suggestions ?

@ArturoVolpe, I restricted the size to 10 results per page, users.size() would give me always < 10. It would basically not ask for the count in the db but the size of the already fetched list. — AME, Dec 28 '16 at 15:43
How hibernate will know the total size of your restricted query without a new query? This is not posible using SQL, so it's not possible with Hibernate, check this to workarounds http://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied. — Arturo Volpe, Dec 28 '16 at 17:54
@ArturoVolpe, I was able to achieve that with a subquery inside my query, I simply don't know how to do that with hibernate. — AME, Dec 28 '16 at 18:29

score 0 · Answer 1 · answered Jun 14 '18 at 05:57

This is the way I used to get count and result with one query.

def result = Domain.createCriteria().list(max:10, offset:20){
                //conditions
             }
def count = result.totalCount

It gives me total count of the rows unaffected by limits and offsets. Hope it helps!

Create criteria statement, get the result and the row count with one query

1 Answers1