0 Replies Latest reply on Mar 7, 2008 3:17 AM by Ken Clark

    Advice Needed for Complex JPA Mapping

    Ken Clark Newbie

      I tried this post on both Nabble and Sun forums, but having received no response I thought I would try here and see if I get better luck.


      I have a table that looks like:


      key_with_rev_id INTEGER (sequence based, unique pk) 
      key_id INTEGER (sequence based) 
      rev_id INTEGER (sequence based, with key_id forms unique index) 
      del_flag VC(1) 
      parent_key_id (ref to the key_id column in this table)
      + various data fields 
      



      The object would contain these attributes (preferably):


      keyId (as the "unique" id) 
      maxRevId (transient, defaults to null) 
      a list of children objects (same class)
      + various data attributes 
      



      When I load it -- when maxRevId is null, it should basically do this:


      select <data fields> from revved_table 
      where key_id = <object.key_id> 
      and rev_id = (select max(rev_id) from revved_table where key_id = <object.key_id>) 
      and del_flag is null or del_flag = 'N' 
      



      The query is a little more complex when maxRevId is not null, but same idea.


      When I am searching for children, I use a query just like the above, only this time with parentKeyId equal to a specified value.


      Now then -- when I store the object, whether it is to insert or update, I want it to always insert rows, with the
      keyWithRevId and revId values from the corresponding sequences. (On insert, it will also use another seq to populate keyId).  When I delete, I also want to insert a new row, but set delFlag equal to Y.


      Is this too complex for JPA such that it would be a huge hassle, or is there an elegant way to set this up?  Does Seam provide any additional capabilities to simplify this?


      Thanks in advance for any assistance.


      ken clark