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

[Tim Hollosy <hollosyt@xxxxxxxxx>]

So because EclipseLink uses it's native Expression API on the backend,
you should be able to clone CriteriaQueries and modify what sort of
results they return?

It's a shame that didn't make it into the official spec, I saw someone
wanting to do this in a Hibernate bug report, and the hibernates dev
simply stated it's not part of the JPA 2.0 spec so go ahead and use
the Hibernate native projection stuff to accomplish this.

How would I go about lobbying for a change to the JPA 2.0 spec? I'd
like to see JPA 2 succeed, but if you can't build a full featured
application using only the JPA 2.0 spec people will continue to use
their implementation of choice keeping the community fractured on two
separate API's.

./tch



On Tue, Jun 8, 2010 at 9:55 AM, James Sutherland <jamesssss@xxxxxxxxx> wrote:
>
> 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.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>