Mapping value in junction table to Entity

Question

I have a USER table and a COURSE table. A USER can have many COURSES, and a COURSE many USERS. The junction table contains a ROLE value that determines what role the user has in a COURSE (i.e. Instructor, Student, etc.). I need to some how associate this role with the COURSE for each USER.

If I put the role in the Course class, it can't work, since a Course has many users, and vice versa in the User class.

Here's what I have so far:

@Entity
@Table(name = "USERS")
public class User {

@Id
@Column(name = "PK1")
private Long id;

@Column(name = "USER_ID")
private String userId;

@ManyToMany
@JoinTable(name = "COURSE_USERS", 
    joinColumns = @JoinColumn(name = "USERS_PK1", referencedColumnName = "PK1"), 
    inverseJoinColumns = @JoinColumn(name = "CRSMAIN_PK1", referencedColumnName = "PK1"))
private Collection<Course> courses;

...

@Entity
@Table(name = "COURSE")
@SecondaryTable(name = "COURSE_USERS", 
pkJoinColumns = @PrimaryKeyJoinColumn(name = "CRSMAIN_PK1"))
public class Course {

@Id
@Column(name = "PK1")
private Long id;

    // THIS PROBABLY WON'T WORK //
@Column(name = "ROLE", table = "COURSE_USERS")
private Character role;

@Column(name = "AVAILABLE_IND")
private boolean available;

@Column(name = "COURSE_NAME")
private String name;

@Transient
private String url;

    ...

Note: I cannot change the database schema, so the junction table is nonnegotiable.

Answer 1

There is third entity in your case, and it wants to come out. You can call it CourseAssignment. CourseAssignment will contain role, and ManyToOne relationship to both User and Role. And additionally of course OneToMany relationship from Course to CourseAssignment and from User to CourseAssignment.

Something like this will work (I didn't tried it, so something can be missing, but you will get idea.

@Entity
@Table(name = "COURSE_USERS")
@IdClass(CourseAssignmentId.class)
public class CourseAssignment {
    @Id
    @ManyToOne
    @JoinColumn(name="USERS_PK1")
    private User user;
    @Id
    @ManyToOne
    @JoinColumn(name="CRSMAIN_PK1")
    private Course course;

    @Column(name = "ROLE")
    private Character role;
}
//and then of course IdClass, because combined key:
@Embeddable
public class CourseAssignmentId implements Serializable{
    @Column(name="USERS_PK1")
    private Long user;

    @Column(name="CRSMAIN_PK1")
    private Long course;
}


User { ..

    @OneToMany(mappedBy = "user")
    private Collection<CourseAssignment> courseAssignments;
...
}

Course {..
   @OneToMany(mappedBy = "course")
   private Collection<CourseAssignment> course;

..
}

And of course remove those existing relationship connected attributes.

Answer 2

As you say, adding the role to the user doesn't make sense since the user may have many courses, and adding the role to the course won't work because a course may have many users.

So you need another entity. Something like:

@Entity
@Table(name = "COURSE_USERS")
// @pkJoinColumns = probably some combination of user/course
public class CourseUser {

@Column(name = "ROLE")
private Character role;

@Column(name = "USERS_PK1")
private User user;

@Column(name = "CRSMAIN_PK1")
private Course course;
}

And each User would have many CourseUsers and each Course would have many CourseUsers.