| Getting Results and Count from NamedQuery [message #559683] |
Fri, 17 September 2010 15:01  |
 Tim
Messages: 21
Registered: June 2010 |
Junior Member |
|
|
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:
fetchEmployees(String query, int start, int max) {
String countQuery = query.replaceFirst(" e ", " count( e ) ");
List result = em.createQuery(query).setFirstResult(start).setMaxResults(max).getResultList();
int count = em.createQuery(countQuery).getSingleResult().intValue();
.
.
.
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.
My ideal scenario would be something like:
fetchEmployees(String queryName, int start, int max) {
Query query = em.createNamedQuery(queryName);
List result = query.setFirstResult(start).setMaxResults(max).getResultList();
int count = query.getCount();
But such a (useful) method does not seem to exist. I could create two named queries and have:
fetchEmployees(String queryName, int start, int max) {
Query query = em.createNamedQuery(queryName);
Query countQuery = em.createNamedQuery(queryName + "-count");
List result = query.setFirstResult(start).setMaxResults(max).getResultList();
int count = countQuery.getSingleResult().intValue();
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.
Thanks.
--Tim
|
|
|
|
| Re: Getting Results and Count from NamedQuery [message #629240 is a reply to message #559683] |
Mon, 27 September 2010 15:01  |
|
Having two named queries is the best way to do this.
A dynamic solution would be to create a ReportQuery with a count from the named query, but this would not have the benefit of caching the prepared SQL as named queries do.
i.e. something like
ReportQuery reportQuery = new ReportQuery();
reportQuery .copyFromQuery(((JpaQuery)query).getDatabaseQuery());
reportQuery.addCount();
query.setDatabaseQuery(query);
long count = (Long)query.getSingleResult();
James : Wiki : Book : Blog : Twitter
|
|
|
|
| Re: Getting Results and Count from NamedQuery [message #630048 is a reply to message #559683] |
Thu, 30 September 2010 13:58 |
|
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,
i.e.
static final String FIND_BY_SALARY = " from Employee e where e.salary > :salary";
@NamedQuery(name="findEmployeeBySalary" query="Select e" + FIND_BY_SALARY),
@NamedQuery(name="countEmployeeBySalary" query="Select Count(e)" + FIND_BY_SALARY)
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.
James : Wiki : Book : Blog : Twitter
|
|
|
|
Powered by
FUDForum. Page generated in 0.03964 seconds
] 
Search
Help
Register
Login
Home
