UserType join in Hibernate

Question

Is it possible to make hibernate do "the right thing" for some value of "right" in this situation?

from ClassA a, ClassB b
where a.prop = b.prop

The thing is that prop is a UserType with different representation in the joined tables. In table A it is represented as an integer and in table B it is represented as a char. So the eq test translates to see if 1 == 'a' more or less, which is false but the object represented by 1 or 'a' should is the same so they should compare true.

Answer 1

(1) Change data-type of columns which map to "prop" to be the same. This will require "Making DBA your friend" but will result in consitent "prop" UserType usage.

(2) Handle the type differences in equals() method

public boolean equals(Object x, Object y) throws HibernateException {
    boolean retValue = false;
    if (x == y) retValue = true;

    if (x!=null && y!=null){
        Character xChar = new Character(x);
        Character yChar = new Character(y);
        if (xChar.equals(ychar)){
            retValue = true;
        }
    }

    return retValue;
}

Answer 2

Do the join using a SQL expression. That way you can do the type conversion explicitly in the query itself.

Answer 3

I think you can do this using a <formula> tag on the relationship in your mapping file.

For example:

<many-to-one name="myClassB" class="ClassB">
  <formula>--Some SQL Expression that converts between ClassA.prop and ClassB.prop</formula>
</many-to-one>

I've used this to associate two tables where one used an integer, but associated it to a char field in another table. This might not be exactly what you're looking for, but maybe it will put you on the right track.