Relationship foreign key uses '0' instead of null

jcouball · **Joined:** Tue Aug 10, 2004 3:20 pm **Posts:** 11

I am having a mapping problem. I am mapping to an existing database whose schema would probably take an act of God to change. In this database all primary keys are integer values. In a one-to-many relationship between two tables, instead of making the foreign key null when the relationship does not exist, the programmer sets the foreign key field to '0' instead.

Is there a way to map the many side ( in the <many-to-one...> tag) to say that '0' means there is no related record instead of null?

Any help greatly appreciated.

Mike Canham · **Posted:** Sun Aug 22, 2004 12:22 am

James,

I am not entirely sure of all the problems that you are encountering .....

Have you looked at defining your own UserType.

This UserType could map a null in your java class to a 0 when writing to the database and vice versa when reading.

If you have a look at the topic "Handling Dates in a Legacy System" (in the forum) you will see the fun and games I have had trying to cope with an empty string representing Null in a date field.

Fortunately, I do not think your problems are nearly so bad .......

You would need to modify the sqlTypes to integer:

Code:

public int[] sqlTypes() { 
  return new int[] { Types.INTEGER }; 
} 
 

You would also have to modify the nullSafeGet() and nullSafeSet():

Code:

  
public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { 
  integer val = rs.getInteger(names[0]);
  
  if (null == val)
    return(null);

  if (val == 0)
    return(null);
        
     return val;
} 

You would have to do the reverse for the nullSafeSet (I have not included the code as I would probably get it wrong first time !!!!!)

I do not think that you would have to alter deepCopy as it appears to use nullSafeGet and hence a 0 would already been changed to a Null.

Obviously you would have to later your table mapping to use the newly created UserType.

Hope this helps,

Mike.

anthony · **Posted:** Sun Aug 22, 2004 3:49 am

Quote:

instead of making the foreign key null when the relationship does not exist, the programmer sets the foreign key field to '0' instead.

why? is he using int? if yes just change it to Integer

jcouball · **Joined:** Tue Aug 10, 2004 3:20 pm **Posts:** 11

anthony wrote:

Quote:

instead of making the foreign key null when the relationship does not exist, the programmer sets the foreign key field to '0' instead.

why? is he using int? if yes just change it to Integer

The legacy system is programmed that way... not my Hibernate classes. Something I don't have control over. Also that database was defined to not allow null in the foreign key field. I suspect that the relationship was required at first, but the requirement was relaxed and implemented in such a way so that the db schema didn't have to change.

Pretty lame, but that is what I have to live with.

jcouball · **Joined:** Tue Aug 10, 2004 3:20 pm **Posts:** 11

Mike Canham wrote:

Have you looked at defining your own UserType.

This UserType could map a null in your java class to a 0 when writing to the database and vice versa when reading.

Hey, Mike, thanks for the reply. It seems that what we are trying to is a little different. I am trying to map an association between entities where as the UserType is meant to map between java values and db values (correct me if I am wrong).

I think something like the following would be what I am looking for (note the null-value attribute):

Code:

        <many-to-one
            name="qaContact"
            class="com.couball.bugs.data.User"
            cascade="none"
            outer-join="auto"
            update="true"
            insert="true"
            column="qa_contact"
            not-null="true"
            null-value="0"           
        />

This means that this class has a many-to-one relationship to the User class as defined by the qa_contact field. The database field is not nullable because of not-null="true" attribute. However, a value of "0" acts as a null to indicate that there is no qaContact for this instance because of the null-value="0" attribute.

In the java object itself, the object reference probably be set to null. The mapping would be to map a null java object reference to '0' in foreign key field in the database and vice-versa.

Is something like that possible?

Sincerely,
James.

michael · **Posted:** Sun Aug 22, 2004 3:10 pm

No, this is not possible.

jcouball · **Joined:** Tue Aug 10, 2004 3:20 pm **Posts:** 11

michael wrote:

No, this is not possible.

Ah... hmmm.

Can you help me understand if something like that is even a good idea to suggest as an enhancement? I know that the implementation of the legacy application is broken, but I'd have an easier time hacking the change to hibernate myself or at least suggesting the enhancement and having someone who knows better do it.

If not, could you suggest a work around? The way I'd think to do it is to treat the foreign key field as a value and just have normal getters and setters rather than define a hibernate association.

Any and all help appreciated.

christian · **Posted:** Sun Aug 22, 2004 4:22 pm

The workaround is to fix your database.

jcouball · **Joined:** Tue Aug 10, 2004 3:20 pm **Posts:** 11

christian wrote:

The workaround is to fix your database.

You guys are fast... thanks for the reply... I'll see what I can do from that side.

gavin · **Posted:** Sun Aug 22, 2004 4:59 pm

Actually, I think it might be possible. A MAnyToOneType wraps around the identifier Type. So if you wrote a custom type that mapped null->zero and used it as the identifier type of the associated class, then the many-to-one would see that.

I havn't actually ever tried this, but don't immediately see a reason why it would not work. Obviously, HQL expressions like "foo.bar is null" would need to be rewritten as "foo.bar = 0", but that is a minor point.

Of course, "fix the database" is an even better solution.

Mike Canham · **Posted:** Sun Aug 22, 2004 6:47 pm

James,

I think Gavin is saying the same thing as me ......

What I was trying to do, was make the field a null in Java / Hibernate land, but remain as a zero in the database column .....

Gavin, has said that he thinks that the HQL wraps around the user type ....

So using the UserType as I suggested, might work .......

I also know where you are coming from, as I am trying to interact to a legacy database system, which still has a running application ..... so making changes to it, is not as easy as everyone wants to suggest ....

Good Luck,

Mike.

Balagan · **Posted:** Tue Apr 25, 2006 8:27 pm

gavin wrote:

Actually, I think it might be possible. A MAnyToOneType wraps around the identifier Type. So if you wrote a custom type that mapped null->zero and used it as the identifier type of the associated class, then the many-to-one would see that.

Just for the record: yep, a custom Type like Gavin suggests works pretty well.

Thanks!