Hi,

I have a requirement that UPDATE operations need additional checks in the WHERE clauses -- our data model has a concept of records being locked by users by setting an integer field to a user id while a piece of work is being processed. I know it's easy to write custom SQL for updates in its entirety but is there a way just to add something onto the end of the update statement (such that it would append "AND locked_by = ?" where ? is the user's id, made available to the whole application).

Thanks!

Steve

Couldn't you create a base persistant object with that property and a method to set it that all other persistant objects inherit from?

Yes, but the point is that our model is such that you should only be allowed to update an entity if you have ownership at the time. We have a number of applications on different technologies and platforms all accessing the same data, and this is the mechanism we have for preventing two programs make changes to a record at the same time. The 'locked_by' field doesn't really belong on the object at all.

The flow might go:
// Lock the work through SQL / HQL
UPDATE thing SET locked_by = 100 WHERE id = 1 AND locked_by IS NULL;

// If the update was successful (altered a single row) you 'own' thing, otherwise someone else has it

// Fully populate a Thing from the database through Hibernate
SELECT ... FROM thing WHERE id = 1

// ... do some work

// Update the Thing through Hibernate
UPDATE thing SET ... WHERE id = 1 AND locked_by = 100

// If rows_returned was 0, you tried to run an update without locking the thing

Does that make any sense?

Thanks,

Steve[/b]