Hi There,

Is there a way to issue a sql update with certain where clause via Hibernate? For example, I have a table with two columns: key int, value int. I want to be able to update the row to the effect that's similar to the following sql statement:
update table tableName set value= (select value + 1 from tableName where key = 1).

The idea is to ensure the uniqueness of the value.

The normal way I can think of is:
1) load the object with the given key,
2) increment the variable for value,
3) flush/update the object.

But there is a chance multi-user may get the same value. You would need to synchronize the code block, or use Hibernate lockMode feature (which I have yet to play with).

I guess this is just one example but in general I wonder if there is a way to issue a sql statement for update/delete/insert with complex where clause where the qualifies may not be easily encapsulated in the object (graph) itself (like the example above).

I read throught the Hibernate document and it seems I can issue direct sql statement only for query.


Any pointer appreciated.

Thanks,

Mike

I think you could accomplish this with some sort of interceptor but I doubt if Hibernate supports issuing an update-select directly.