Hibernate: Bulk delete on join table?

[Peter Merker]

Hi,

I have a many-to-many association between classes A and B. Now I want to delete a couple of A-B associations with only ONE delete statement. To further illustrate this: Let's say, we have the following instances and associations:

a1 -> b1, b2
a2 -> b2, b3

So far, Hibernate issues two delete statements, one for each collection of B's in each A. Is there a possibility to get this down to just ONE statement, and how?

[Peter Merker]

Is it not possible to reduce the number of delete statements or was my problem description too short? No matter if I used a HQL query or a Criteria, I always ended with two delete statements whereas I'm looking for something which results in this kind of SQL: 'delete from JoinTable where idA in (select id from A where... )' I guess I should add that there are parents and children in the table A. So if I want to delete the associations to B's for a parent, I also want to delete them for it's children.

[Peter Merker]

Hmm. I'm using native SQL now, but this is far from ideal as I'm losing portability this way. This is the code now:

String sqlQuery = "delete from CONTAINERS2ITEMS where CONTAINERS_FK in " +
"(select C2I.CONTAINERS_FK " +
"from CONTAINERS2ITEMS C2I, CONTENT C " +
"where C2I.CONTAINERS_FK = C.ID " +
"and C.ROOT_ID = ? " +
"and C.LFT >= ? " +
"and C.RGT <= ? " +
"group by C2I.CONTAINERS_FK)";
PreparedStatement preparedStatement = getSession(false).connection().
                prepareStatement(sqlQuery);
preparedStatement.setString(1, parent.getRootId().getValue());
preparedStatement.setInt(2, parent.getLft());
preparedStatement.setInt(3, parent.getRgt());
preparedStatement.executeUpdate();

As you can see, Containers and Items are both tuples in the table CONTENT, CONTAINERS2ITEMS is the join table. Contents are structured in nested sets. I didn't write about this before in order not to complicate things unnecessarily.
Anyway, still no one with an idea how to use HQL or a Criteria for this?
[ July 25, 2007: Message edited by: Peter Merker ]