Hibernate Many-To-One Foreign Key Default 0

Question

I have a table where the the parent object has an optional many-to-one relationship. The problem is that the table is setup to default the fkey column to 0.

When selecting, using fetch="join", etc-- the default of 0 on the fkey is being used to try over and over to select from another table for the ID 0. Of course this doesn't exist, but how can I tell Hibernate to treat a value of 0 to be the same as NULL-- to not cycle through 20+ times in fetching a relationship which doesn't exist?

<many-to-one name="device" lazy="false" class="Device" not-null="true" access="field" cascade="none" not-found="ignore">
<column name="DEVICEID" default="0" not-null="false"/>

Answer 1

There are two ways of doing this, the way that can get ugly performance-wise and the way that is painful and awkward.

The potentially ugly way is done on the ToOne end. Using Hibernate Annotations it would be:

@Entity
public class Foo
{
    ...

    @ManyToOne
    @JoinColumn( name = "DEVICEID" )
    @NotFound( action = NotFoundAction.IGNORE )
    private Device device;

    ...
}

Unfortunately, this forces a preemptive database hit (no lazy loading) because device can be null, and if Hibernate created a lazy Device then "device == null" would never be true.

The other way involves creating a custom UserType that intercepts requests for the ID 0 and returns null for them, and then assigning that to the primary key of Device with @Type. This forces the 0 ~ null interpretation on everyone with a foreign key into Device.

Answer 2

I was able to fix this by creating an id-long type which extends the built in Long type, but if the id returned from SQL was 0, return null instead. This kept the allowance of default 0s in our DB while getting hibernate to stop doing lazy fetches.

public class IdentifierLongType extends LongType implements IdentifierType {

@Override
public Object get(ResultSet rs, String name) throws SQLException {
    long i = rs.getLong(name);
    if (i == 0) {
        return null;
    } else {
        return Long.valueOf(i);
    }
}

}

The reason for enforcing explicit default 0 is that Oracle handles indexing and null values oddly, suggesting better query performance with explicit values vs. 'where col is [not] null'