英文:
How to use Hibernate Check constraint in this case?
问题
我想要使用检查约束来验证学科中的学生人数是否超过了空缺名额。以下是相关实体的代码部分:
SubjectOffer
@Entity
@SequenceGenerator(name = "SUBJECT_OFFER_SEQ", sequenceName = "SUBJECT_OFFER_SEQ")
@Table(name = "SUBJECT_OFFER", uniqueConstraints = {
@UniqueConstraint(name = "UQ_SUBJECT_OFFER_COURSE_SUBJECT_SEMESTER_CLASS", columnNames = {"COURSE_ID", "SUBJECT_ID", "SEMESTER", "CLASS_NUMBER"})})
@Check(constraints = "COUNT(STUDENT_SUBJECT_ID) <= VACANCIES")
public class SubjectOffer {
@Id
@GeneratedValue(generator = "SUBJECT_OFFER_SEQ")
@Column(name = "SUBJECT_OFFER_ID", nullable = false)
private Long id;
@OneToMany(fetch = FetchType.LAZY, orphanRemoval = true, cascade = CascadeType.ALL)
@JoinColumn(name = "STUDENT_SUBJECT_ID")
private Set<StudentSubject> studentSubjects = new HashSet<>();
//other attributes
@Column(name = "VACANCIES", nullable = false)
private int vacancies;
}
StudentSubject
@Entity
@Table(name = "STUDENT_SUBJECT")
public class StudentSubject {
@EmbeddedId
private StudentSubjectId id = new StudentSubjectId();
@MapsId("studentId")
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "STUDENT_ID", nullable = false)
private Student student;
@MapsId("subjectOfferId")
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "SUBJECT_OFFER_ID", nullable = false)
private SubjectOffer subjectOffer;
@Column(name = "SEMESTER", nullable = false)
private int semester;
@Column(name = "GRADE")
private BigDecimal grade;
}
你还提到尝试在Set<StudentSubject>
的@JoinColumn
中使用列定义,但没有成功。
英文:
I want to use check constraint to verify if there are more students in the subject more than vacancies. These are the entities:
SubjectOffer
@Entity
@SequenceGenerator(name = "SUBJECT_OFFER_SEQ", sequenceName = "SUBJECT_OFFER_SEQ")
@Table(name = "SUBJECT_OFFER", uniqueConstraints = {
@UniqueConstraint(name = "UQ_SUBJECT_OFFER_COURSE_SUBJECT_SEMESTER_CLASS", columnNames = {"COURSE_ID", "SUBJECT_ID", "SEMESTER", "CLASS_NUMBER"})})
@Check(constraints = "COUNT(STUDENT_SUBJECT_ID) <= VACANCIES")
public class SubjectOffer {
@Id
@GeneratedValue(generator = "SUBJECT_OFFER_SEQ")
@Column(name = "SUBJECT_OFFER_ID", nullable = false)
private Long id;
@OneToMany(fetch = FetchType.LAZY, orphanRemoval = true, cascade = CascadeType.ALL)
@JoinColumn(name = "STUDENT_SUBJECT_ID")
private Set<StudentSubject> studentSubjects = new HashSet<>();
//other attributes
@Column(name = "VACANCIES", nullable = false)
private int vacancies;
}
StudentSubject
@Entity
@Table(name = "STUDENT_SUBJECT")
public class StudentSubject {
@EmbeddedId
private StudentSubjectId id = new StudentSubjectId();
@MapsId("studentId")
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "STUDENT_ID", nullable = false)
private Student student;
@MapsId("subjectOfferId")
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "SUBJECT_OFFER_ID", nullable = false)
private SubjectOffer subjectOffer;
@Column(name = "SEMESTER", nullable = false)
private int semester;
@Column(name = "GRADE")
private BigDecimal grade;
}
I also tried column definition in Set<StudentSubject> @JoinColumn but it didn't work
答案1
得分: 0
SQL检查约束只能用于单个表。你想要的是所谓的SQL断言约束,但没有数据库实现它。你能做的最好的方法是预先创建职位的行数,然后只是将学生分配到这些行,而不会再创建更多的行。这样,你可以确保只分配与职位数相同的学生,前提是在分配学生时使用了乐观/悲观锁定。
英文:
SQL check constraints only work on a single table. What you want is a so called SQL assertion constraint, but no database implements that. The best you can do is to pre-create rows for the amount of vacancies and just assign students to these rows without ever creating more rows. This way, you can make sure that you only assign as many students as there are vacancies, given that you use optimistic/pessimistic locking when assigning a student.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论