Sunday, June 24, 2012

Despite being almost ten years old, the JPA specification to this day has rather poor support for basic paging/sorting/filtering. Paging/sorting/filtering is used in a lot of (CRUD) applications where the result from a query is shown in a table, and where the user can scroll through the results one page at a time, and where this result can be sorted by clicking on any of the table column headers.

In order to support this a number of things are generally needed:

The total number of rows (or entities) in the full result must be known

There should be support for an offset in the full result and a limit for the amount of rows that will be obtained

The column (attribute) on which to sort must be dynamically added to the query

Search expressions must be dynamically added to the query

As it appears, only offset/limit is directly supported in JPA. A sorting column can only be added dynamically when using the overly verbose and hard to work with Criteria API. Search expressions are somewhat possible to add via the Criteria API as well, but it's an awkward and rather poor mechanism.

Surprisingly, universally counting the number of rows is not possible at all in JPA. In this article we'll look at a very hairy workaround for this using Hibernate specific code.

Strange as it may seem, this query is uncountable in JPA, while in SQL this is usually not a problem. So what we could do is generate the corresponding SQL query, surround it by an outer count(*) query and then execute that.

But here we hit another wall. While by definition every JPA implementation must be able to generate SQL from a JPA query, there's no actual standard API to get just this query text.

Now one particular aspect of JPA is that it's almost never a pure implementation (such as e.g. JSF), but a standardization API layered on top of another API. This other API is typically richer. In the case of Hibernate there indeed appears to be a public API available to do the transformation that we need, including handling query parameters (if any).

To demonstrate this, let's first create the Query object in Java. Here we assume that the JPQL query shown above is available as a query named "Statistic.perDate":

From this typed query we can obtain the Hibernate Query, and from that get the query string. This query string always represents the JPQL (technically, HQL) independent of whether the query was created from JPQL or from a Criteria:

In order to parse this JPQL (HQL) query text we need to make use of the ASTQueryTranslatorFactory. Using this and the JPA EntityManagerFactory one can get hold of the SQL query text and a collection of parameters:

Note that the +1 on the position is needed because of a mismatch between 0-based and 1-based indexing of both APIs.

With all this in place we can now finally execute the query and obtain the count:

Long cnt = ((Number) nativeQuery.getSingleResult()).longValue();

The casting here looks a big nasty. In the case of PostgreSQL a BigInteger was returned. I'm not entirely sure if this would be the case for all databases, hence the cast to Number first and then getting the long value from that.

Conclusion

Using the Hibernate specific API it's more or less possible to universally count the results of a query. It's not entirely perfect still, as values set on a JPQL query can often be richer than those set on a native query. For example, you can often set an entity itself as a parameter and the JPA provider will then automatically use the ID of that.

Furthermore using provider specific APIs when using JPA, especially for such an essential functionality, is just not so nice.

Finally, some providers such as EclipseLink do support subqueries in the select clause. For those providers no vendor specific APIs have to be used (and therefor there are no compile time concerns), but the code is of course still not portable.

If/when there will ever be a new JPA version again it would really be nice if the current problems with paging/sorting/filtering could be addressed.