mysql - Spring MVC 无法删除父行？

Question

我开始学习spring mvc。我在做一些练习时遇到了逻辑错误。在我的示例中，我在 spring mvc 中有 2 个实体类。在那里我的代码 school_ıd 是主键。当我尝试从学校列表中删除学校时，出现如下错误

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails

实体学校:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="school_id")
private int schoolId;


@Column(name="school_name")
private String schoolName;


public int getSchoolId() {
    return schoolId;
}


public void setSchoolId(int schoolId) {
    this.schoolId = schoolId;
}


public String getSchoolName() {
    return schoolName;
}


public void setSchoolName(String schoolName) {
    this.schoolName = schoolName;
}       

实体学生

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id")
private int id;


@Column(name="first_name")
private String firstName;

@Column(name="last_name")
private String lastName;


@Column(name="email")
private String email;

@OneToOne
@JoinColumn(name="school_id")
private School school;



public School getSchool() {
    return school;
}

public void setSchool(School school) {
    this.school = school;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getLastName() {
    return lastName;
}

public void setLastName(String lastName) {
    this.lastName = lastName;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

学校 Controller 删除方法:

  @GetMapping("/deleteSchool")
  public String deleteSchool(@RequestParam("schoolID") int 
  theSchoolId) {    
    schoolService.deleteSchool(theSchoolId);
    return "redirect:/school/list";
   }

删除方法SchoolDAOImpl:

@Override
    public void deleteSchool(int theSchoolId) {
        Session currentSession=sessionFactory.getCurrentSession();
        Query theQuery=currentSession.createQuery("delete from School where id=:schoolID");
        theQuery.setParameter("schoolID", theSchoolId);
        theQuery.executeUpdate();

    }

实际上我现在的问题是我尝试删除学校，该学校至少有 1 名学生，因为我无法删除学校。为此，我首先需要删除本例中的 child ，在删除家长(学校)后， child 是学生。但我认为我的场景不适用于此。请帮帮我，我该怎么办？

Answer 1

您可以先更新学生:

@Override
public void deleteSchool(int theSchoolId) {
    Session currentSession=sessionFactory.getCurrentSession();

    Query updateStudentQuery=currentSession.createQuery("Update Student s SET s.school = null WHERE s.school.schoolId = :schoolId");
    updateStudentQuery.setParameter("schoolID", theSchoolId);
    updateStudentQuery.executeUpdate();

    Query theQuery=currentSession.createQuery("delete from School where id=:schoolID");
    theQuery.setParameter("schoolID", theSchoolId);
    theQuery.executeUpdate();

}

或使用 Studdent 的 DAO 进行更新，然后执行学校的删除查询。

我无法测试，所以我不知道它是否适用于“WHERE s.school.schoolId = :schoolId”，或者您必须使用子查询创建一个 where 。如果不起作用，请尝试使用 native 查询:

Query updateStudentQuery=currentSession.createNativeQuery("sql here");

而不是

Query updateStudentQuery=currentSession.createQuery("Update Student s SET s.school = null WHERE s.school.schoolId = :schoolId");

此外，如果您想使关系成为双向，请将其添加到学校实体:

@OneToOne(mappedBy = "school")
private Student student;

顺便说一句，我猜这个关系是 @ManyToOne 而不是 @OneToOne，不是吗？