With the advent of the <filter> tag in Hibernate, I assumed there would be more support for managing "active" objects. My table has not only effective and termination dates indicating what records are "active", but also has a last updated timestamp as part of the primary key.

Below are both the mapping document and the DDL for the table in question.

What I am hoping to find is a way to have Hibernate manage some of these date fields automagically.

My filters are currently returning the records I expect, and Hibernate hydrates the objects I expect. But if I make a change to an object and save it, I get a whole new row (as expected with the versioning), but I can't figure out how to update the termination date on the old row.

Unfortunately I cannot do any of this with triggers, as the DBAs consider this "application logic" that they don't want in their tables. The fact that my application doesn't give a hoot about old, "inactive" records is totally lost on them.

The only solution I have come up with is maintaining the versioning timestamp myself, always making a copy of the object upon retrieval, only modifying the copy, then updating the termination date on the original and saving that before ever saving a copy. This means each object has to know if it is dirty (so I don't actually do any saving if not) and how to copy itself (not ideal for complex objects).

And the worst thing, from my point of view, is this is the simple table. I have a similar challenge with link tables looming in the near future. The auditors want to be able to tell what permissions a user had three months ago, to all the many-to-many links between permissions and roles and users have to be timestamped and effective, termination dated.

Any help or suggestions anyone could give would be greatly appreciated.

-- Clayton --

Mapping documents:
<class name="LegacyMessage" table="TA01495" lazy="false" >
<composite-id>
<key-property name="messageType" column="CLM_MSG_TYP_CD" type="com.bcbsks.kcaps.dao.hibernate.usertype.LegacyMessageTypeUserType"/>
<key-property name="messageCode" column="CLM_MSG_CD" />
</composite-id>
<version name="updatedTimeStamp" column="CLM_MSG_UPD_TS" type="calendar" />
<property name="effectiveDate" column="CLM_MSG_EFF_DT" type="calendar_date" />
<property name="terminationDate" column="CLM_MSG_TERM_DT" type="calendar_date" />
<property name="adnlTxIndicator" column="CLM_MSG_ADNL_TX_IN" not-null="true" />
<property name="messageStateCode" column="CLM_MSG_STAT_CD" not-null="true" />
<property name="notes" column="CLM_MSG_NOTE_TX" not-null="true" />
<property name="superOpIndicator" column="CLM_MSG_SPR_OP_IN" not-null="true" />
<filter name="mostRecent"
condition="CLM_MSG_UPD_TS = (select max(t.CLM_MSG_UPD_TS) from DBZACM.TA01495 t where t.CLM_MSG_TYP_CD=this_.CLM_MSG_TYP_CD and t.CLM_MSG_TYP_CD=this_.CLM_MSG_TYP_CD)"/>
<filter name="active"
condition=":asOfDate BETWEEN CLM_MSG_EFF_DT and CLM_MSG_TERM_DT"/>
</class>
<filter-def name="mostRecent" ></filter-def>
<filter-def name="active">
<filter-param name="asOfDate" type="calendar_date"/>
</filter-def>


Database Table DDL:
CREATE TABLE DBZACM.TA01495
(CLM_MSG_TYP_CD CHAR(1) NOT NULL,
CLM_MSG_CD CHAR(3) NOT NULL,
CLM_MSG_UPD_TS TIMESTAMP NOT NULL,
CLM_MSG_EFF_DT DATE NOT NULL DEFAULT CURRENT DATE,
CLM_MSG_TERM_DT DATE NOT NULL DEFAULT '9999-12-31',
CLM_MSG_SPR_OP_IN CHAR(1) NOT NULL,
CLM_MSG_ADNL_TX_IN CHAR(1) NOT NULL,
CLM_MSG_STAT_CD CHAR(1) NOT NULL,
CLM_MSG_NOTE_TX CHAR(32) NOT NULL,
CONSTRAINT C4108385 PRIMARY KEY(CLM_MSG_TYP_CD, CLM_MSG_CD, CLM_MSG_UPD_TS));