4 Replies Latest reply on Aug 1, 2006 10:08 PM by David Bernstein

    How to store information about many-to-many relationship its

    David Bernstein Newbie

      I have a situation where I have a many-to-many bidirectional relationship between two entity beans "User" and "Group", where users can belong to many groups and groups can have many users. I want to keep track of the status of the relationship itself and still take advantage of automatic table generation from source annotations in a database-independent fashion. For example, a user's status in a group could be "invited" or "member" independently for each group that the user is related to. The user is the owning side of the relation, although that doesn't seem relevant to me. It seems to me that what I want to do is have another column in the join table and I'm stumped about how to do in a clean way and was unable to find anything about this topic; I've read Bill Burke's O'Reilly EJB3 book and couldn't think of good search terms that would capture this issue. (I'm also using Seam, although I don't think that's germane here.) I imagine I could hack in another column to the join table with an ALTER TABLE, but that strikes me as a kluge and I don't see how I could easily deal with group member status using EJB3. Does anybody have a suggestion? Pointers on how to do this or to previous discussions of this topic gratefully accepted.

      current Group.java

      ...
      @Entity
      @Table(name="GROUPS")
      public class Group implements Serializable {
      
       private long id;
       private Set<User> users = new HashSet<User>();
      
       @Id
       @Column(name="GROUP_ID")
       @GeneratedValue
       public Long getId() {
       return id;
       }
       public void setId(Long id) {
       this.id = id;
       }
      
       @ManyToMany(cascade=CascadeType.PERSIST,mappedBy="groups")
       public Set<User> getUsers() {
       return users;
       }
       public void setUsers( Set<User> users ) {
       this.users = users;
       }
      
       ...
      }
      


      current User.java

      ...
      @Entity
      @Table(name="USERS")
      public class User implements Serializable {
      
       private long id;
       private Set<Group> groups = new HashSet<Group>();
      
       @Id
       @Column(name="USER_ID")
       @GeneratedValue
       public Long getId() {
       return id;
       }
       public void setId(Long id) {
       this.id = id;
       }
      
       @ManyToMany(cascade=CascadeType.PERSIST)
       @JoinTable(name="USER_GROUP",
       joinColumns={@JoinColumn(name="USER_ID")},
       inverseJoinColumns={@JoinColumn(name="GROUP_ID")})
       public Set<Group> getGroups() {
       return groups;
       }
       public void setGroups( Set<Group> groups ) {
       this.groups = groups;
       }
      
       ...
      }
      


      currently generated schema:


      2006-07-27 19:15:53,790 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] create table GROUPS (GROUP_ID bigint generated by default as identity (start with 1), ..., primary key (GROUP_ID))
      2006-07-27 19:15:53,790 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] create table USERS (USER_ID bigint generated by default as identity (start with 1), ..., primary key (USER_ID))
      2006-07-27 19:15:53,790 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] create table USER_GROUP (USER_ID bigint not null, GROUP_ID bigint not null, primary key (USER_ID, GROUP_ID))
      2006-07-27 19:15:53,790 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] alter table USER_GROUP add constraint FKC62E00EB21CA0D09 foreign key (USER_ID) references USERS
      2006-07-27 19:15:53,790 DEBUG [org.hibernate.tool.hbm2ddl.SchemaExport] alter table USER_GROUP add constraint FKC62E00EB44BDA00B foreign key (GROUP_ID) references GROUPS