| Getting Results and Count from NamedQuery [message #559683] |
Fri, 17 September 2010 11:01  |
Eclipse User |
|
|
|
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 #630048 is a reply to message #559683] |
Thu, 30 September 2010 09:58  |
| Eclipse User |
|
|
|
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.
|
|
|
| Re: Getting Results and Count from NamedQuery [message #630121 is a reply to message #630048] |
Thu, 30 September 2010 12:58 |
| Eclipse User |
|
|
|
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.
Thanks for all the help/patience.
--Tim
|
|
|
Powered by
FUDForum. Page generated in 0.04784 seconds
Home