Hello,

I have a problem where I need to read legacy data that is not well
designed.  The table in question does not have a unique key.  Getting a
unique row requires knowing values for a non-unique indexed column and
an unindexed column.

The main problem is the unindexed column allows nulls and when a value
is not appropriate for the row, null is used in that column.  Making
matters worse the indexed column consists of the concatenation of 3
"intelligent" codes.

The obvious Hibernate mapping looks like this

   <class name="ReferenceTable"
       table="REFERENCE_TABLE">

       <composite-id>
           <key-property name="indexedColumn"
               column="INDEXED_COLUMN"/>
           <key-property name="unindexedColumn"
               column="UNINDEXED_COLUMN"/>
       </composite-id>

       <property ... />

   </class>

There is another class that defines a many-to-one to this

   <class name="detailTable"
       table="DETAIL_TABLE">

       <id name="theKey" column="THE_KEY" />

       <property ... />

       <many-to-one name="referenceTable"
           cascade="none" not-found="ignore">

           <formula> <!-- 1st key-property in ReferenceTable -->
               PART_1 || PART_2 || PART_3
           </formula>

           <column name="foreignUnindexedColumn" /> <!-- 2nd -->

       </many-to-one>

   </class>

This works fine when foreignUnindexedColumn contains a non-null value.

However, Oracle does not allow NULL in comparison expressions such that

   SELECT * FROM DUAL WHERE NULL = NULL;

returns no rows.

So when foreignUnindexedColumn contains null, Oracle returns no rows.
This is because the hibernate generated SQL looks something like
this...

SELECT ... FROM REFERENCE_TABLE
WHERE INDEXED_COLUMN = ?
AND UNINDEXED_COLUMN = ?

What I need is the above SQL to be executed when foreignUnindexColumn
contains a non-null and for the following SQL to be used when
foreignUnindexedColumn contains NULL...

SELECT ... FROM REFERENCE_TABLE
WHERE INDEXED_COLUMN = ?
AND UNINDEXED_COLUMN IS NULL

It is my impression that Hibernate was designed for well formed data
structures with some concession here and there for messy legacy data.
Are there any hibernate mappings that could handle this situation.  It
would be nice if this could be solved entirely via mapping.  This is
for reading only, I'm not doing any updates to these tables through
hibernate.

Any advice is greatly appreciated.