Hi,

This might sound as a newbie question, but I'll post it as I need help.

I have the following tables in an Oracle 10g database and I need mapping for it but somehow can't get it right. I am using Hibernate 3.2.

Order
------
Order_Id
Name


Billing
-------
Billing_Id
Billing_Name
Version


Order_Billing
-------------
Order_Id
Billing_Id
Billing_Version

An Order can have multiple billings with different versions. The current billing is indicated by Order_Billing.Billing_Version. So When I need to look up the billing for an order I run the following SQL:

Select b.Billing_Id, b.Billing_Name from Order o, Billing b, Order_billing ob where
b.billing_id = ob.billing_id and
b.version = ob.billing_version and
o.order_id = ob.order_id and
o.order_id = ?

My mapping in Order.hbm.xml is as follows

<class name="Order" table="Order">

<!-- Other details ommited -->

<set name="billings" table="Order_Billing" lazy="false">
<key column="order_id"/>
<many-to-many column="billing_id" class="billing" />
</set>

The other file Billing.hbm.xml is plain simple file that maps some of the Billing class attributes to columns in the billing table.

Now I am trying to know how can I ask Hibernate to match the version column in the billing table to the billing_version column in the order_billing table (b.version = ob.billing_version clause in the SQL).

I am using a <set> as I'll only be returning 1 billing record at a time, the one indicated by Order_Billing.Billing_Version.

Any help is appreciated.

Thanks,
N

Make the (Billing_Id and Version) as the composite primary key and then do the mappings accordingly ...