What's the best (most efficient and portable) way to get "SELECT COUNT(*) FROM TABLE" functionality using CMP & EJB-QL 2.0 without JDBC?
I have this EJB system, using a session facade to front local entity beans, and it will be serving clients from a different web module in the application. One of the primary functions of the web UI is to view tabular data.
I want to paginate this data to maximize speed and efficiency. The client will specify how many rows-per-web-page it wants to display, along with the page-number to be viewed and possibly an order-by parameter. The job of the session facade is to use this client input to determine exactly which rows to return.
The server must take the rows-per-web-page variable passed by the client and then query the database to determine how many total records are available. The session EJB can then calculate the total number of pages to track pagination state. For example, if there are 100 total rows, one client many want to view 10 records per page whereas another client many want to view 20. The first client would have a 10-page chain, whereas the second client's pagination chain would only consist of 5 pages.
It's a simple system, and I've implemented it on other, non-EJB applications. The main problem I'm encountering has to do with querying the persistent store (db) to determine how many total records the UI will be selecting from. Right now I've implemented an EJBLocalHome findAll() method, which returns a Collection of EJBLocalObject stubs. By running the size() method on the Collection, I can determine the total number of records.
I don't like this solution because, as a table grows, the findAll() method will hog more and more resources. I don't want to make the server collect hundreds or thousands of entities when all I want is a single number. I've read about a design pattern (JDBC For Reading) that recommends embedding tabular read logic directly into the session facade, but I'm using CMP and want to declople the EJB layer from the persistence layer & SQL as much as possible. I heard that EJB 2.1 adds the COUNT() aggregate function to EJB-QL, but unfortunately my project is tied to EJB 2.0.
The only other thing I can think of would be to keep track of the number of rows in a table (entities in a home) myself, using another persistent EJB. That way, every time a row is added or deleted, the row-count would be incremented or decremented accordingly.
Has anyone else found a better solution to this problem? What's the best (most efficient and portable) way to get COUNT() functionality using EJB-QL 2.0 without JDBC?