Hibernate version: 2.1.6

hi,

i've got a little 'problem' and i really hope there's a workaround for it ...

I've got a Class A with many-to-one relationships to Classes B,C and D. Relations are via 'simple'- and 'composite'- ids.
Everything works perfect on my test-database (a db2 on as400).
But on the pre-production db, there're exceptions during loading ...

My problem:
All relationships can be 'null', so the field in the db is 'null' (and that works perfectly).
But ... and i really don't know why it's solved like that, in (pre-)production-db there isn't a 'null-value' in the field ... instead of, there's a '0' :( And i'm absolutly not allowed to change this ...
So hibernate 'thinks' (and i would do the same), that there must be an entry in the associated table and throws an exception (net.sf.hibernate.UnresolvableObjectException), because there isn't an entry because '0' means 'null' in this special case ...

Is there a workaround to define '0' as 'null'?
Somethink like:


I really hope, that i only have overread something and that's not a problem ...

I've tried solving the problem with an Interceptor or liflecyle-methods, but that won't work ....

I hope somebody can help a little bit ...

thx!
curio

sorry for posting a little to fast ...

the problem is not really the one i descriped ... it's only a part of my problem ... so i will post again if i'm able to reduce the problem to a min. ..

gtx
curio

hi,

ok, after doing some tests, looking at the legacy db etc. ... i really need a way to define '0' (number) as 'null' because in the legacy db there's a '0' to indicate, that no entry on the 'one'-side is available ... :(

I need to solve this for update/insert-operations, too ...

If a Class A has a relationsship to Class B and i set "classA.setClassB(null)" in my database must appear a '0', not a null ... and i'm not allowed to change anything on the database, so setting a default-value on 'table-level' is not permitted ...

By the way ... if the relation is handled by string-fields, i need to define 'null' as Empty-String ...

Is there a possibility to do this? If there's no other way, perhaps i can patch hibernate? Where would be a good place to look at it?

thx!
curio

P.S.: This problem comes accross with my problem mentioned in this thread : http://forum.hibernate.org/viewtopic.php?t=935713

sorry for posting again in this thread, but i wasn't able to find a complete answer without debugging hibernate (and that would propably take to much time) ....

I realized setting '0' or ' ' for null with a UserType ... that wasn't really a problem, after realizing how easy it is to write a own Type ... (problem: at the moment i'm not able to use a CompositeUserType as composite-id-type)...

But writing my own UserType wasn't enough :(
Because if the reference from a ClassA to a ClassB is not set (null), hibernate won't use the CustomUserType on the 'one'-Side of the corresponding many-to-one relationship to determine the value which should be written to db ... and why should hibernate do this, i wouldn't either ...

So my question is, if i'm able to handle this case without replacing the 'many-to-one'-Type and patching hibernate?

thx!
curio

P.S.: promise, if no one answers, i won't reactivate this thread for a fifth-time ;) ... only once again to show the solution i hopefully have found ... perhaps usefull for someone getting the same problem ...

okay ... i was stupid and complitly blind ....

perhaps not the best way to do this ... but it works now perfectly ... just do
and it will
work perfectly ...

well ... hoping this is the end now (for you and me ;) ) ...

gtx
curio (who should make some holiday ... ;) )

Hi curio,

I just wanted you to know that I have tried your solution and that it works perfectly.

And I wanted to say "Thanks very much" for taking the time to post your question and the solution. I have been trying to solve this same problem for a while now and I had not discovered any good solution. I even read the "Hibernate in Action" book and didn't get a clue.

If you're ever near Chicago please drop me a line and I'll buy you dinner at Scoozi.

best regards,

Ted Stockwell
Technical Director
RPC Software
tstockwell@rpcsoftware.com

hi ted,

great that it helps you! It's good to know that someone else can use it ....
And no, what a pity, i'm not near chigaco ... :( but perhaps i will be some day ;)

gtx
curio (who had a lot of fun with those UserTypes ;) )

upps ... sorry ted ... i've meant 'Chicago' ;)

gtx
curio

Ah, wonderful -- I need this too. As I understand this method, I'll need to create a new Type for each of the classes that are hidden behind my could-be-0 foreign key, is that right?

Any chance of getting an example from you, Curio?

I haven't had time to read the whole thread, but I'd like to mention something: You guys know that '0' is not 'null', semantically?

A 'null' is what is called a 'missing information marker', something you use if you "don't know" a particular value at a particular time (hence, you can not fill in all values to make the tuple "statement" complete).

