Hibernate mapping problem with null in legacy data (in Oracle)

[ducnbyu]

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.

 

[kafkasbug]

I am by no means an expert in SQL, but if you need to manipulate the
data during retrieval, and you want to try and solve this problem via
mapping files, then your best bet would be to use the "where" attribute
of the <class> element in the mapping file. This should allow you to
set conditions during the retrieval of the records in this table.

so...

<class name="ReferenceTable"
table="REFERENCE_TABLE" where="[SQL code]">

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

<property ... />

</class>

Dunno if that helps...

 

[ducnbyu]

I am by no means an expert in SQL, but if you need to manipulate the
data during retrieval, and you want to try and solve this problem via
mapping files, then your best bet would be to use the "where" attribute
of the <class> element in the mapping file. This should allow you to
set conditions during the retrieval of the records in this table.

so...

<class name="ReferenceTable"
table="REFERENCE_TABLE" where="[SQL code]">

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

<property ... />

</class>

Dunno if that helps...

It helps in a big way! The where= itself doesn't help but, as I'm
still learning Hibernate, you prompted me to take a close look at the
Class definition documentation and found the <subselect>.

So I did this and it works:

<class name="ReferenceTable"
table="REFERENCE_TABLE">
<subselect>
select indexed_column,
coalesce(unindexed_column, ' ') as unindexed_column,
field, ..., more_fields
from reference_table
</subselect>

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

<property ... />

</class>

The generated SQL puts the subselect into an in-line view:

SELECT reference0_.indexed_column AS ind1_9_0_,
reference0_.unindexed_column AS uni2_9_0_,
reference0_.field AS fie3_9_0_, ...,
reference0_.more_fields AS mor4_9_0_
FROM
(SELECT indexed_column,
COALESCE (unindexed_column, ' ') unindexed_column,
field, ..., more_fields
FROM refrence_table) reference0_
WHERE reference0_.indexed_column = ?
AND reference0_.unindexed_column = ?

Since unindexed_column is not indexed, peformance is not lost using it
in an expression. The Explain Plan is correct too. Thanks so much for
the lead you gave!