Getting data from two tables into a single class

drekka · **Joined:** Thu Mar 30, 2006 9:21 pm **Posts:** 11

Hi all,
I think this has been discussed but Im still finding it confusing. (Newbie ;-)) I'm trying to map data from an account table and an assicated country table where the account table has a foreign key called country_id. I want to place all the data in the same pojo. The manula suggests using a join in this situation. However the join refuses to see the countryId field on the account and generates sql which looks at the account_id instead.

Reading this forum it seems to be a non-implemented feature which has crept into the distribution. I.e. it's in all the doco but the code has not been written and is listed in the source as TODO (although I cannot find the place in the source at the moment where this apparently is documented).

I'm wondering if anyone has an update or can provide me with a workaround. I beleive it can be done with a many-to-one but I'm just learning and don't understand how to do it as the many-to-one doco seems to indicate you need two classes.

Setup is as per below.

Thanks
Derek

Hibernate version: 3.1

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="com.lp.commissions.beans.Account" table="account" mutable="false">
      <cache usage="read-only" />
      <id name="accountId" column="account_id">
         <generator class="native" />
      </id>
      <property name="name" type="string" column="account_name" />
      <property name="countryId" access="field" type="integer" column="country_id" />
      <!--  Join to get country and office information -->
      <join table="country">
         <key column="country_id" property-ref="countryId" />
         <property name="officeId" column="office_id" />
         <property name="countryName" column="name" />
      </join>
   </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close(): Not relevant

Full stack trace of any exception that occurs: No Exception occurs

Name and version of the database you are using: Postgres 7.3

The generated SQL (show_sql=true):
select this_.account_id as account1_0_0_,
this_.account_name as account2_0_0_,
this_.country_id as country3_0_0_,
this_1_.office_id as office2_1_0_,
this_1_.name as name1_0_
from account this_
inner join country this_1_ on this_.account_id=this_1_.country_id
where this_.account_id = ? order by this_.account_id asc

Debug level Hibernate log excerpt: none relevant.

axismundi · **Joined:** Mon Aug 22, 2005 5:45 am **Posts:** 146

why do you use property-ref="countryId" ?
have you tried to remove this? column attribute should be sufficient.

drekka · **Joined:** Thu Mar 30, 2006 9:21 pm **Posts:** 11

Hi, My undestanding of property-ref is that you use it to specify that the foreign key field in the main table is not the primary key. I.e. in my example above, I am using it to refer to the countryId field in the account table so that the generated sql should read:

from account this_
inner join country this_1_ on this_.coountry_id=this_1_.country_id

However hibernate is ignoring this and instead trying to join the primary key account_Id to the country table which is completely wrong.

The only thing I can think of that would make this work is to flip the tables around so that country is my main table and account becomes a lookup. Then joining to the primary key would work. But this of course falls down the moment you need to join more than one table. So if I had a start schema with account at the center and a number of lookup tables around it I would be screwed.

ciao
Derek

drekka · **Joined:** Thu Mar 30, 2006 9:21 pm **Posts:** 11

Ok, I've searched the source code and come up with a hack to fix the problem. But being a newbie to Hibernate and not knowing much about how it works, I would warn anyone thinking of applying it to be very careful ebcause I may have broken something else. I'm only posting it here so people can take a look.

This is a hack to file org.hibernate.persister.entity.AbstractEntityPersister

I hold no responsibility for it's use ;-)

Code:

   /**
    * This version hacked to correctly implement the foreign key columns on the parent table
    * in the situation where the &lt;join&gt; tag is being used to join several lookup
    * tables to a fact table in a star schema which is loaded into a single pojo.
    * Distribution version was hard wired to use the primary key to join to the fact tables
    * which makes no sense as fact tabels are wired to foreign keys.
    * Hack by Derek Clarkson, 4/5/2006.
    * @param name The name of the main table.
    * @param innerJoin If true, an inner join is performed.
    * @param includeSubclasses If true ???
    * @return a JoinFragement containing the details of the join.
    */
   protected JoinFragment createJoin(String name, boolean innerJoin, boolean includeSubclasses) {
      //**** DHC Hack Start
      this.log.debug("Creating join " + name);
      //final String[] idCols = StringHelper.qualify( name, getIdentifierColumnNames() ); //all joins join to the pk of the driving table
      //**** DHC Hack End
      final JoinFragment join = getFactory().getDialect().createOuterJoinFragment();
      final int tableSpan = getSubclassTableSpan();
      for ( int j = 1; j < tableSpan; j++ ) { //notice that we skip the first table; it is the driving table!
         final boolean joinIsIncluded = isClassOrSuperclassTable( j ) ||
               ( includeSubclasses && !isSubclassTableSequentialSelect( j ) && !isSubclassTableLazy( j ) );
         if ( joinIsIncluded ) {
            join.addJoin( getSubclassTableName( j ),
                  generateTableAlias( name, j ),
                  //**** DHC Hack Start
                  StringHelper.qualify( name, getSubclassPropertyColumnNames(j)),
                  //idCols,
                  //**** DHC Hack End
                  getSubclassTableKeyColumns( j ),
                  innerJoin && isClassOrSuperclassTable( j ) && !isInverseTable( j ) && !isNullableTable( j ) ?
                  JoinFragment.INNER_JOIN : //we can inner join to superclass tables (the row MUST be there)
                  JoinFragment.LEFT_OUTER_JOIN //we can never inner join to subclass tables
               );
         }
      }
      return join;
   }