Hibernate - Mapping foreign keys tables

[Rick Smith]

I have been reading the book Hibernate In Action in order to better understand OR Mapping to improve my current project.

In particular I want to move away from more direct mapping of classes to tables to a better 'domain model' (if I understnad this concept properly) for use by my business tier.

The tables this concerns are arranged as below (please excuse the crude representation) An order record has a number of status records referenced by foreign key, there is also a reference from each status record to a status type which gives more information for orders at that stage.

    
 --------------------           --------------------        ------------------
|    tblOrder        |         |   tblStatus        |      | tblStatusType    |
 -------------------- 1 -- 1.*  -------------------- 1 -- * ------------------
|varchar orderID (PK)|         |int recordID (PK)   |      |int recordID      |
|varchar desc        |         |varchar orderID (FK)|      |varchar typeID    |
|                    |         |varchar typeID      |      |varchar statusDesc|
 --------------------           --------------------        ------------------

I want to map these tables to a simple 'model' of two classes; Order and Status, where Order has a List of Status

From the reading I have done my understanding is that if I get the OR Mapping right my business tier can deal with an order as an object without worrying about how it relates to the database, this is more obvious when i come to update the state of the order. Instead of having to call some DAO to add a new status record, I should be able to add a new Status object to the Order object and my Hibernate mapping would take care aof the rest, am I correct?

If so then I have a problem with my mappings:

<class name="Order" table="tblOrder" dynamic-update="true" optimistic-lock="version">
  <meta attribute="implement-equals">true</meta>
  <meta attribute="implement-tostring">true</meta>
    
  <!-- Primary Key  -->
  <id name="orderID"  type="string" column="orderID" length="10" unsaved-value="none">
    <meta attribute="scope-set">public</meta>
    <meta attribute="use-in-tostring">true</meta>
    <generator class="assigned"></generator>
  </id>
    
  <!-- Fields -->
    
  <property name="description" column="desc" type="string" not-null="true" length="100">
    <meta attribute="use-in-equals">true</meta>
    <meta attribute="use-in-tostring">true</meta>
  </property>
    
  <set name="OrderStatus" inverse="true" cascade="all" >
    <key column="orderID" not-null="true" />
    <one-to-many class="Status"/>
  </set>
</class>
 
 
<class name="Status" table="tblStatus" dynamic-update="true" optimistic-lock="version">
  <meta attribute="implement-equals">true</meta>
  <meta attribute="implement-tostring">true</meta>
 
  <!-- Primary Key  -->
  <id name="recordID" column="recordID" type="long" unsaved-value="none">
    <meta attribute="scope-set">public</meta>
    <meta attribute="use-in-tostring">true</meta>
    <generator class="native"></generator>
  </id>
 
  <!-- Fields -->
  
  <property name="typeID" column="typeID" type="string" not-null="true" length="5">
    <meta attribute="use-in-equals">true</meta>
    <meta attribute="use-in-tostring">true</meta>
  </property>
 
  <many-to-one name="order" class="Order" column="orderID" not-null="true" />
</class>

The problem comes when I attempt to write a new Order with an initial Status I get an exception:

 ...
org.hibernate.exception.ConstraintViolationException: could not insert: [Order]
...
..
Caused by: java.sql.SQLException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__tblOrder__OrderID__2236BEF8". The conflict occurred in database "Orders", table "dbo.tblOrder", column 'OrderID'.
...

To me the error seems to suggest that I cannot add the Status to the Order object before the Order is initially saved, although this goes against my understanding of the benefits of ORM, so I hope my mapping is wrong.

Any help would be appreciated.
[ November 18, 2008: Message edited by: Rich Peate ]

[Kuladip Yadav]

How do you write a new Order with an initial Status ?

[Rick Smith]

Thank you.

I've managed to get it to work, I think the problem was two-fold.

My Value Object had identifier values which were declared as primitive type int instead of Integer, this would mean that hibernate found an id for those objects and so woudl expect them to exist, null (not set) tells hibernate that the object is a new one! - although obvisouly this isn't relevent to the Order objects.

Also in my DAO I was using session.saveOrUpdate(object) (is this what you were refering to?) changing that to session.save(object) allows the Order and any status added to the Order object before saving are then saved.

Is the fact that the orderID field is assigned in my business code the reason that the saveOrUpdate method is not working? (again hibernate cannot determine if the object is new or should already exist)
---
A slight aside that I've been pondering about at the same time (perhaps athis should be a separate post?) is the degree to which the associations should be mapped..

A rather contrived scenario, esspecially in this case, but if all our orders were related in some way via a relation table, e.g:

 --------------------------
| tblRelation              |
|--------------------------|
|int recordID (PK)         |
|varchar orderID (FK)      |
|varchar relatedOrderID(FK)|
|varchar comment           |
 --------------------------

If we expanded out mapping to populate relations in a List<Order> on each Order this would mean that in retrieving one Order record I may end up retrieving all it's related Orders, along with all those related Order's Orders etc.

In retrieving the Order I'm interested in it seems reasonable to retrive all it's related Orders, but not necessarily all the distently related orders, the danger being that I may end up retrieving a significant portion on the Database depending on how many relations were made.

Is there any guidence on what to do here? I see the posibility of a few options:
1) Not map relations, instead provide a different was to access them, although this may mean to obtain an Order and all it's related orders two queries are required.
2) Provide a different mapping, one might return all the related orders the other might exclude related Orders, this would result in either a different Object type, or partially populated objects returned to the business and presentaion tiers.
3) Perhaps Hibernate offers some support for this - I would hope it's a common pattern with a common solution.
4) Perhaps the datamodel is over complicating things, although I have little influence on the design of that.
[ November 21, 2008: Message edited by: Rich Peate ]