Hi Tom, Marina, Michael...
While I'm investigating the issue 2744(bulk delete problem with temporary
table), I come to have a very curious question.
Should bulk DELETE query handle relationships automatically or should
application do it manually by another query? It seems there is no words
about it in the specification.
For example, if Department and Employee have one-to-many relationship, and
Employee and Project have many-to-many relationship like below.
@Entity public class Department {
@Id Integer id;
String name;
@OneToMany(mappedBy="dept") Collection<Employee> employees;
}
@Entity public class Employee {
@Id Integer id;
String name;
@ManyToOne Department dept;
@ManyToMany(mappedBy="members") Collection<Project> projects;
}
@Entity public class Project {
@Id Integer id;
String name;
@ManyToMany Collection<Employee> members;
}
(1) "DELETE FROM Project p WHERE p.name = :name"
(there is many-to-many relationship bet. Project and Employee)
Should this also remove many-to-many relation rows from relation table
automatically?
TopLink is now doing this, generating DELETE SQLs of many-to-many relations
like below.
ExpressionQueryMechanism:1165
// Add statements for ManyToMany and DirectCollection mappings
Vector deleteStatementsForMappings =
buildDeleteAllStatementsForMappings(selectCallForExist,
selectStatementForExist, tablesToIgnore == null);
if(!deleteStatementsForMappings.isEmpty()) {
if(getSQLStatement() != null) {
getSQLStatements().add(getSQLStatement());
setSQLStatement(null);
}
getSQLStatements().addAll(deleteStatementsForMappings);
}
(2) "DELETE FROM Department d WHERE d.name = :name"
(there is one-to-many relationship bet. Department and Employee)
Similary, should this also remove one-to-many relations automatically?
Toplink is *not* generating any SQLs to set relationship column of the owner
to NULL in this case, so foreign key constraint violation occurs!
To avoid constraint violation, I should execute another query like "UPDATE
Employee e SET e.dept = NULL WHERE e.dept = :dept" before DELETE query.
In sum, it seems that two cases are inconsistent and it's not clear whether
bulk DELETE query should handle relationship automatically.
If it's not proper thing for provider to handle relationship like (1) and
application should always handle relationship for itself like I did in (2),
I could not figure out a query to update collection association field to
NULL for (1) like below.
"UPDATE Project p SET p.members = NULL WHERE p.name = :name" -> p.members =
NULL is error as JPQL only allows single valued association field for
update.
I guess this issue seems something to be clearified in the spec.
Could anyone give me some explanation or opinion about this?
Thanks,
-Wonseok