Hibernate; HQL; why does the delete query not work, but select does?

Question

I want to delete certain records from a table. These records have some child-records in other tables.

In order to be able to delete the main records, I have to delete the child records first.

Here is the example of the HQL used:

delete from ItineraryBooking ib where ib.booking.user.id = :paramId

Basically, this should remove all ItineraryBookings (records in seperate table), these are joined to the Booking table. A Booking table can be joined with the User table.

The odd thing is that when you change the above to:

from ItineraryBooking ib where ib.booking.user.id = :paramId

And execute a Query.list(), it will work just fine.

Whenever I want to execute the delete variant, it looks like Hibernate generates an odd delete statement. Is my HQL wrong? Or is it a Hibernate quirk?

Answer 1

From the hibernate manual:

No joins, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.

Your ib.booking.user.id clause looks like a join to me. I don't know if Hibernate actively rejects joins in a delete statement, or just silently gets it wrong.

A nicer way to delete child records is to use cascading deletes.

Answer 2

Simple questions that may help:

1. Just for curiosity, are you running this HQL in a transaction? selects doesn't need a transaction, but deletes does need it.

2. Are you flushing the session after executing the deletion HQL?

3. Are you deleting, and selecting in the same transaction or in separate ones?