Hi guys,

is there any way to leverage Hibernate with DELETE or UPDATE statements?

I have researched various alternatives, all essentially doable but none ideal:

1) Retrieve the objects via Hibernate, then use Session.delete(Object) for DELETE, or call setters on the entity and flush the Session (and use Session.evict() to avoid a memory leak through a bloated first-level cache).
Problems:
1a) Less efficient than an in-database UPDATE or DELETE. I'm doing bulk data manipulation.
1b) More complicated coding because I need to clear out the first-level cache. I can use evict(), which is efficient but may miss subobjects pulled in through foreign keys, or Session.clear(), which throws out not just the bloat but may affect incompletely updated entities from other modules of the program. In other words, the bulk update would require any caller to prepare for the possibility that any of the entitities it uses may have become detached, creating some close coupling between layers (think upper layers preparing some status update in a statistics gathering table, or keeping tab of how much work was done, or simply keeping a records of what user was last active when).

2) Generate SQL and shoot if off using doWork or createSQLQuery.
The Restriction objects are collected from various places such as GUI, configuration, and application logic; I'd generate the SQL object from that and shoot it off to the database.
Problems:
2a) Generating SQL is complicated and error-prone, with several border cases that need to be handled correctly: not generating a WHERE clause if the set of restrictions turns out empty, generating ANDs in the proper places if there is more than one restriction, and not forgetting to wrap each restriction in parentheses in case it contains an OR condition. This requires a lot of care on the part of the application programmer.
2b) It duplicates work done inside Hibernate, which parses update and delete statements written in HQL and generates the equivalent UPDATE and DELETE statements in SQL.
2c) It requires me to pass around SQL metadata such as table and column names together with each restriction.
2d) Anybody coding stuff needs to know the exact mapping between Hibernate and SQL entities, adding yet another opportunity for mistakes.

3) Generate HQL and shoot it off using createQuery.
Problems:
3a) Like 2a, except I'm generating HQL.
3b) Similar to 2b: create HQL just to have it parsed milliseconds later. D'oh.
3c) Restriction objects have no toHqlString, they have just a toSqlString. So I'd need to build my own hierarchy of Condition objects, again duplicating work already done inside Hibernate.

Does anybody know of a fourth approach?
Did I miss something in any of the above approaches and the problems noted above can be mitigated or eliminated?
E.g. I have yet to benchmark approach (1) and check whether there's really any noticeable performance hit - except I don't know how much of the results would transfer to databases other than Oracle (that's what we use now, but the code is going to be ported in the future).