[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
Re: [eclipselink-users] ReadAllQuery/ReportQuery count..
|
can someone suggest a fix for this ? I am struck with this problem and
i dont like the kludge particularly :)
On Fri, Aug 17, 2012 at 9:30 AM, vaidya nathan <vaidyaatdst@xxxxxxxxx> wrote:
> Good Morning Eclipselink users,
>
> Am having problems doing a count on a query that fetches objects using
> aggregate functions. I would like to use the query object that i use
> for getting the objects to do a count and am not having success . Lets
> say, I have a following entity called
>
> public class BaseEntity //Base Entity which gets inherited by other Entities
> {
> private Long int;
> private Date recorded;
> }
>
> @Entity
> public class BusClass extends BaseEntity
> {
> }
> and when i query on the BusClass this is how i do
>
> public BusClass retrieveBusClass(Paginationinfo pI)
> {
> ExpressionBuilder eb = new ExpressionBuilder(qbe.getClass()) ;
> ReportQuery query = new ReportQuery(eb) ;
> QueryByExamplePolicy qbePolicy = new QueryByExamplePolicy() ;
> query.setExampleObject(qbe) ;
> query.setQueryByExamplePolicy(qbePolicy) ;
> query.setShouldFilterDuplicates(true) ;
> query.setDistinctState(ReportQuery.USE_DISTINCT) ;
> query.addAttribute("id") ;
> query.addGrouping("id") ;
> query.addMaximum("recorded") ;
> Query jpaq = JpaHelper.createQuery(query, getEntityManager());
> List<ReportQueryResult> results = jpaq.getResultList() ;
> pI.setCount(getCount(query)) ;;// this is where my problem is ..
> }
>
> and this is the query i get finally when i turn on logging..
> SELECT DISTINCT ID, MAX(RECORDED) FROM BUSCLASS GROUP BY ID
>
> I need to get a total row Count on this and that's where my problem is
> lets say i want to generalize the getCount in one place , following is
> how my method looks like
>
> Integer getCount(ReportQuery reportQuery)
> {
> reportQuery.addCount();
> Query jpaq = JpaHelper.createQuery(query, getEntityManager());
> countObject = jpaq.getSingleResult();
> }
>
> The problem is that this gives the following sql
> SELECT DISTINCT ID, MAX(RECORDED),COUNT FROM BUSCLASS GROUP BY ID
>
> whereas i was expecting it to give
> select count(*) from (SELECT DISTINCT ID, MAX(RECORDED) FROM BUSCLASS
> GROUP BY ID)
>
> The only way in which i am able to get the above statement is if i use
> native query something like this
>
> Integer getCount(ReportQuery query)
> {
> if (query.getSQLString() != null)
> {
> q1 = getEntityManager().createNativeQuery("select
> count(*) from (" + query.getSQLString() + ")");
> countObject = q1.getSingleResult();
> }
> }
>
> Is there a way in which i can use the ReportQuery itself to achieve
> what i want to do instead of using nativeQuery since native queries is
> generally intended to provide support for those cases where it is
> necessary to use the native SQL of the target database in use .
>
> Also per the links
> http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Sub-selects_in_FROM_clause
> we can possibly use subselects to achieve what i want to achieve but
> we are not using jpql and i cant see an example of using subselects
> using ReportQuery/ReadAllQuery which we are currently using..Also i am
> not sure whether ReportQuery supports subselects (Didnt see a
> ReportQuerySubSelect class for example or examples of how to use
> ReportQuery itself to achieve what i want to achieve)
>
> I want to use the same Query that i am using to read data to get a
> count(without the pagination info which i later add). Is this
> possible?
>
> Cheers
> Vaidya