有条件的多对一关系

Question

我有两张桌子，学生和老师，关系是ManyToOne。表格结构如下

student(
id long,
student_id string,
....
teacher_id string,
active boolean
)
teacher(
id long,
teacher_id string,
....
active boolean
)

我使用的是Spring引导和Hibernate。在这里，当更新实体时，表中现有行的活动列将设置为false，并且将添加一个具有新id(long(的新行，活动列为true。这就是为什么每个表中都有两个id值。这里的问题是，我已经在我的实体中用外键teacher_id指定了多对一的师生关系。

@Entity
@Table(name = "student")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "student_id")
private String studentId;
@ManyToOne
@JoinColumn(name = "teacher_id", referencedColumnName = "teacher_id")
private Teacher teacher;
@Column(name = "active")
@JsonIgnore
private Boolean active = true;
}
@Entity
@Table(name = "teacher")
public class Teacher {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "teacher_id")
private String teacherId;
@OneToMany(mappedBy = "teacher")
private Set<Student> students = new HashSet<>();
@Column(name = "active")
@JsonIgnore
private Boolean active = true;
}

但是，由于同一个teacher_id可能会出现多个教师，因此这是失败的。有没有办法给这种关系一个条件，让老师以主动为真？在表中，只有一个教师具有给定的id，并且活动为true。

Answer 1

我刚刚看到你的帖子。几个月前我也有同样的要求，我就是这么做的。。

public class Student {
@Column(name = "teacher_id ")
private String teacherId;
@ManyToOne
@JoinFormula(value = "(Select t.id from teacher t where t.teacher_id= teacher_id and t.active=1)"
)
private Teacher teacher;
}

@Entity
@Table(name = "teacher")
public class Teacher {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "active")
@JsonIgnore
private Boolean active = true;
}

这种方法适用于我的情况。如果有更好的，你可以分享。

感谢

Answer 2

Teacher实体中不应有teacher_id外键字段。相反，只需使用主键id列即可。考虑一下这个版本的实体：

@Entity
@Table(name = "student")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "student_id")
private String studentId;
@ManyToOne
@JoinColumn(name = "teacher_id", referencedColumnName = "id")
private Teacher teacher;
@Column(name = "active")
@JsonIgnore
private Boolean active = true;
}
@Entity
@Table(name = "teacher")
public class Teacher {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@OneToMany(mappedBy = "teacher")
private Set<Student> students = new HashSet<>();
@Column(name = "active")
@JsonIgnore
private Boolean active = true;
}

这种设计应该强制一个给定的学生只能与一个老师关联(尽管给定的老师可以有多个学生(。