CMP query with COUNT()

[tobias]

Hi!
I'd like execute a query like this on the database behind my CMP entities:

SELECT COUNT(technologyid) FROM techlink WHERE componentid IN ('22', '27', '29') AND technologyid = '15'

But I run into bumps regardless in which direction I turn. COUNT() is not supported by EJB/CMP2.0, it will be support by CMP2.1 which is not yet final.
JBossQL and DynamicQL do not seem to help here.
(see http://sf.net/tracker/?group_id=22866&atid=376688&func=detail&aid=588646 as well)

Even DeclaredSQL seems not to allow using COUNT() as a result to SELECT. Am I am mistaken at this point ? If yes, how to do it ?

Is the only alternative I have to use JDBC directly on the database ?

TIA,
Tobias

[larry054]

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?

[kv_moj]

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.

kv.

[nicfournier]

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 ??

Thanks!!