Eclipse Community Forums: EclipseLink » Getting Results and Count from NamedQuery

Help

Home

Home » Eclipse Projects » EclipseLink » Getting Results and Count from NamedQuery

Show: Today's Messages :: Show Polls :: Message Navigator

Getting Results and Count from NamedQuery [message #559683]

Fri, 17 September 2010 11: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

Report message to a moderator

Re: Getting Results and Count from NamedQuery [message #628771 is a reply to message #559683]

Fri, 24 September 2010 01:30

Tim
Messages: 21
Registered: June 2010

Junior Member

It seems hard to believe that this is a unique issue and/or there isn't a standard solution for it, without having to do some kind of preprocessing or other tricks outside the code.

I know that there is a MySQL call to return the count from the previous query, but I'd like to use something portable.

Any help or pointers greatly appreciated.

--Tim

Report message to a moderator

Re: Getting Results and Count from NamedQuery [message #629240 is a reply to message #559683]

Mon, 27 September 2010 11:01

James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

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

Report message to a moderator

Re: Getting Results and Count from NamedQuery [message #629293 is a reply to message #629240]

Mon, 27 September 2010 12:58

Tim
Messages: 21
Registered: June 2010

Junior Member

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?

Thanks for the informative reply.

--Tim

Report message to a moderator

Re: Getting Results and Count from NamedQuery [message #630048 is a reply to message #559683]

Thu, 30 September 2010 09:58

James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

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

Report message to a moderator

Re: Getting Results and Count from NamedQuery [message #630121 is a reply to message #630048]

Thu, 30 September 2010 12:58

Tim
Messages: 21
Registered: June 2010

Junior Member

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

Report message to a moderator

Previous Topic:	Column "rowid" cannot be resolved
Next Topic:	EclipseLink JPA2.0 orm table name prefix help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 12 14:45:34 EDT 2014