Problems with composite id

greenThumb · **Joined:** Fri Sep 09, 2005 11:40 am **Posts:** 12

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Name and version of the database you are using: Sybase 11.5

I am dealing with a legacy database so sometimes the structure of the tables is not optimal. I have a table which consists of a composite key containing 3 fields.

Two of the fields are numeric data types in the database represented as Long objects. The third field is a datetime data type in the database represented as a java.util.Date object.

I have an equals method and hashcode method in my object utilizing the EqualsBuilder and HashCodeBuilder classes. Both methods use the 3 composite fields to check for equality and generate the hashcode.

This object, let's call it B is represented as a bag in object A. So A's can have many instances of B.

I am using versioning in object B. In order to get past the initial select I had to set the Flush Mode to Never because all of the objects in the bag (B) were being updated during the select and this seemed to cause the version number in the object and the version stored in the database to get out of sync. I am not sure if setting the Flush Mode to Never was correct but it got me further.

Now, on updates I get a StaleObject exception. It seems that problem is definitely surrounding the use of the composite id. I did modify the Sybase table to add an identity key but this involved creating a new table with an identity key and then inserting all the data from the original table, dropping tables, creating the table (to get the naming correct) and making sure all the constraints, dependents were preserved. Of course then with an identity column and using it as the primary key takes care of my problem but there are a number of tables consisting of composite keys and I would rather not have to fool around with the production data in that manner, but if I have to then that what will have to be done.

Anyway, is there a problem with using a datetime column as a composite key and using it in the EqualsBuilder/HashCodeBuilder? This thought just came to me - should I be using java.sql.Date instead of java.util.Date?

Any help would be greatly appreciated. Unfortunately I can not post any of the actual code but I can do a mock up if that would help.

Michelle

albert_kam · **Posted:** Fri Mar 10, 2006 5:53 am

Hi, maybe u could post your hbm.xml of A and B ? your version of hibernate ?

I've been dealing with lots of legacy tables that use composite keys, and so far it's ok .. The very important point is to use managed versioning in composite key model.

Quote:

In order to get past the initial select I had to set the Flush Mode to Never because all of the objects in the bag (B) were being updated during the select and this seemed to cause the version number in the object and the version stored in the database to get out of sync.

I've never set 'flushmode' to 'never' .. and i'm not sure if the select would cause the version to be increased. An excerpt from hibernate in action,

Quote:

Hibernate flushes occur only at the following times:
* When a Transaction is committed
* Sometimes before a query is executed
* When the application calls Session.flush() explicitly

Did you do any changes/updates on the related tables before doing the query ?

greenThumb · **Joined:** Fri Sep 09, 2005 11:40 am **Posts:** 12

Here are some code mock ups of what I am dealing with:

Code:

<class name="PersonInfo" table="person_table">
         <id name = "personId" column="person_id" type="long">
                <generator class="native"/>
         </id>

         .....

          <bag name="shots" inverse="true" cascade="save-update">
                 <key column="person_id"/>
                 <one-to-many class="Shot"/>
          </bag>
</class>

The other mapping file looks like:

Code:

<class name="Shot" table="shot_table">
          <composite-id>
                 <key-property name="personId" type="long" column="personId"/>
                 <key-property name="shotId" type="long" column="shot_id"/>
                 <key-property name="dateGiven" type="date" column="date_given"/>
           </composite-id>

           <version name="version" column="version" type="integer" unsaved-value="null"/>

           ....
</class>

The Shot class has the following equals and hashcode methods:

Code:

public boolean equals (Object obj) {
    if (this == obj) return true;
    if (!(obj instanceof Shot)) return false;
    final Shot s = (Shot)obj;

    EqualsBuilder eb = new EqualsBuilder();
    eb.append(this.personId, s.getPersonId());
    eb.append(this.shotId, s.getShotId());
    eb.append(this.dateGiven, s.getDateGiven());

     return eb.isEquals();
}

public int hashCode() {
     if (this.personId == null) {
           this.personId = new Long(0);
     }
     
      HashCodeBuilder hb = new HashCodeBuilder();
      hb.append(this.personId);
      hb.append(this.shotId);
      hb.append(this.dateGiven);

     return hb.hashCode();  // don't remember the actual method call
}

This is basically what I have. During the initial load of the PersonInfo object I would see updates being performed against the Shot collection. I thought maybe that was causing the version number in the object to become out of sync with the version number in the data base and that is why I set the flush mode to never. Since we did not flush the updates did not happen.

Now, there are other collections of objects in the PersonInfo object. We have special accesses in the application and those access rules are performed via named queries. So what we do is we load the PersonInfo object based on a person id. Then for the collections that have special access rules we set the collection to null and then do a query on that collection using the named query. The named query checks certain access privileges for the logged in user to determine what set of data to retrieve if any. This was performed via a view in the legacy system but we could use the view because it was using the special key word USER (Sybase) to determine the logged in user. In the new system we are using connection pooling so each user is not actually logged into the database.

So, we are making changes to the object perhaps that was causing the updates when the session was closed? And if so, that is fine accept it was during these updates that we initially saw the problem with this class and the version number.

The Shot collection does not have a named query that needs to be invoked or anything but I am now thinking that because we modify the other collections it causes an update but I still don't understand why we get a stale object exception for the Shot collection. For some records no error is reported and on others it will go through like the first 4 items of the Shot collection fine and then bomb on the 5 item with a stale object exception. I have looked at that 5th object and the personId, shotId and dateGiven are unique amongst the rows in the collection.

I am not at work today but the Exception says something to the effect that the object may have been modified/deleted by another transaction.

If we have to modify the table structure to add an identity column then that is what we will do. I have already tested this approach and it does not seem have any ill affect on the legacy system which must remain operational during the transition but since we do have other composite key tables I would like to know how to do this properly in Hibernate.

Any help will be greatly appreciated.