EJB3/Hibernate: how to use sum/group by in a native query

[Gerd Michalke]

Hi

I have been googling for some days, but without result

I have one table whose fields are

DATE (DATE)
PLACE (INTEGER)
AMOUNT (INTEGER)

In plain sql, I need SELECT date,place,sum(amount) FROM table GROUP BY date,place ORDER BY place,date;

I tried to make a native query

List l = em.createNativeQuery(zeQuery).getResultList();

This makes a list of Object whose size is the number of records which would be have been returned by the query.

How can I use this resultset ?

I have been searching for days, but have found nothing interesting (apart from query examples, but the query is ok; what I can't get is how to use the result)

Thanks in advance

[Abiodun Adisa]

Originally posted by Gerd Michalke:
Hi

I have been googling for some days, but without result

I have one table whose fields are

DATE (DATE)
PLACE (INTEGER)
AMOUNT (INTEGER)

In plain sql, I need SELECT date,place,sum(amount) FROM table GROUP BY date,place ORDER BY place,date;

I tried to make a native query

List l = em.createNativeQuery(zeQuery).getResultList();

This makes a list of Object whose size is the number of records which would be have been returned by the query.

How can I use this resultset ?

I have been searching for days, but have found nothing interesting (apart from query examples, but the query is ok; what I can't get is how to use the result)

Thanks in advance

try this

List results = new ArrayList();
List l = em.createNativeQuery(zeQuery).getResultList();

for (Iterator it = l.iterator(); it.hasNext(); ) {
Object obj[] = (Object[]) it.next();
log.debug("obj: " + obj.getClass().getName() + ": " + obj); Collection row = new ArrayList();
for (int i = 0; i < obj.length; i++) {
log.debug("column " + i + " is: " + obj[i].getClass().getName));
row.add(obj[i]);
}
results.add(row);
} // for each


The List result contains the values you need
[ September 14, 2006: Message edited by: Abiodun Oyesiji ]