JPA Many-to-Many Unidirectional Ordered List

Major.kt

/*
 * See LICENSE for licensing and NOTICE for copyright.
 */
package edu.vt.middleware.ed.model.db

import javax.persistence.*

/**
 * Models a Virginia Tech academic major from Banner.
 *
 * @author Marvin S. Addison
 */
@Entity
@Table(name = "major")
class Major(
  /** The major code uniquely identifies a major. */
  @Id
  @Column(name = "code", length = 4, nullable = false)
  override val id: String = ""
) : Persistent<String> {

  /** Major description. */
  @Column(name = "description", length = 100, nullable = false)
  var description: String = ""
}

schema.sql

create table student_record (
  seqno bigint not null, academic_level integer, campus varchar(30) not null,
  college_code varchar(2), last_enrolled_term_code varchar(6),
  next_enrolled_term_code varchar(6), primary key (seqno));

create table major (
  code varchar(4) not null, description varchar(100) not null,
  primary key (code));
                         
create table student_to_major (student_seqno bigint not null, major_code varchar(4) not null, 
sequence integer not null, primary key (student_seqno, sequence));

alter table student_to_major add constraint FKmmr0316qgojnw30q5gv4ho71q
  foreign key (major_code) references major;

alter table student_to_major add constraint FKami5lrl1rllkq6t50y78u5vpu
  foreign key (student_seqno) references student_record;

StudentRecord.kt

/*
 * See LICENSE for licensing and NOTICE for copyright.
 */
package edu.vt.middleware.ed.model.db

import javax.persistence.*

/**
 * Models a student record summary data from Banner.
 *
 * @author Marvin S. Addison
 */
@Entity
@Table(name = "student_record")
@SequenceGenerator(name = "student_record_seq", allocationSize = 1)
class StudentRecord(
  /** Student record unique identifier. */
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "student_record_seq")
  @Column(name = "seqno", nullable = false)
  override val id: Long = 0
) : Persistent<Long> {

  /** Student academic level. */
  @Column(name = "academic_level")
  var academicLevel: AcademicLevel? = null

  /** Campus location where student was last enrolled. */
  @Column(name = "campus", nullable = false, length = 30)
  @Enumerated(EnumType.STRING)
  var campus: Campus = Campus.BLACKSBURG

  /** College of the student's declared major. */
  @OneToOne
  @JoinColumn(name = "college_code")
  var college: College? = null

  /** Previous term where student was last enrolled. */
  @OneToOne
  @JoinColumn(name="last_enrolled_term_code")
  var lastEnrolledTerm: Term? = null

  /** Future term where student will be enrolled next. */
  @OneToOne
  @JoinColumn(name="next_enrolled_term_code")
  var nextEnrolledTerm: Term? = null

  /** Student's declared academic majors in order of descending precedence. */
  @ManyToMany
  @JoinTable(
    name = "student_to_major",
    joinColumns = [JoinColumn(name = "student_seqno")],
    inverseJoinColumns = [JoinColumn(name = "major_code")])
  @OrderColumn(name = "sequence")
  var majors: MutableList<Major> = mutableListOf()
}


/** Academic levels derived from Banner academic levels. */
enum class AcademicLevel(val code: String, val description: String, val generalLevel: String?) {
  ASSOCIATE_1("05", "Associate I", "Associate"),
  ASSOCIATE_2("06", "Associate II", "Associate"),
  FRESHMAN("10", "Freshman", "Freshman"),
  SOPHOMORE("20", "Sophomore", "Sophomore"),
  JUNIOR("30", "Junior", "Junior"),
  SENIOR("40", "Senior", "Senior"),
  MASTERS("70", "Regular Masters", "Masters"),
  DOCTORAL("90", "Doctoral", "Doctorate"),
}


/** Virginia Tech campus locations. */
enum class Campus(val code: String, val description: String) {
  BLACKSBURG("0", "Blacksburg"),
  NCR("4", "National Captiol Region"),
  HAMPTON_RHODES("7", "Hampton Rhodes Center"),
}

In this data model, students can have multiple majors ordered in terms of descending precedence. The student_to_major table models this many-to-many relationship where the sequence column provides for the correct ordering in the StudentRecord#majors property.