Tobias, I too have been frustrated with the limitations of ejbql. I finally decided that the best solution is to create custom finders. They look exactly like the finders I wrote for my BMP beans, so I didn't have to learn anything new. If you haven't written any BMP beans, you can find plenty of examples in the JBoss or Sun docs.
I understand its not vendor independent, but if for some unforseen reason, I ever need to migrate an app to BEA or IBM, I can always convert the whole bean to BMP.
Anyone have a better idea?
Maybe this sounds too simplistic but why not run the query without the COUNT clause and then use java.util.Collection.size() to count the results. If you're concerned about performance then use a lazy-load optimization group for the query.
I am also interested in COUNT() but also SUM(). The system i develop logs calls made on a system. The system can deal with 10 calls per second so doing Collection.size() on thousands of records would be really faster if made by the db even with lazy load (you need to load them all ultimatly anyway). Same thing for SUM, we need to calculate total time for thousands of calls.
Currently it is even planned that it could be hundreds of thousand of calls.
What I seem to understand with your answer is that my only resort is to go BMP?? Or wait for 4.0 ??