Hi Tom,
I found problem in TopLink that shared cache does not reflect the change of
many-to-many associations which are removed by DELETE query.
i.e. even though many-to-many assocations are removed in database, Employees
have still associated Projects which are deleted by the query(of course em
is closed and recreated). If I clear shared cache manually after DELETE
query, this does not occur.
Hmm, anyhow I think bulk DELETE cause confusions and can cause problem. it
needs to be clarified.
Cheers,
-Wonseok
On 3/30/07, Tom Ware <tom.ware_at_oracle.com> wrote:
>
> I think this is an area where the spec could use some clarification.
>
> If we cannot remove the entries from join table, there are very few
> cases where a bulk delete can be called on an Entity with a
> bidirectional ManyToMany relationship. It would be nice if we could
> maintain this functionality somehow.
>
> -Tom
>
> Wonseok Kim wrote:
>
> > Hi Michael,
> >
> > spec only says cascade which I think means associated entites are not
> > deleted even with Cascade.DELETE, but does not tell association itself
> > should be maintained or not - of course the association should be
> > removed for successful DELETE, but It's not clear that application
> > should do it.
> > Also, do you have an idea regarding an UPDATE query which can remove
> > many-to-many association for DELETE query (1)?
> >
> > Thanks,
> > -Wonseok
> >
> > On 3/30/07, * Michael Bouschen* <Michael.Bouschen_at_sun.com
> > <mailto:Michael.Bouschen_at_sun.com>> wrote:
> >
> > Hi Wonseok,
> >
> > I think a buld delete should not handled relationships. Here is
> > what the
> > spec says in chapter "4.10 Bulk Update and Delete Operations":
> > A delete operation only applies to entities of the specified class
> > and
> > its subclasses. It does not cascade to related entities.
> >
> > Regards Michael
> >
> > > 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 <http://p.name>
> > <http://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 <http://d.name>
> > <http://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
> > <http://p.name> <http://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
> > >
> >
> >
>