How do I do a simple reference table lookup (join? formula?)

biguniverse · **Joined:** Fri Jul 29, 2005 2:11 pm **Posts:** 21

I'm using the standard relational database design of using columns with either a integer or a char code of some sort which links to a reference table that has descriptions of the code. Basically, I want to display the meaningful name/description to the user but use the code behind the scenes (again, standard relational stuff).

How do I map this simple join?

When I use the <join> style below, I get an ORA-01722: invalid number because Hibernate is trying to join PORT_ID [char(4)] with PGM_ID [number(4)]. So, meanwhile, I'm using formula which doesn't seem efficient.

Please help since I have a bunch of these sort of join/lookups. Thanks in advance.

Code:

<hibernate-mapping>
    <class name="DefaultPortfolio" table="DEFAULT_PORTFOLIO">
      <id name="portfolioId" column="PORT_ID">
         <generator class="assigned"/>
      </id>
      <property name="originatingSystemId" column="ORGG_SYS_ID"/>
      <property name="programTypeId" column="PGM_ID"/>
      <property name="programLongName"
              formula="(select DISTINCT pr.PGM_LONG_NME from PROGRAM_REF pr where pr.PGM_ID=PGM_ID)"/>

      <!-- Commented out since it results in a ORA-01722 error -->
      <join table="PROGRAM_REF" inverse="true">
         <key column="PGM_ID" />
         <property name="programLongName" column="PGM_LONG_NME" />
      </join>
       -->
   </class>
</hibernate-mapping>

zdenek.hrib · **Joined:** Thu Dec 16, 2004 7:50 am **Posts:** 11

i guess you should try to use <many-to-one> instead of <join>

it is described pretty well in docs, but the basic is like this:

<many-to-one name="programLongName" column="PGM_ID" class="com.whatever.ProgramNameType"/>

of course you will need a sort of com.whatever.ProgramNameType class with mapping also:

<!DOCTYPE hibernate-mapping PUBLIC '-//Hibernate/Hibernate Mapping DTD 2.0//EN' 'http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd'>
<hibernate-mapping package="com.whatever">
<class name="ProgramNameType" table="PROGRAM_REF">
<id name="PGM_ID" column="PGM_ID" type="long" unsaved-value="null">
<generator class="native"/>
</id>
<property name="programLongName" column="PGM_LONG_NME" type="string" length="25" not-null="true"/>


<set name="portfolios" inverse="true" lazy="true">
<key column="PGM_ID"/>
<one-to-many class="com.whatever.DefaultFortfolio"/>
</set>


</class>
</hibernate-mapping>

biguniverse · **Joined:** Fri Jul 29, 2005 2:11 pm **Posts:** 21

Thank you. I'll give that a try.

pksiv · **Posted:** Tue Aug 02, 2005 9:06 am

biguniverse wrote:

Thank you. I'll give that a try.

You should also look into implementing a level-2 cache for these objects which will limit the number of times Hibernate actually makes a database call to retrieve the data.

And for data that is static ( Country Codes etc...) you can set the mapping to mutable="false" which will also improve performance with these objects.