How to use EntityQuery and aggregating functions for lists

[keithbannister]

Hi,

I'm pretty new to EJB/Seam and things have been going great so far but I've run in to a snag.

I have 2 Entities, A RadioSource and a FieldSource. One RadioSource owns many FieldSources. FieldSources have an attribute called flux (a double).

I want to display a table of RadioSources - I want the following columns in the table:

- RadioSource ID and other stuff (I think I know how to do this)
- Maximum/Minimum/Average flux of all the FieldSources owned by this RadioSource. All separate columns with the nifty click and the list is re-ordered feature.
- The value of (Maximum - minimum)/Average flux as another column with the sorting feature
- A link to the FieldSource whose flux was the minimum/maximum

The only SEAM stuff I've managed to do so far is basic CRUD s and to create some plots with jfreechart.

I have some java classes which do the statistics as a @Transient class but I can't sort on them because the EJBQL doesn't know they exist (beacause they're @Transient).

Any ideas?

[sander]

If they are transient than this means they are not in the database and therefor cannot be ordered by the database, and thus via ejbql.

One option is to get the whole list and sort it on the server.
Downside is that you'd need to recalculate everything when sorting it, so you'd might run into performance prob's for large sets.

Other option is to pre-calculate the stats and include them in the database just for performance reasons

[sander]

hmmz just read it again and your only using max/min/avg.

Since these are also just sql functions why not use these in the query.

I don't know exactly the ejbql notation but it could do.

You can also set them in the object via ejbql when using:
although i don't know if this is hibernate only function or just normal ejbql

Select new Object(field1, field2, max(radio.source.field), min(..), etc) FROM ..

just make sure there is a correct constructor for it.

furthermore read some doc's about ejbql / hql first as this might not be syntax correct

[endyamon]

take a look here...

http://docs.solarmetric.com/full/html/ejb3overviewquery.html

[svadu]

I've had a similar problem but with much more complex business rules and somewhat big database (few mill records) so the performance impact would be severe.

I've decided to define pre-calculated values at RadioSource (in your case) and used an EntityListener which listens for PrePersist and PreUpdate where I calculate and set the values and this did the job for me.