We've recently developed an application that has audit triggers which update an object history. We've implemented Hibernate for object persistence but we've had some trouble with providing an elegant solution to audit table reading. Specifically, we have a table X (a complex object with array-based child attributes) and its audit history X_audit. Creation and updates are pretty simple becuase the triggers fire audit records into X_audit without intervention from Hibernate.
However, the difficulty comes about when selecting time-driven data from X/X-audit. The audit table has a from- and to- date on it, and the X table has a from- on it. So selection is a complex process: for Date (D) if there is an X-audit record with (D) between from- and to- then retrieve this. Otherwise, if X there is an X with from- < (D) then use this.
My question is twofold: (i) what's the best object-relational mapping technique to represent this? I've mapped X as an entity and done the SQL using a createSqlQuery() method. (ii) What's the most elegant way of doing the retrieve ? I don't think it can be done in pure SQL because it requires logical precidence.