Composite Keys where one element of the key may be null

agiaccone · **Posted:** Mon Dec 18, 2006 7:47 pm

I'm in a situation where I'm reverse engineering a legacy system to use Hibernate. That said, I'm open to possible solutions, but obviously the less modification I have to make to the current schema and views the better.

I have a situation where a view has been created. I'm trying to map this view into Hibernate. The view is a left-join of two tables. The view represents two tables that have an optional to-many relationship from the first table to the second. The value from the first table may be repeated many times, with unique values from the second table ensuring uniqueness for the "key" or, the values in the first member of the composite key will only be present once and the second element of the key will be null. The result of this is that I have to map a composite key on the view that includes rows where the value of one element of the composite key will be null.

I mapped this relationship like this:

<composite-id name="id" class="org.myorg.db.ReqQueue$Id">
<key-property name="ReqId" column="REQ_ID">
<key-property name="DocId" column="DOC_ID">
</composite-id>

The problem seems to be that Hibernate chokes on the DOC_ID column being null. When later on I try to query the view using a like criteria, I get a resulting Collection that includes null entries. These null entries always correspond to items which have null as the value for Doc_ID.

My question is, can this situation be mapped with Hibernate, without changing the underlying view? If so, how? If it can't be, I'm open to other possibilities, any suggestions?

VampBoy · **Posted:** Thu Dec 21, 2006 3:33 pm

I had the same type of issue a LONG time ago, at one point it was decided that if the PK is null then it will return NULL as the object. So the short answer to your question is pretty much no.

In our case, the requirement to talk to said view went away hence our problem did as well.

Javier Martinez · **Posted:** Thu Dec 21, 2006 6:33 pm

Hi, you can use, the option not-null (not-null="false") into the required tags.

Best regards.

midnightraver · **Joined:** Tue Apr 01, 2008 3:34 pm **Posts:** 10

I realize this is a very old post, but thought I'd throw some additional detail into the mix.

In your hbm.xml file you will note that the object for the view consists of a single id field. This id field is then represented as a composite id with every field being a key-property. The docs for key-property indicate that this key-property is "never null". Because this is a key-property this also means that you can not specify not-null=false.

In org.hibernate.type.ComponentType you will find the code...

Code:

public Object hydrate(
         final ResultSet rs,
         final String[] names,
         final SessionImplementor session,
         final Object owner)
         throws HibernateException, SQLException {

      int begin = 0;
      boolean notNull = false;
      Object[] values = new Object[propertySpan];
      for ( int i = 0; i < propertySpan; i++ ) {
         int length = propertyTypes[i].getColumnSpan( session.getFactory() );
         String[] range = ArrayHelper.slice( names, begin, length ); //cache this
         Object val = propertyTypes[i].hydrate( rs, range, session, owner );
         if ( val == null ) {
            if ( isKey ) {
               //AZG - incorrect behavior for composite keys built on views? So long as
               //  one value is not null the key is valid, no?
               return null; //different nullability rules for pk/fk
            }
         }
         else {
            notNull = true;
         }
         values[i] = val;
         begin += length;
      }

      return notNull ? values : null;
   }

...this is the heart of the matter. Note the AZG comment I have inserted. If any field is null then a null result will be returned. I'm not sure if this line could simply be commented out or if this would break composite keys elsewhere (e.g. composite primary key relationships ). Since a view might contain fields that are null this breaks the contract implied by the docs for the schema, but it seems to me like this is a shortfall in the reveng/ORM capabilities...

Anyway, thought I would throw this into the post for posterity.[/b]