Mapping multiple tables to one object, foreign composite key

quicksilver · **Posted:** Wed Jun 15, 2005 5:39 pm

I've poured over the Hibernate docs and I can't find a solution to this problem. I've tried several different solutions with the hibernate mappings but I can't figure it out.

Basically, I'm working with Brands that can have a different name depending on the current language (ie. the name is translated to whatever language we're currenlty using). Here is the Java class and the two SQL tables:

Code:

JAVA
public class Brand {
   private Integer id;  // unique ID per brand
   private Integer languageId;  // unique ID per language, always set to the current language
   private String enabled;  // flag, true if we display this to html
   private Integer ranking;  // ranking to display in html
   private String name;  // the translated name of this brand
// .... Getters and Setters
}



SQL
brand
{
  brand_id  // int, auto_increment, primary key
  ranking  // int
  enabled  // char(3)
}

brandtext
{
  brand_id  // int, foreign key
  language_id  // int, foreign key
  name  // varchar(255), a translation of this brand's name
}

Basically I want the following behavior:
1) when I load a Java Brand object, I want to load the static data from the brand table and then use the class's language_id property to load the translated name

2) when I delete a Java brand object, I want to delete the entry in the brand table, as well as all entires in the brandtext table that correspond to the brand_id

3) when saving a Brand, I want both tables' values to be updated

I know there must be a way for hibernate to map these properties, although I'm not sure about getting all of the desired behavior... If someone could point me in the right direction I'd be very appreciative. I'm fairly sure this is wrong, but here is the current mapping:

Code:

<hibernate-mapping package="foobar">
   <class name="Brand" table="brand">
      <id name="id" column="brand_id" type="int">
         <generator class="identity"/>
      </id>
      
      <property name="enabled" type="string"/>
      <property name="ranking" type="int"/>
      <join table="brandtext" inverse="true" >
         <key column="brand_id" on-delete="cascade"/>
         <property name="languageId" column="language_id" type="int"/>
         <property name="name" type="string"/>
      </join>       
   </class>
</hibernate-mapping>

* this is the last semi-working edition, I realize it doesn't handle the composit key correctly

max · **Posted:** Thu Jun 16, 2005 3:40 am

use a one-to-many from brand to brandtext since your #2 sees brandtext as an entity on its own.

do #1 by having a usertype that handles the lookup by current language id. http://blog.hibernate.org/cgi-bin/blosx ... 06/23#i18n

quicksilver · **Posted:** Fri Jun 17, 2005 1:08 pm

I think that will do the trick... I'd never heard of usertypes before...

I appreciate the link too, thanks a ton!

frub_de · **Joined:** Fri Apr 08, 2005 6:44 am **Posts:** 5

Hi all,

I have the same problem, but for me, it's important to do a search in the name field.
That's why I cannot use the solution ("i18n") above discussed.
(I need it because of a "product search by name")

So I tried to solve it in the other way, I declared "brand_text" as the main table
All queries query "brand_text", and the query parameters will always be brand_id and language_id, i try to do it via a composite key.
back to the example my Mapping should look like this:

Code:

   <class name="Brand" table="brandtext">
   <composite-id>
              <key-property name="brand_id" type="int" column="brand_id"/>
              <key-property name="language_id" type="int" column="language_id"/>
      </composite-id>      

      <property name="name" type="string"/>

      <join table="brandtext" inverse="true" >

         <key column="brand_id"/>

      <property name="enabled" type="string"/>
      <property name="ranking" type="int"/>
         
      </join>       
   </class> 

But this join doesn't work, I get the exception:
"Foreign key must have same number of columns as the referenced primary key"

Is there any solution for this, e.g. to specify the join only for the field "brand_id"?

The plain SQL works fine:

Code:

 select * from brandtext as a1 left join brand as a2 on a1.brand_id=a2.brand_id where language_id='1';

Currently, I have no Idea, how to set up the correct mapping in Hibernate.

frub_de · **Joined:** Fri Apr 08, 2005 6:44 am **Posts:** 5

I posted this some months ago, now I have the problem again.
Is there someone who has a solution for this problem?

Thanks in advance!

Best regards

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

Maybe you should try using Criteria queries. As far I am aware, most of the time Criteria returns inner-joins if you dont take enough care in formulating the Criterion.

The following mapping may help, edit as needed

Code:

<class name="Brand" table="brandtext"> 
   <composite-id> 
         <key-many-to-one name="brand" 
                          column="brand_id"
                          class="BRAND_CLASS"/> 
         <key-property name="language_id" 
                       type="int"
                       column="language_id"/> 
    </composite-id>       

    <property name="name" type="string"/> 
    ....
          
</class> 

and within the Java code having something like below. But as initially said this may/will return inner-joins. Taking lead on this idea you can search the forum for outer-join specific Criteria examples.

Code:

Criteria c = session.createCriteria( BrandText.class ).createAlias( "brand",  "brandAlias" );
c = c.add( Restrictions.eqProperty( "this.brand_id", "brandAlias.brand_id") );
c = c.add( Restrictions.eq( "this.language_id", new Integer( 1 ) ) );

HTH..