如何在这种情况下使用Hibernate检查约束?

huangapple go评论64阅读模式
英文:

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 = &quot;SUBJECT_OFFER_SEQ&quot;, sequenceName = &quot;SUBJECT_OFFER_SEQ&quot;)
@Table(name = &quot;SUBJECT_OFFER&quot;, uniqueConstraints = {
        @UniqueConstraint(name = &quot;UQ_SUBJECT_OFFER_COURSE_SUBJECT_SEMESTER_CLASS&quot;, columnNames = {&quot;COURSE_ID&quot;, &quot;SUBJECT_ID&quot;, &quot;SEMESTER&quot;, &quot;CLASS_NUMBER&quot;})})
@Check(constraints = &quot;COUNT(STUDENT_SUBJECT_ID) &lt;= VACANCIES&quot;)
public class SubjectOffer {

    @Id
    @GeneratedValue(generator = &quot;SUBJECT_OFFER_SEQ&quot;)
    @Column(name = &quot;SUBJECT_OFFER_ID&quot;, nullable = false)
    private Long id;

    @OneToMany(fetch = FetchType.LAZY, orphanRemoval = true, cascade = CascadeType.ALL)
    @JoinColumn(name = &quot;STUDENT_SUBJECT_ID&quot;)
    private Set&lt;StudentSubject&gt; studentSubjects = new HashSet&lt;&gt;();

    //other attributes

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

StudentSubject

@Entity
@Table(name = &quot;STUDENT_SUBJECT&quot;)
public class StudentSubject {

    @EmbeddedId
    private StudentSubjectId id = new StudentSubjectId();

    @MapsId(&quot;studentId&quot;)
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = &quot;STUDENT_ID&quot;, nullable = false)
    private Student student;

    @MapsId(&quot;subjectOfferId&quot;)
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = &quot;SUBJECT_OFFER_ID&quot;, nullable = false)
    private SubjectOffer subjectOffer;

    @Column(name = &quot;SEMESTER&quot;, nullable = false)
    private int semester;

    @Column(name = &quot;GRADE&quot;)
    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.

huangapple
  • 本文由 发表于 2023年2月14日 00:11:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/75438438.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定