Hibernate - Legacy Database Question

[Jim Cross]

I have a table in my database which is versioned, so each record consists of ID, VERSION, TRADE_DATE, VALUE_DATE, etc where the primary key is a composite of ID and VERSION.
So a trade with ID 12345 starts with VERSION 1, and this version number gets incremented on each update.
Is there a recommended pattern for handling this in Hibernate? My current understanding is that I need to use the composite-id tag, and create a TradeKey inner class in my Trade bean class, with the TradeKey class holding the ID and VERSION. I then have to handle the generation of trade ids and version myself. Is this the best way to do it?
If so, is there any way to create my own SQL prepared statements using Hibernate? I know you can create normal SQL queries, but I'd ideally like to use a prepared statement which parameterises the object type (my key generator table in the database as it is currently holds keys for various object types - trade, cashflow etc). If possibly I'd like to query and update this table using a prepared statement. If this isn't possible using Hibernate, I'll have to do this part of it using standard JDBC, which I'd rather avoid...

Also, if I was looking to refactor this trade table to make it more suitable for Hibernate in the future, what would be the best way to structure the primary key, given that each trade must maintain the "trade id" and incremented version number throughout its life?

Any help much appreciated.

Thanks,

Jim

[Scott Johnson]

the primary key is a composite of ID and VERSION

If you update entity with ID 12345 from version 1 to version 2, do you keep version 1 in the table and insert version 2?

a TradeKey inner class

TradeKey can be an inner class, but it doesn't have to be.

is there any way to create my own SQL prepared statements using Hibernate?

Yes, using a Hibernate named query is one way.
[ July 16, 2006: Message edited by: Scott Johnson ]

[Jim Cross]

Thanks Scott.

If you update entity with ID 12345 from version 1 to version 2, do you keep version 1 in the table and insert version 2?

Yes, so it's update as insert, and we maintain the full history of each trade in the table.

[Jim Cross]

OK, that solved that problem

Another question related to this.
Trades have a one-to-many relationships with cashflows. So the cashflow table contains the columns TRADE_ID, TRADE_VERSION, which are a foreign key into the TRADE table.

Using the following in my Hibernate mapping file, when I load a trade it now loads a set of cashflows. However, while it populates most of the properties correctly, it doesn't populate the tradeId and tradeVersion properties of the cashflows...

<set name="cashflowList" table="J_CASHFLOW">
  <key>
<column name="TRADE_ID" />
<column name="TRADE_VERSION" />
  </key>
  <composite-element class="com.jc.hibernate.bean.Cashflow">
<property name="id" column="ID" />
  <property name="ibisEvent" column="IBIS_EVENT" />
  <property name="amount" column="AMOUNT" />
  <property name="payDate" column="PAY_DATE" />
  <property name="payable" column="PAYABLE" type="boolean" />    
  </composite-element>
</set>

I guess this is because the column element only specifies the database column, and not the Java bean property to map that column on to. Any ideas how I can specify this?

[Scott Johnson]

Any ideas how I can specify this?

I've not tried this before, so this is a wild guess... But can you add the column <property> tags even though they are already in the <key>:

    
    <set name="cashflowList" table="J_CASHFLOW">
      ...
      <composite-element class="com.jc.hibernate.bean.Cashflow">
    ...
      <property name="TRADE_ID" column="TRADE_ID" />
      <property name="TRADE_VERSION" column="TRADE_VERSION" />
      ...    
      </composite-element>
    </set>

[Jim Cross]

I tried that yesterday, but it didn't allow me to specify those columns in both the key and the composite element.
However, I finally got it working by doing the right way (I think!):

    
<set name="cashflowList" table="J_CASHFLOW">
  <key>
<column name="TRADE_ID" />
<column name="TRADE_VERSION" />
  </key>
  <one-to-many class="com.jc.hibernate.bean.Cashflow" not-found="ignore"/>
</set>