Hi,
I have a problem I'm trying to solve, and I'm not arriving at an efficient solution. I'm sure I'm just missing something in the way of a good cmp approach. I know how to do this with BMP, but want to move to CMP.

I have an object (Notification) which in real-world usage notifies a user about the publication of another type of object (Report). In the db schema, Notification simply has userID and reportID.

When I do a finder method (findByUserID), I would also like it to return a couple fields from each report (report name, publication date....), for an informative listing to present to the user. However, when I create or delete a notification, all I want to do is delete from or add to the NOTIFICATION table.

I could do a finder method on the notification table, then for every single record found, get a reference to a Report EJB, load that, and present the data for that bean. However, if a query returns 100 notifications, that's one query for the collection, then 100 queries just to get name and publication date for each of the reports. There must be a more efficient way to deal with this. In a JDBC-only world, I would just run a query against a view which joins the REPORT and NOTIFICATION tables, and get all the results, in whatever order I specify.

Another idea would be to use some sort of approach where one table (or in this case a view) is used to find and load beans, but another is used for actual updates, with read-only fields being left out of the persistence statements. However, to my knowledge there is no such concept of a read table and a write table.

Can anyone offer a suggestion as to how to accomplish this?

Thanks in advance,
Matt Geis
mgeis@yahoo.com