Currently we have a method where we pass a JPA Query String and fetch both a result set (usually a range from the query) and the full count. The most familiar use case likely being paging of results (showing 11-20 or 542). So we have something like:

So this works great. We need to remember to use "e" as our select target and it fails for significantly more complex queries, but most of our code uses this. But I don't like working with strings and would like to use NamedQueries instead.

But this seems a violation of DRY. Is there a good general way of doing this with NamedQueries such that I don't have to duplicate the query? Should I stick with moving strings around? Is there a clean way to manipulate the Query to convert it to a count?

A JPA compliant solution preferred, but Eclipselink specific is fine, also. We are using EL 2.1.0.

Hmmmm. Having two almost identical queries really feels wrong (or I guess in keeping with the vernacular, smells wrong). It is such a common idiom, not just with websites, but with any kind of app dealing with paging, that I'm rather shocked there isn't a good solution to it.

I put up a simplified version of what we are doing. We handle parametrized calls. I'm assuming that I'd be able to do handle the parameters in the ReportQuery, also?

And to your caching comment, does the string version I included in the original post also suffer from lack of caching? I was under the impression that calling createQuery would allow the call to be cached and reused when identical (minus the parameters) queries were called. And what exactly would be the impact of having non-cached queries?

EclipseLink also supports cursors, if you use a CursoredStream query (Query hint "eclipselink.cursor"="true"), then you will get a Cursor back from the Query and be able to call size() which will trigger a second size query.

For having to named queries you should not need to duplicate any code,

Or if using a dynamic query just have a findEmployeeBySalary(boolean count) method that appends the count if a count to the JPQL or Criteria.

Any NamedQuery will have its generated SQL cached in EclipseLink, this is more efficient as it avoids the cost of generating the SQL. Any dynamic query (Criteria, createQuery(jpql)) may have to generate is SQL every time it is executed. May have to, but normally does not, as EclipseLink also maintains a JPQL parse cache that allows the generated SQL to be cached based on the JPQL string, so as long as parameters are used, then SQL is normally cached. The cost of generating the SQL is not normally significant in terms of the cost of the database access and query execution, but does add CPU utilization load to the mid-tier machine.

Ahhhhh. I was not aware that you could use static Strings in NamedQuery entries. It still feels like a little bit of a workaround, but does avoid the code duplication. I'll try that out.

The cursor information also sounds very interesting. I'll definitely check that out as it sounds like that could be more efficient in terms of working with paged data for more than just the count reason.

And thanks for the details on query caching. It matches more or less with what I thought, though I had assumed that parameterized SQL/JPQL would always get cached.