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

Question

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").

Solution 1

So the solution I ended up using was to create two @NamedQuerys, one for the result set and one for the count, but capturing the base query in a static string to maintain DRY and ensure that both queries remain consistent. So for the above, I'd have something like:

@NamedQuery(name = "getAccounts", query = "SELECT a" + accountQuery)
@NamedQuery(name = "getAccounts.count", query = "SELECT COUNT(a)" + accountQuery)
.
static final String accountQuery = " FROM Account";
.
  Query q = em.createNamedQuery("getAccounts");
  List r = q.setFirstResult(s).setMaxResults(m).getResultList();
  int count = ((Long)em.createNamedQuery("getAccounts.count").getSingleResult()).intValue();

Obviously, with this example, the query body is trivial and this is overkill. But with much more complex queries, you end up with a single definition of the query body and can ensure you have the two queries in sync. You also get the advantage that the queries are precompiled and at least with Eclipselink, you get validation at startup time instead of when you call the query.

By doing consistent naming between the two queries, it is possible to wrap the body of the code to run both sets just by basing the base name of the query.

Solution 2

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.

Solution 3

oh well you can use introspection to get named queries annotations like:

String getNamedQueryCode(Class<? extends Object> clazz, String namedQueryKey) {
    NamedQueries namedQueriesAnnotation = clazz.getAnnotation(NamedQueries.class);
    NamedQuery[] namedQueryAnnotations = namedQueriesAnnotation.value();

    String code = null;
    for (NamedQuery namedQuery : namedQueryAnnotations) {
        if (namedQuery.name().equals(namedQueryKey)) {
            code = namedQuery.query();
            break;
        }
    }

    if (code == null) {
        if (clazz.getSuperclass().getAnnotation(MappedSuperclass.class) != null) {
            code = getNamedQueryCode(clazz.getSuperclass(), namedQueryKey);
        }
    }

    //if not found
    return code;
}

Comments

[Tim]

Yes. I want to get the full count that the query would result in. So count in the example would not correspond to r.size() or I would just use that. A potential use case is that I want to get a page of results for listing accounts, but in order to let them how many pages total, I would want the total count from the query, even though I'm just retrieving 25 results.

[Devanshu Mevada]

@Tim I've clarified my answer. If it's still not clear, let me know.

[Tim]

Thanks. I've tried to clarify my question. Basically, I know that the modifiers change the query before it is run. What I'm hoping to do is have a single @NamedQuery that acts as the base for returning a subset as well as getting a count, instead of writing one query for the count and one for the return set. Ideally still using @NamedQuery. I'll check out the link you reference.

[Tim]

Checked out the link and it displays the problem I'm trying to avoid. Basically, they have two distinct queries in two different methods to get the objects and get the count. So in one class you have "select object(o) from Item as o" and in another, "select count(o) from Item as o". If you update the former without realizing there is a corresponding count query, you'll end up with inconsistent results. I'd like to define the base query in one place and have the "object(o)" and "count(o)" variations derived. If I were using String for the query, this is pretty easy, but you lose Named benefits.

[Tim]

Not sure how this applies to my question. I want to used NamedQuery for the advantages of precompilation, caching and other optimizations. I want the two queries to be identical except for one returning results and one returning a count. And I want to follow DRY and not copy and paste the conditional clause.

[aroth]

Interesting, I didn't even know that you could reference static finals when configuring annotations. However a solution that didn't require two separate named queries would still be ideal, I think.