Is there a way to get the count size for a JPA Named Query with a result set?

user_5284 0
Viewed 42972

I like the idea of Named Queries in JPA for static queries I'm going to do, but I often want to get the count result for the query as well as a result list from some subset of the query. I'd rather not write two nearly identical NamedQueries. Ideally, what I'd like to have is something like:

@NamedQuery(name = "getAccounts", query = "SELECT a FROM Account")
.
.
  Query q = em.createNamedQuery("getAccounts");
  List r = q.setFirstResult(s).setMaxResults(m).getResultList();
  int count = q.getCount();

So let's say m is 10, s is 0 and there are 400 rows in Account. I would expect r to have a list of 10 items in it, but I'd want to know there are 400 rows total. I could write a second @NamedQuery:

@NamedQuery(name = "getAccountCount", query = "SELECT COUNT(a) FROM Account")

but it seems a DRY violation to do that if I'm always just going to want the count. In this simple case it is easy to keep the two in sync, but if the query changes, it seems less than ideal that I have to update both @NamedQueries to keep the values in line.

A common use case here would be fetching some subset of the items, but needing some way of indicating total count ("Displaying 1-10 of 400").

jpql jpa java
3 Answers

Using setFirstResult/setMaxResults do not return a subset of a result set, the query hasn't even been run when you call these methods, they affect the generated SELECT query that will be executed when calling getResultList. If you want to get the total records count, you'll have to SELECT COUNT your entities in a separate query (typically before to paginate).

For a complete example, check out Pagination of Data Sets in a Sample Application using JSF, Catalog Facade Stateless Session, and Java Persistence APIs.

user_706040
Related