Hello Everyone,
I'm struggling with a legacy Sybase database and was wondering if anyone could help me out with a tricky problem please.
Before I describe the problem I should point out that the database I am using does not enforce referential integrity in the schema - although it is enforced by applications which use it. I understand that by definition I am asking an ORM tool to deal with an RDBMS without relational behaviour however I have been able to successfully solve a number of issues with Hibernate because even though the database isn't enforcing relational integrity, the data is consistent with it's normalisation.
I'm currently mapping the tables in the database using Hibernate 3 (hbm) mappings. I have the following tables which are related:
PAYMENT, ITEM and SUBSCRIPTION.
The idea is a PAYMENT can be for an ITEM or for a SUBSCRIPTION. So there is a one-to-one relationship between PAYMENT and ITEM and SUBSCRIPTION, like this:
PAYMENT - ITEM
PAYMENT - SUBSCRIPTION
I would like the Payment Java class which is being generated by the hbm2java Ant task to create an class structure which looks like this (please excuse the pseudo code), as this will be consistent with the rest of my application:
Code:
public class Payment implements Serializable {
private Item item = null;
private Subscription subscription = null;
// accessors & mutator methods to go here...
}
(If I could get this far I would be happy and then after that I could consider looking to introduce better polymorphic behavour to the object graph)
This is where is gets awkward.
Firstly the PAYMENT table does not have a unique primary key, however a unique composite key is implied on it. The PAYMENT table has a number of columns, however the following represents a unique row:
PAYMENT
+ TARGET_ID (non-null int)
+ TARGET_TYPE (non-null int)
+ INVOICE_LINE_ID (non-null int)
And I have mapped this in Hibernate like this:
Code:
<class name="PaymentDBO" table="PAYMENT">
<composite-id
class="com.example.PaymentCompositeId"
mapped="false">
<meta attribute="use-in-tostring">true</meta>
<meta attribute="use-in-equals">true</meta>
<key-property name="targetIdKey" type="integer"column="TARGET_ID" />
<key-property name="targetTypeKey" type="integer" column="TARGET_TYPE" />
<key-property name="invoiceLineIdKey" type="integer" column="INVOICE_LINE_ID" />
</composite-id>
<property name="targetId" type="integer" not-null="true"
insert="false" update="false">
<meta attribute="use-in-tostring">true</meta>
<meta attribute="use-in-equals">true</meta>
<column name="TARGET_ID" />
</property>
<property name="targetType" type="integer" not-null="true"
insert="false" update="false">
<meta attribute="use-in-tostring">true</meta>
<meta attribute="use-in-equals">true</meta>
<column name="TARGET_TYPE" />
</property>
<property name="invoiceLineId" type="integer" not-null="true"
insert="false" update="false">
<meta attribute="use-in-tostring">true</meta>
<meta attribute="use-in-equals">true</meta>
<column name="INVOICE_LINE_ID" />
</property>
</class>
And defined my own serlializable class, PaymentCompositeId, to represent this key. All of this works fine and I have used a similar pattern to get other similar tables mapped and working ok.
Secondly the ITEM and SUBSCRIPTION tables both have a unique KEY, as per:
ITEM
+ ITEM_ID (non-null int)
SUBSCRIPTION
+ SUBSCRIPTION_ID (non-null int)
Here's the fun bit :-D
Items and Subscriptions are related to the Payment via the TARGET_ID and THE TARGET_ID type of the PAYMENT table.
The TARGET_ID_TYPE in the PAYMENT table can only have two values, 0 or 1.
If the TARGET_ID_TYPE equals 0 in the PAYMENT table then TARGET_ID is the ITEM_ID of the ITEM, otherwise
If the TARGET_ID_TYPE equals 1 in the PAYMENT table then TARGET_ID is the SUBSCRIPTION_ID of the SUBSCRIPTION
In other words, if the payment target id type is 0, it is a payment for an item. If the payment target id type is 1 then it is a payment for a subscription.
I've been banging my head against the wall trying to get this conditional logic into the hibernate mapping files to define an 'item' and a 'subscription' property. I've tried various incarnations of embedded formulas and where clauses, but I just can't seem to crack this one.
If somebody could perhaps give me a nudge in the right direction it would be much appreciated.
Many thanks in advance.
-Andy
p.s. The database strucutre is out of my control and I'm unable to change it :-(
Login
FAQ
Search