Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

java

jpa

jpql

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

like image 994
Tim Avatar asked Feb 17 '10 23:02

Tim


People also ask

How does JPA get count of records?

count() repository method If you want to know the total number of rows available in the entity table, use the count derived method of the CrudRepository interface. For example, the following getCustomerCount method retrieves the number of entities available in the table using the method count.

Can we use limit in JPA query?

Conclusion. Limiting query results in JPA is slightly different to SQL; we don't include the limit keyword directly into our JPQL. Instead, we just make a single method call to Query#maxResults, or include the keyword first or top in our Spring Data JPA method name. As always, the code is available over on GitHub.

What is the limit clause alternative in JPQL?

When using native JPQL you should use setMaxResults to limit the results.


3 Answers

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.

like image 76
Tim Avatar answered Oct 11 '22 00:10

Tim


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.

like image 43
Pascal Thivent Avatar answered Oct 11 '22 02:10

Pascal Thivent


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;
}
like image 29
alessandro Avatar answered Oct 11 '22 00:10

alessandro