I don't know if this is the best way to do it but I figured it's less expensive to have a select method in the home interface of a cmp that counts the amount of rows for a specific condition than to find them all and count the collection.

The problem is though, according to Denniger & Peter's EJB 2.1 select methods can only return type of remote or local interface or of a persistent attribute. So I come up with this query:

Is COUNT() not supported yet or am I not using it correctly? Btw, what does one do if the primary key is not numeric, the return type would not match the persistent attribute type? Is there a better way of achieving this?

make sure you implement this method as an select method and not as a finder method

assuming you're using xdoclet you will need to add a class-level tag @ejb.select and specify the query there, you will then also need to write a method for this query

there are many examples to be found on the internet that explain how to achieve this, but let me point you to a working example of mine, it is a bean generated by AndroMDA using a custom template, it automatically generates some default finders and selects