Mapping Single Table to Embeddable Collection in JPA

Question

I have a fairly unique situation trying to map a single table to multiple entities in JPA. I have read about @Embeddable and @ElementCollection, but I'm not sure how to use them in my situation (or if I can). The one database table holds Course information. There can be rows in the table where everything in the Course is the same except for a few values, such as the room number and day. For example:

TERM_CODE   SUBJECT_CODE    ROOM    DAY    INSTRUCTOR_ID
201220      EGRE            0101    TR     123
201220      EGRE            0103    W      124

Is there a way that I can pull data from two rows as above, and put the common data in one Object and the different values in a Collection of separate Objects? Here's an example of how I'd like to have the classes defined:

@Entity
public class Course implements Serializable {

    @Id
    @Column(name = "TERM_CODE")
    private Long termCode;

    @Column(name = "SUBJECT_CODE")
    private String subjectCode;

    @Embedded
    Collection<CourseSchedule> schedule;

    public Long getTermCode() {
     return termCode;
    }

    public void setTermCode(Long termCode) {
     this.termCode = termCode;
    }

    public String getSubjectCode() {
     return subjectCode;
    }

    public void setSubjectCode(String subjectCode) {
     this.subjectCode = subjectCode;
    }
}

CourseSchedule:

@Embeddable
public class CourseSchedule {
     private String room;
     private String day;

      public String getRoom() {
       return room;
      }

      public void setRoom(String room) {
       this.room = room;
      }

      public String getDay() {
       return day;
      }

      public void setDay(String day) {
       this.day = day;
      }

      public String getInstructorId() {
       return instructorId;
      }

      public void setInstructorId(String instructorId) {
       this.instructorId = instructorId;
      }
}

I also am confused as to what my JPQL would look like in this situation once I get them mapped.

EDIT:

If I add @Id to the TERM_CODE column, a Course object is returned without Hibernate errors, but the CourseSchedule Collection that is part of the Course is null.

EDIT 2:

I've tried to play around with treating Course and CourseSchedule as two separate tables (even though they're not), but I can't seem to get them joined using @OneToMany and @ManyToOne.

@Entity
@IdClass(CourseId.class)
@Table(name = "course_table")
public class Course implements Serializable {

    @OneToMany(mappedBy = "course")
    private Collection<CourseSchedule> schedule;

    @Id
    @Column(name = "TERM_CODE")
    private Long termCode;

    @Id
    @Column(name = "SUBJECT_CODE")
    private Long subjectCode;

    ...
}

@Entity
@IdClass(CourseScheduleId.class)
@Table(name = "course_table")
public class CourseSchedule implements Serializable {

    @ManyToOne
    @JoinColumns({
    @JoinColumn(name="TERM_CODE", referencedColumnName="TERM_CODE"),
    @JoinColumn(name = "SUBJECT_CODE", referencedColumnName="SUBJECT_CODE")
    })
    private Course course;

    @Column(name = "TERM_CODE")
    private Long termCode;

    @Column(name = "SUBJECT_CODE")
    private Long subjectCode;

    @Id
    private String room;

    @Id
    private String day;

    @Id
    @Column(name = "INSTRUCTOR_ID")
    private String instructorId;

    ...

}

(The CourseId and CourseScheduleId are simple classes used for the composite ID.) The above mapping return the following error:

org.hibernate.MappingException: Foreign key (FK82D03688F590EF27:course_table [TERM_CODE,SUBJECT_CODE])) must have same number of columns as the referenced primary key (course_table [ROOM,DAY,INSTRUCTOR_ID)

I don't need the CourseSchedule referring back to the Course if that helps make it easier.

Any ideas? My only other thought is to define them as completely separate entities (not joined), and then somehow map them together using JPQL.

Answer 1

I have been trying some things, and the closest I get to what you want is this(check the setter setCourse in the CourseSchedule class):

Course

@Embeddable
public class Course implements Serializable {

@Column(name = "TERM_CODE")
private Long termCode;

@Column(name = "SUBJECT_CODE")
private String subjectCode;

@Transient
Collection<CourseSchedule> schedule = new ArrayList<CourseSchedule>();

public void setSchedule(Collection<CourseSchedule> schedule) {
    this.schedule = schedule;
}
public Collection<CourseSchedule> getSchedule() {
    return schedule;
}

public Long getTermCode() {
 return termCode;
 }

 public void setTermCode(Long termCode) {
 this.termCode = termCode;
 }

 public String getSubjectCode() {
return subjectCode;
 }

 public void setSubjectCode(String subjectCode) {
 this.subjectCode = subjectCode;
 }
}

CourseSchedule

@Entity(name="Course")
public class CourseSchedule {
private String room;
private String day;

@Id
@GeneratedValue
private int id;

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

@Embedded
private Course course;

public Course getCourse() {
    return course;
}

public void setCourse(Course course) {
    course.schedule.add(this);
    this.course = course;
}

public String getRoom() {
    return room;
}

public void setRoom(String room) {
    this.room = room;
}

public String getDay() {
    return day;
}

public void setDay(String day) {
    this.day = day;
}
}

generated database table

id    subject_code    term_code    day    room
1      "EGRE"           201220     "TR"   "0101"
2      "EGRE"           201220     "W"    "0103"

Basically the other way around. It isn't completely what you expect, but it might inspire you for a better solution, please keep me updated if you have more ideas or found something interesting...