Many to one relationship with a condition

user_7805325 0
Viewed 339

I have two tables, student and teacher, with a relationship ManyToOne. The table structure is as follows

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

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

I'm using Spring boot and Hibernate. Here when updating an entity, the active column of the existing row in the table will be set to false and a new row will be added with a new id(long) and active as true. That is why there are two id values in each table. The problem here is I have specified the student-teacher relation as many to one in my entity with the foreign key as 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;
}

But since multiple teachers can occur with the same teacher_id, this fails. Is there any way to give a condition to the relationship to fetch the teacher with active as true? In table, there will be only one teacher with the given id and active as true.

many-to-one spring-boot hibernate java
2 Answers

I just came across your post. I had the same requirement few months ago and this is what i did..

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;
}

This approach works for my case. If there is a better one you can share it.

Thanks

user_22005510

There should be no teacher_id foreign key field in the Teacher entity. Rather, just use the primary key id column instead. Consider this version of your entities:

@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;
}

This design should enforce that a given student can be associated with only one teacher (though a given teacher can have multiple students).

user_18632290
Related