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();
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.jav
a:48)
at java.lang.Integer.parseInt(Integer.java:447)
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
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