It's one of these concepts you can discuss for hours with relational experts (the relational data model doesn't have a 'null', it's binary logic with only true/false statements, there can not be any "missing information").

As soon as you are going to introduce '0' (a numeric value! not "missing information") as a missing information marker or anything similar, you are complicating the picture. Ternary logic in SQL (with the 'null') is already extremely complicated (if you think its trivial, I can point you to some interesting questions). Adding more translation steps and additional semantics only make the situation worse.

I bet that someone will curse you in 5 or 10 years for this decision, just as you are right now cursing about the idiotic composite keys (etc.) that have been used by someone else 10 years ago. Please, by all means, never compromise your logical data model for physical reasons.

Think about it. Fix your data and logical data model once and forever, its a better way.

@christian
you're complety right ...
but belive me if i had the choice, i would never think about it writing '0' into the database when i mean 'null' ... but im not allowed to change anything at the legacy db (which exists about 10 years now- and contains composite-keys with up to 5 key-properties ;) ) ...

The funny thing is, that '0' is never used as value ... only as a 'null'-replacement ...
well, what should i say ... i've had a lot of fun figuring out how that works which i hadn't if i'm allowed to change the db ;)

@aahoughton
like christian said ... if you have the choice ... avoid it ;)
If you don't have it :

I've written for example a "IntegerType" which does the following:
method: nullsafeGet
Read entry from Resultset. If it's '0' return 'null' ... else the new Integer(value).
method: nullsafeSet
If the value is 'null' then insert '0' ... else the int-value.

I have my own types for all SQL-Types where i need this behaviour ... in my legacy db 'null' is never used ... so a 'null-String' must be changed to 'empty string', too ...

If you need more help, i can post an example ... but it will last same days (long weekend ;) )

gtx
curio

what i've forgotten to say ...
the domain-modell (persistence-classes) uses 'null' ... i only need to map it for the DB ... i need all this, that i'm able to write "classA.setClassB(null)" and insert a '0' in Table of ClassA ...

not really perfect ... but if it's possible some day to change the db i musn't change the application ... only the UserTypes ...

gtx
curio

Hi Christian,

I agree with you about the semantic difference between 0 and null, I am already cursing the decision (not mine) to use 0's.

The situation is that I am using Hibernate with a legacy database that I don't have full control over. There are other applications within the enterprise that I cannot change that will keep writing 0's into the DB. Therefore I must find a way to live with the 0's.

I have read "Hibernate In Action". It was well worth my time. I discovered that I had misunderstanding about Hibermate and how to use it effectively. I also got a good education about persistence issues in general. However, the book didn't help me when it comes to dealing with the legacy database that I have to use. Maybe in the next edition ;-).

For the other poster that wanted to see some code, I have inserted the code for my identity type below. Understand that my id type is written specifically for my DB. I am using MySQL. All tables have an int ID column. This type will always write nulls to the DB (which is acceptable for this DB, some applications write nulls, some write 0's) but will translate 0's to nulls when reading.

Sorry about the formatting.

-------------------------

package com.rpc.core.utils.hibernate;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import net.sf.hibernate.type.IdentifierType;
import net.sf.hibernate.type.IntegerType;

/**
* This type is used for identifiers in the CORE.
* This type maps the integers in key columns to Java Strings and also maps
* zeros in key columns to nulls.
*
* @author ted stockwell
*/
public class LegacyIdentifierType
extends IntegerType
implements IdentifierType
{

private static final Integer ZERO = new Integer(0);

public Object get(ResultSet rs, String name) throws SQLException {
int i= rs.getInt(name);
if (i == 0)
return null;
return new Integer(i);
}

public void set(PreparedStatement st, Object value, int index)
throws SQLException {
if (value == null || ZERO.equals((Integer)value)) {
st.setNull(index, sqlType());
}
else
st.setInt( index, ( (Integer) value ).intValue() );
}

public String getName() { return "legacyIdentifier"; }


}

@ted
yes, that's a good idea ... extending the existing type saves a lot of work ...

Why did i use the "AbstractType"? ... I must have been 'addleheaded' ;)

gtx
curio (who will change it as soon as he is at work again)

Yeah, HiA doesn't help much with legacy databases (or, very weird special conditions). This has happened on purpose, we always try to show "best practices", and not even mention any strange and possibly broken setup. I have the opinion that even talking about bad practices gives people the wrong idea; thats also the reason why everyone uses simplifications (read, "trivial lies") sometimes to explain things.

Well, let's see what will be in the next edition, if it happens :)