[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] ReadAllQuery/ReportQuery count..
|
JPQL query:
SELECT e.id, c.city FROM Employee e, (SELECT DISTINCT a.city FROM
Address a) c WHERE e.address.city = c.city
Generates the following SQL:
SELECT t1.EMP_ID, t3.CITY FROM CMP3_ADDRESS t4, (SELECT DISTINCT t0.CITY
FROM CMP3_ADDRESS t0) t3, CMP3_SALARY t2, CMP3_EMPLOYEE t1 WHERE
(((t4.CITY = t3.CITY) AND (t2.EMP_ID = t1.EMP_ID)) AND (t4.ADDRESS_ID =
t1.ADDR_ID))
To generates the same SQL without JPQL:
ExpressionBuilder mainBuilder = new ExpressionBuilder(Employee.class);
mainQuery = new ReportQuery(Employee.class, mainBuilder);
mainQuery.addAttribute("id");
ExpressionBuilder subBuilder = new ExpressionBuilder(Address.class);
subQuery = new ReportQuery(Address.class, subBuilder);
subQuery.addAttribute("city");
subQuery.useDistinct();
Expression fromSubSelect =
mainBuilder.getAlias(mainBuilder.subQuery(subQuery));
mainQuery.addNonFetchJoin(fromSubSelect);
Expression fromAlias = fromSubSelect.get("city");
mainQuery.addAttribute("city", fromAlias);
mainQuery.setSelectionCriteria(mainBuilder.get("address").get("city").equal(fromAlias));
Query query = JpaHelper.createQuery(mainQuery, em);
On 8/20/2012 1:21 PM, vaidya nathan wrote:
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
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
