How To use group functions in CMP with JBoss

[ysiraj]

I want to make up a query which should return max(columnName). Something like "Select MAX(id)". Is this possible in CMP? I'm using JBoss 2.4.4.


Thanks in advance

[dsundstrom]

You will have to code this by hand using a BMP style custom finder (see the free online manual).

[lepekhine]

Almost all group functions (exept may be min() and max() where database optimizer could use indexes) need full table scan. So you will not lost much performance if you find all beans by the findAll() method. With collection of beans found you can use Visitor pattern to calculate any group function. See J.Cooper "Design Patterns Java Companion" for an example of sum() function.
Another way is to use durty trick of substitution view for the CMP table. Write read-only CMP and replace it's database table with view such as:
drop table xxx;
create view xxx(...) as select min(...), max(...),...from yyy;
Theoretically it must work.
And one another way is using JDBC calls in appropriate SessionBean. You can use DAO pattern, wich facilitate switching databases.
And the last way is doing nothing and wait for the next version of EJB-QL :).