Hi Tim,
sorry for the late answer. Please find my comments inline ...
>
> I have a table in which I want to get some very simple aggregated info
> to display on a webpage. The query looks like this:
>
> EntityManager em = getEntityManager();
>
> String param =
> FacesContext.getCurrentInstance().getExternalContext().getRemoteUser();
>
> Query q = em.createQuery("SELECT a.status, COUNT(a) FROM Action a
> WHERE a.assigneduser.username=:param GROUP BY a.status");
>
> q.setParameter("param", param);
>
> List l = q.getResultList();
>
I guess status is an enum type field, correct?
>
> Two questions:
>
> 1) Unless I add “HAVING COUNT(a.status) > 0” to the query string, why
> do I get this:
>
> |java.lang.NumberFormatException: For input string: "status"|
> | at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)|
> | at java.lang.Integer.parseInt(Integer.java:447)|
I tried to reproduce the NumberFormatException, but I could not. Could
you please provide more lines from the stacktrace to see which part of
the code is trying to convert the text status into a number.
It is really strange that adding the HAVING clause makes any difference.
Is it possible to provide a test case that allows me to reproduce. Thanks!
>
> 2) How do I return a ResultList of scalars to List l? In other words,
> the returned values should be something like:
>
> Unassigned 5
>
> Working 7
>
> Sleeping 4
>
The above query returns a list of type List<Object[]>. Each element in
the result list is an Object[] with two elements: the status field at
index 0 and a Long representing the COUNT at index 1.
List<Object[]> resultList = q.getResultList();
for (Object element : resultList) {
Status status = (Status)element[0];
Long count = (Long)element[1];
...
}
Each line of of your sample output from above is represented by an Object[].
Another alternative is wrapping the query result elements into your own
class instead of the Object[]. This is done using a constructor
expression in the SELECT clause:
SELECT NEW com.xyz.MyResultElement(a.status, COUNT(a)) FROM Action a
WHERE a.assigneduser.username=:param GROUP BY a.status
The above query returns List<MyResultElement> and creates a
MyResultElement instance per query result element.
Hope this helps.
Regards Michael
>
> I understand that this is not really doing ORM, but I’m wondering if
> there’s a way to accomplish this (it seems that having the db do this
> query for large datasets will be more efficient than sorting a
> collection and finding a count that way), or if there’s a better way
> to achieve what I am trying to get. If I perform the query (with
> “HAVING count(a.status) > 0” appended to the query string), a call to
> l.toString() returns two objects. I’d like to be able to access the
> String and Number for each row via the list.
>
> I see tons of examples on the web that look like [1], but nobody ever
> shows code where they assign these values to anything! An example at
> [2] looks close, but they only get a SingleResult (i.e., one row). How
> can I maintain the OR paradigm of EJB 3.0 without creating a separate
> container entity just for this query?
>
> [1] http://forums.java.net/jive/thread.jspa?threadID=19637&tstart=90
>
> [2] http://technology.amis.nl/blog/?p=1065
>
> Any tips or suggestions would be greatly appreciated!
>
> TR
>