I have made it my mission to replace the crumbling awful hand-coded DAO apps at my current contract with a nice alternative using hibernate. I think this is the last stumbling block to prove that it can work. (most of the developers are converted PL/SQL programmers who like to have their meetings in SQL and would love SQL on Rails)

We have both a Last Update Date column, and a Created Date column on all tables. There is a trigger on insert that populates the current DB timestamp into the Created Date column, and another trigger on Update that populates the Last Update Date column.

In order to get the optimistic locking working correctly for existing records, I have used the Last Update Date column with the timestamp mapping:



But, it's not "always" generated by the database - only on updates. So when I create a new transient object, the version is null, and the new object is persisted, but then the subsequent SELECT gets a null value for the version, and treats the object as unsaved, eventually producing a TransientObjectException. The data is saved but my object on the Java side can't be modified anymore because now the optimistic locking fails.

I read in the manual:

http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-declaration-version
So that means I can't indicate the object is actually saved by another means.

Is there a strategy that would handle this? I know the best solution is to fix the trigger so the Last Update Date is not null for a record in the DB, but the DBA is the sort who greatly enjoys making developers crazy. It seems that if I could have insert="true" and generated="always", it would do the right thing, but it's not allowed:

I have poked around the code some but I'm not sure where to look to find a way to do this.

Thanks!

-Alex

[ Hibernate 3.1.3, Oracle 10g database ]

Just get the create triger to set the date on both fields.

Alternatively - dump the triggers and use a hibernate event/trigger to update the fields for you. Examples are on the wiki.

I have found the Interceptor / EmptyInterceptor classes that allow me to twiddle the Java object, but I haven't been able to find the hibernate event or trigger you're referring to. That sounds like a good solution, could you post a link?

Thanks!

See these and related pages
http://www.hibernate.org/195.html
http://www.hibernate.org/48.html
http://www.hibernate.org/318.html

It turns out none of the event listeners help. I can set the version into the Object in a listener, but the UPDATE statements inside the Persister always have a WHERE LAST_UPD_DATE=? clause in them, and since that's null in the database I'll always have optimistic locking issues.

It seemed very daunting to try to write a JDBC call inside the event listener, since I'd have to figure out the name of the database table, as well as all the keys (we have a scary number of composite keys) for the update. I need this to be generalized since I have the same problem for all 100+ tables, and don't want to write a listener for each one.

I ended up making a view, which I'll have to have for every table:



This is a little annoying but works nicely with hibernate's generated="always" and doesn't mess up the DBA's bookkeeping too bad.

Thanks!