0 Replies Latest reply on Apr 2, 2003 12:14 AM by Matt Geis

    how to load from a view but update/insert/delete on a table?

    Matt Geis Newbie

      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