I have two tables DEPARTMENT and EMPLOYEE (the first parent, and the second - the child). That is, each department has a list of employees (one-to-many relationship). Employees cannot exist outside the department, but there can be a department without employees. Therefore, I need to remove the department from the EMPLOYEE table from the DEPARTMENT table when it is deleted.

Implement this with Hibernate. I tried a lot of different options, but when performing the operation I get an error:

ERROR: Cannot delete or update a parent row: a foreign key constraint fails (`STAFF`.`EMPLOYEE`, CONSTRAINT `FK_ons3ycsifhocods0wpg7gym58` FOREIGN KEY (`department_id`) REFERENCES `DEPARTMENT` (`id`))

Here are the sources:

DepartmentEntity:

 @Entity @Table(name = "DEPARTMENT") public class DepartmentDataSet implements Serializable { @Id @Column(name = "id", unique = true) @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; @Column(name = "name", unique = false, updatable = true, length = 45) private String name; @OneToMany(mappedBy = "departmentId", cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval=true) @OnDelete(action = OnDeleteAction.CASCADE) private Set<EmployeeDataSet> employeesSet; public DepartmentDataSet() { } public DepartmentDataSet(String name) { this.name = name; } public int getId() { return id; } public String getName() { return name; } public void setId(int id) { this.id = id; } public void setName(String name) { this.name = name; } public Set<EmployeeDataSet> getEmployeesSet() { return employeesSet; } public void setEmployeesSet(Set<EmployeeDataSet> employeesSet) { this.employeesSet = employeesSet; } @Override public String toString() { return "UserDataSet{" + "id=" + id + "', name='" + name + '\'' + '}'; } }

EmployeeEntity:

 @Entity @Table(name = "EMPLOYEE") public class EmployeeDataSet implements Serializable { @Id @Column(name = "id") @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; @Column(name = "department_id", unique = false, updatable = true) private int departmentId; @Column(name = "age", unique = false, updatable = true) private String age; @Column(name = "type", unique = false, updatable = false) private String type; @Column(name = "name", unique = false, updatable = true, length = 25) private String name; @Column(name = "language", unique = false, updatable = true, length = 25) private String language; @Column(name = "methodology", unique = false, updatable = true, length = 25) private String methodology; public EmployeeDataSet() { } public EmployeeDataSet(String name, String type, String age, int department_id, String methodology, String language) { this.setName(name); this.setType(type); this.setAge(age); this.setDepartmentId(department_id); this.setMethodology(methodology); this.setLanguage(language); } public EmployeeDataSet(int id, String name, String type, String age, int department_id, String methodology, String language) { this.setId(id); this.setName(name); this.setType(type); this.setAge(age); this.setDepartmentId(department_id); this.setMethodology(methodology); this.setLanguage(language); } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getAge() { return age; } public void setAge(String age) { this.age = age; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getDepartmentId() { return departmentId; } public void setDepartmentId(int departmentId) { this.departmentId = departmentId; } public String getLanguage() { return language; } public void setLanguage(String language) { this.language = language; } public String getMethodology() { return methodology; } public void setMethodology(String methodology) { this.methodology = methodology; } @Override public String toString() { return "UserDataSet{" + "id=" + id + ", name='" + name + "', age='" + age + ", type='" + departmentId + '\'' + '}'; } }

DepartmentDAO:

  public void removeDepartment(String name) throws HibernateException { session.beginTransaction(); String hql = "delete from DepartmentDataSet where name = :name"; Query query = session.createQuery(hql); query.setString("name", name); int rowCount = query.executeUpdate(); session.getTransaction().commit(); System.out.println(">>DepartmentDDAO removeDepartment: " + rowCount); }

If you make the removeDepartment method as shown below, then for some reason everything works. But I need my method to get the name of the department, not its id.

  public void removeDepartment(int id) throws HibernateException { session.beginTransaction(); session.delete(session.get(DepartmentDataSet.class, 3)); session.getTransaction().commit(); }

I will be glad to any tips where to dig)

    1 answer 1

    The @OneToMany(cascade = CascadeType.ALL) works only when a particular record is deleted by the session.delete method. You have another case - the SQL-sample results are being deleted: the delete method is not called, and in executeUpdate executes "pure" SQL and DBMS functions. JPA is no longer involved in this process and no annotations are sent to the DBMS along with the SQL query.

    To solve the problem in two ways.

    1. First select the list of entities by name, and then go through the list and call session.delete for each entity.
    2. Leave the query as is, but specify cascading deletion of child records at the DBMS level. To do this, when creating a FOREIGN KEY , add ON DELETE CASCADE .