4 Replies Latest reply on Oct 8, 2010 1:50 AM by Nuwan Gunarathna

    Many-to-Many mapping table

    Rick Kilcoyne Newbie

      Hi all,


      This question might be more of a jpa question, but here goes...


      I have two entities that are mapped using @ManyToMany. Works great. However, I'm not sure what to do if I want to add an additional property (column) to the mapping table created by my jpa provider to represent the many-to-many relationship.


      Any help is much appreciated.


      Rick

        • 1. Re: Many-to-Many mapping table
          Matt Davis Newbie

          Well, I know how to do this using Hibernate. I'm not sure where JPA and Hibernate annotations overlap exactly, but...


          Also, I'm kinda new, but your question is one of things that I've successfully transitioned from the ConceptState.DONOTUNDERSTANDTHAT state to ConceptState.CANMAKETHATWORK state in my head. :P


          So, you can either make an intermediate entity class for the join table and map it to your 2 entities with onetomany associations, or you can make a collection of components with with a value-type class for the join table. (There may be other ways)


          I did it the 2nd way. It involved making an Embeddable entity for my join table.  One of the related tables was the @Parent, because I would mostly be using unidirectional navigation...


          So I had something like this for my link table:


          @Embeddable
          public class Question_Guy{
          
           @Parent // hibernate annotation (optional), gives a backpointer to the link object
           private Guy guy;
          
           @ManyToOne
           @JoinColumn(name="question_id", nullable=false, updatable=false)
           private Question question;
          
           @Temporal(TemporalType.TIMESTAMP)
           @Column(name="dateadded", nullable=false)
           private Date dateAdded;
          
          public Question_Guy{Question questionIn, Guy guyIn){
           this.question = questionIn;
           this.guy = guyIn;
           this.dateAdded = //get current date 
          }
          



          And then the Guy class...


           ...
          
           @CollectionOfElements  //hibernate annotation
           @JoinTable(name="question_guy", joinColumns = @JoinColumn(name="guy_id"))
           private Set<Question_Guy> question_guys = new HashSet<Question_Guy>();
          
           ...
          
            public void addQuestion(Question questionIn){
            Question_Guy link = new Question_Guy(questionIn, this);
            question_guys.add(link);
          




          So when I call addQuestion(Question), some magical process puts the question into the database with the appropriate date in the extra column.


          But there are other solutions, and I won't be able to explain the pros and cons very well. I just wanted to show off that I know something, mostly :P


          And sorry that it's using Hibernate, I learned all this from the Hibernate docs.

          • 2. Re: Many-to-Many mapping table
            Rick Kilcoyne Newbie

            Thanks for the fast response and great tip Matt! Keep on learning! I'm having more fun with Seam than I've had in a long time in developing Web apps.


            Rick

            • 3. Re: Many-to-Many mapping table
              Val Sw Expert
              More of JPA, but found this link. Need suggestion to understand if approach is correct.

              I tried following the suggested approach.

              We need to associate User entity with Experiment entity.

              Requirement : Add a dateAdded column to the USERS_EXPERIMENT_J join table to identify who was the last person to modify an experiment.
              If userA creates an experiment a record should be created in the Join table.
              If userB go in and modify the experiment a new record should be added to the join table.

              @Entity(name = "Experiment")
              public class Experiment implements Equals, HashCode, ToString {
                   protected Long hjid;
                   @Id @GeneratedValue(strategy = GenerationType.SEQUENCE,  
                       generator="EXPERIMENT_STORE")
                       @Column(name = "HJID")
                       public Long getHjid() {
                        return hjid;
                       }
                   ....
              }
              -------------------------
              @Entity(name = "Users")
              public class Users implements Equals, HashCode, ToString{
                   protected List<Experiment> quantExperiment;
                   protected Long hjid;

                   @OneToMany(cascade = {CascadeType.ALL})
                   @JoinTable(name = "USERS_EXPERIMENT_J", joinColumns = {
                       @JoinColumn(name = "PARENT_USERS_ID")
                           }, inverseJoinColumns = {
                       @JoinColumn(name = "CHILD_EXPERIMENT_ID")
                       })
                       @OrderBy     
                   public List<Experiment> getExperiment() {
                        return quantExperiment;
                   }
                   public void setExperiment(List<Experiment> quantExperiment) {
                        this.quantExperiment = quantExperiment;
                   }

                   /////////////////
                   @CollectionOfElements
                   @JoinTable(name="USERS_EXPERIMENT_J", joinColumns = @JoinColumn(name="hjid"))
                   private Set<ExperimentUsers> users_quantexperiment_j =
                                      new  HashSet<ExperimentUsers>();
                   
                   public void addExperiment(Experiment quantExperimentIn){
                        ExperimentUsers link = new ExperimentUsers(quantExperimentIn, this);
                        users_quantexperiment_j.add(link);
                   }
                   /////////////////
              -------------------------
              @Embeddable
              public class ExperimentUsers {
                   
                   @Parent
                   private Users users;
                   
                   @ManyToOne
                  @JoinColumn(name="hjid", nullable=false, updatable=false)
                   private Experiment quantExperiment;
                   
                   @Temporal(TemporalType.TIMESTAMP)
                   @Column(name="dateadded", nullable=false)
                   private Date dateAdded;
                   
                   public ExperimentUsers(Experiment quantExperimentIn, Users usersIn){
                         this.quantExperiment = quantExperimentIn;
                         this.users = usersIn;
                         this.dateAdded = new java.util.Date();
                   }
              }
              • 4. Re: Many-to-Many mapping table
                Nuwan Gunarathna Newbie

                Hi,
                I also have faced with a same scenario that I need a mapping table that contains columns for foreign keys and some additional columns.


                Rick Kilcoyne... If you fond a complete solution to your problem can you please past the relevant Code here.



                Thank You.