如何使用Hibernate Check约束验证选课学生数不超过课程空位？

首先，你当前的实现存在两个核心问题：

1. 关联配置错误：SubjectOffer中@OneToMany的@JoinColumn配置指向了STUDENT_SUBJECT_ID，这是错误的——关联外键实际在StudentSubject表中是SUBJECT_OFFER_ID，且双向关联应使用mappedBy来指定关联的另一方。
2. 跨表CHECK约束无效：数据库原生CHECK约束只能校验当前表的字段，无法直接引用关联表的统计值（比如COUNT(STUDENT_SUBJECT_ID)），所以你添加的@Check约束根本不会生效。

步骤1：修正关联配置 #

将SubjectOffer中的@OneToMany配置改为正确的双向关联形式：

@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"})})
public class SubjectOffer {

    // ... 其他字段不变

    // 修正后的OneToMany关联
    @OneToMany(fetch = FetchType.LAZY, orphanRemoval = true, cascade = CascadeType.ALL, mappedBy = "subjectOffer")
    private Set<StudentSubject> studentSubjects = new HashSet<>();

    @Column(name = "VACANCIES", nullable = false)
    private int vacancies;
}

步骤2：实现选课人数限制验证 #

方案一：业务层验证（简单通用，兼容所有数据库） #

在添加选课前，直接校验当前选课人数是否已达上限，并发场景需加锁防止超员：

// 选课业务逻辑示例
public void enrollStudent(SubjectOffer subjectOffer, Student student) {
    // 悲观锁防止并发超员
    subjectOffer = entityManager.find(SubjectOffer.class, subjectOffer.getId(), LockModeType.PESSIMISTIC_WRITE);
    
    if (subjectOffer.getStudentSubjects().size() >= subjectOffer.getVacancies()) {
        throw new IllegalStateException("该课程已满员，无法选课");
    }

    StudentSubject studentSubject = new StudentSubject();
    studentSubject.setSubjectOffer(subjectOffer);
    studentSubject.setStudent(student);
    studentSubject.setSemester(subjectOffer.getSemester()); // 假设SubjectOffer有semester字段
    
    subjectOffer.getStudentSubjects().add(studentSubject);
    entityManager.persist(studentSubject);
}

方案二：数据库触发器+冗余字段（强约束，保证数据一致性） #

如果需要数据库层面强制约束，可以通过冗余字段+触发器实现：

1. 在SubjectOffer中添加冗余字段记录当前选课人数：

@Column(name = "ENROLLED_COUNT", nullable = false)
private int enrolledCount = 0;

2. 给SubjectOffer添加CHECK约束：

@Table(name = "SUBJECT_OFFER", uniqueConstraints = {
        @UniqueConstraint(name = "UQ_SUBJECT_OFFER_COURSE_SUBJECT_SEMESTER_CLASS", columnNames = {"COURSE_ID", "SUBJECT_ID", "SEMESTER", "CLASS_NUMBER"})},
        constraints = @Check(constraints = "ENROLLED_COUNT <= VACANCIES"))

3. 创建数据库触发器（以PostgreSQL为例），自动更新选课人数：

-- 插入选课记录时更新人数
CREATE OR REPLACE FUNCTION update_enrolled_count_insert()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE SUBJECT_OFFER
    SET ENROLLED_COUNT = ENROLLED_COUNT + 1
    WHERE SUBJECT_OFFER_ID = NEW.SUBJECT_OFFER_ID;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_student_subject_insert
AFTER INSERT ON STUDENT_SUBJECT
FOR EACH ROW
EXECUTE FUNCTION update_enrolled_count_insert();

-- 删除选课记录时更新人数
CREATE OR REPLACE FUNCTION update_enrolled_count_delete()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE SUBJECT_OFFER
    SET ENROLLED_COUNT = ENROLLED_COUNT - 1
    WHERE SUBJECT_OFFER_ID = OLD.SUBJECT_OFFER_ID;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_student_subject_delete
AFTER DELETE ON STUDENT_SUBJECT
FOR EACH ROW
EXECUTE FUNCTION update_enrolled_count_delete();

内容的提问来源于stack exchange，提问作者jpcairesf