Re: [eclipselink-users] Dynamically get a Count for any CriteriaQuery -

[James Sutherland <jamesssss@xxxxxxxxx>]

This should work in EclipseLink.

The issue seems to be your where clause is a Expression not a Predicate, so
the isTrue() operation is used.  Which does not seem to be working in
Oracle.  Please log a bug for this and vote for it, the issue is that
isTrue/isFalse do not seem to work, or that getRestriction() uses isTrue to
convert an Expression to a Predicate.

A workaround should be to set your where clause to a predicate to avoid the
isTrue.

i.e. instead of 
>> cq.where(cb.like(client.<String>get("lastName"), search.getLastName()));

use,
cq.where(cb.and(cb.conjunction(), cb.like(client.<String>get("lastName"),
search.getLastName())));

or when you get the restrictions get the first expression to pass to the
where.


tch wrote:
> 
> I'd like to start to use the new Criteria API rather than
> EclipseLink's Expression API to make my code more portable. Here's the
> issue that I'm running into however.
> In my current toolbox, I have a dao method that can take any
> ReadAllQuery and get a count query for it. This is great for paging.
> 
> 
> My Expression API based method looks like this:
> 
> public BigDecimal getCountQuery ( Class clazz, Expression eb  ) {
> if( eb == null ) {
> eb = new ExpressionBuilder();
> }
> ReportQuery rq = new ReportQuery(clazz, eb);
> rq.addCount();
> rq.setShouldReturnWithoutReportQueryResult(true);
> Vector reportRows = (Vector) JpaHelper.getEntityManager(
> getEntityManager()).getActiveSession().executeQuery(rq);
>         Integer count = null;
> if (reportRows != null) {
>       return  (BigDecimal) reportRows.get(0);
> }
> return null;
> }
> 
> 
> 
> I took a stab at doing the same thing with a the CriteriaQuery API,
> and just as an example -- this is not generic:
> 
>  CriteriaBuilder cb  = getEntityManager().getCriteriaBuilder();
> 
>               CriteriaQuery<Client> cq = cb.createQuery(Client.class);
> 
> 
>               Root<Client> client = cq.from(Client.class);
> 
>               cq.where(cb.like(client.<String>get("lastName"),
> search.getLastName()));
> 
>               List<Client> clients =
> getEntityManager().createQuery(cq).getResultList();
> 
>               CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
>               countQuery.select(cb.count(client));
>               countQuery.where(cq.getRestriction());
> 
>               Long count =
> getEntityManager().createQuery(countQuery).getSingleResult();
> 
> 
> The first query looks great, the second query ends up like:
> 
> SELECT COUNT(ID) FROM CLIENT WHERE ((LAST_NAME LIKE ?) = ?)
> and gets bound with '%whatever%', true
> 
> which is not a valid oracle query.
> 
> 
> What I require is a way to easily get a count for any CriteriaQuery.
> This is essential if you're trying to build a mature web application
> in the real world.
> 
> I saw some people in hibernate's jira asking for this, and they cite a
> rule in the jpa 2.0 spec saying you can't copy predicates from one
> CriteriaQuery to another.
> 
> Is there really no way to do that in the current API? If so is there
> some other way I can get the backing EL expression from a
> CriteriaQuery so I can use my old count query, that'd be better than
> nothing.
> 
> Thanks!
> 
> ./tch
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://old.nabble.com/Dynamically-get-a-Count-for-any-CriteriaQuery---is-it-possible--tp28784258p28818197.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.