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

Discussion in 'Java' started by ducnbyu, Sep 7, 2006.

  1. ducnbyu

    ducnbyu Guest

    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.
     
    ducnbyu, Sep 7, 2006
    #1
    1. Advertising

  2. ducnbyu

    Guest

    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 wrote:
    > 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.
     
    , Sep 7, 2006
    #2
    1. Advertising

  3. ducnbyu

    ducnbyu Guest

    wrote:
    > 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!
     
    ducnbyu, Sep 8, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. willirl
    Replies:
    8
    Views:
    15,056
    Michael Borgwardt
    Jan 19, 2005
  2. Replies:
    7
    Views:
    4,668
    lars.egarots
    Jul 22, 2008
  3. garhone

    Hibernate mapping problem

    garhone, Aug 24, 2006, in forum: Java
    Replies:
    0
    Views:
    448
    garhone
    Aug 24, 2006
  4. Hole
    Replies:
    7
    Views:
    7,017
  5. Feyruz
    Replies:
    4
    Views:
    2,339
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page