Treat '0',"-1" as Null

Question

I am working with a large, old database and i now try to access it with hibernate instead of SQL. One of the larger Problems with this is the use of "0" and "-1" (meaning "NULL") in foreignKeys without constraints.

I had much of the code generated, but i add the joins manually. Like this

@OneToMany(fetch = FetchType.EAGER)
@JoinColumn(nullable = true, name = "fkActivityId")
public List<Activity> getActivities() {
 return activities;
}

The problem is, that as soon as i have an object having fkActivityId = "-1" or "0" meaning NULL, there will be an Exception complaining that such an object can't be found.

Is there a way to make Hibernate behave as if it was NULL?

Answer 1

Sorry I know it does not answer the question, but really much better if you are able to.

update ExampleTable 
set fkActivityId = null 
where fkActivityId = 0 or fkActivityId = -1

Answer 2

Go through your database and update every fkActivityId to null if its value is -1 or 0. That's what null is for. Hibernate is doing its job correctly; it's the database that isn't set up properly.

Answer 3

Welbog's answer about fixing the data would be the preferred method.

If you really can't fix the data, you can write a custom value type to map the messy boolean DB field through to a java boolean value and back.

Answer 4

I have tried two things now.

I wrote a NullableIDType extends UserType, which would return null when the datase contained "0" or "-1". Like DanVinton suggested(upvote!). But there is a problem. I have to set the UserType to the id of the referenced model.

@Id 
@GeneratedValue
@Column(name = "pkID", unique = true, nullable = false, type = NullableIDType)
public Integer getPkId() {
    return this.pkId;
}

Which forces all references to this model to use "0" insted of "null". I forgot to say, that most of the time null is used when it should. So this would have worked if the database had consistently used "0" instead of "null".

Then there is @NotFound(action=NotFoundAction.IGNORE) w

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="fkProjectBereichId",nullable = true)
@NotFound(action=NotFoundAction.IGNORE)
public Projectbereich getProjektbereich() {
 return projektbereich;
}

At first this seemed like the bad but working solution, until i saved something, which had a null-reference. It was saved as "null" in the database and then caused the other code to crash, as it expected "0".

So in order to use hibernate joins i must use the same value for null everywhere. No way arround.

This is not really an satifsfying answer to my question. But it just is like that.