Get rowCount before execute query JPA

Question

I'm trying to build a Pagination inside my system. In DAO i'm using "setFirstResult()" and "setMaxResults()" to limit the amount of rows returned.

Look:

Query query = entityManager.createNamedQuery(namedQuery);

        if (firstResult != null) {
        query.setFirstResult(firstResult);
        }

        if (maxResult != null) {
        query.setMaxResults(maxResult);
        }

        List returnList = query.getResultList();

But for pagination works i need to know the amount of rows without limitation (firstResult() and maxResults()).

If i have this query:

SELECT * FROM MyEntity e WHERE e.car = :carParam OFFSET 10 LIMIT 20

i would like to count like this

SELECT Count(*) FROM MyEntity e WHERE e.car = :carParam

But I want to avoid create another query manually for each Entity, how can i do a count() without force creating a new count() query ?

possible duplicate of [Total row count for pagination using JPA Criteria API](http://stackoverflow.com/questions/5349264/total-row-count-for-pagination-using-jpa-criteria-api) — zaerymoghaddam, Feb 16 '15 at 18:44
You can't. You must issue a separate `count(*)` since SQL queries don't intrinsically return the total number of matching records. — fps, Feb 16 '15 at 18:51

score 0 · Accepted Answer · answered Feb 16 '15 at 19:07

There is no way to calculate the total count of results without using Criteria API or (as you said) creating another query manually. Both of them will cause to a separate query against database to calculate the total counts. I had some experiences in this context. It may doubles your response time if your query is going to be run on a huge data set or if you have a large number of concurrent users.

I think the best way to prevent such an overhead on your DBMS is to display a "load more" link at the end of the search results. I highly recommend such approach if displaying total count is not part of your main business or is not forced by the client.

Take a look at this link (if you've not checked it before)

The "load more" really is the best solution, i didn't think about it. I will use this solution, thanks. — Shelly, Feb 16 '15 at 19:12

Get rowCount before execute query JPA

1 Answers1