Hibernate 4: Partial many-to-one association + where/filter discriminator

Question

I'm using Hibernate 4. I have this database with Category and CategoryItem tables:

CREATE  TABLE `test`.`Category` (
  `Id` INT NOT NULL AUTO_INCREMENT ,
  `Description` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`Id`) )

CREATE  TABLE `test`.`CategoryItem` (
  `IdCategory` INT NOT NULL ,
  `Id` VARCHAR(10) NOT NULL ,
  `Description` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`Id`, `IdCategory`) ,
  CONSTRAINT `fk_table1_Category1`
    FOREIGN KEY (`IdCategory` )
    REFERENCES `test`.`Category` (`Id` ))

This are used to store different categories or catalogs and its values. For example:

Category table:

1, 'Genders'
2, 'Marital status'
3, 'Countries'

CategoryItem table:

1, 'FEM', 'Female'
1, 'MAL', 'Male'
2, 'SIN', 'Single'
2, 'MAR', 'Married'
3, 'US', 'United States'
3, 'UK', 'England'

These values are used all over the schema, but with logical reference to CategoryItem(Id) only, for example:

CREATE  TABLE `test`.`Person` (
  `Id` INT NOT NULL ,
  `Name` VARCHAR(30) NULL ,
  --this fields are referencing CategoryItem(Id)
  `IdGender` VARCHAR(10) NOT NULL ,
  `IdMaritalStatus` VARCHAR(10) NOT NULL ,
  `IdCountryOrigen` VARCHAR(10) NOT NULL ,
  PRIMARY KEY (`Id`) )

Is it possible to map this tables with some technique in Hibernate? I'm not able to change this schema right now.

EDITED:

These are the classes and mappings so far:

public class Category implements Serializable {

    private int id;
    private String description;

    private Set<CategoryItem> items = new HashSet<CategoryItem>();

    public Category() {
    }

    //getters and setters omitted
}

public class CategoryItem implements Serializable {

    private CategoryItemId id;
    private Category category;
    private String description;

    public CategoryItem() {
    }
    //getters and setters omitted
}

public class CategoryItemId implements Serializable {

    private Category category;
    private String id;

    public CategoryItemId() {
    }
    //getters and setters omitted
}

public class Person implements Serializable {

    private int id;
    private String name;
    private CategoryItem gender;
    private CategoryItem maritalStatus;
    private CategoryItem countryOrigen;

    public PersonaNatural() {
    }
    //getters and setters omitted
}

  <class catalog="test" name="test.Category" table="Category">
    <id name="id" type="int">
      <column name="Id"/>
      <generator class="increment"/>
    </id>
    <property name="description" type="string">
      <column length="50" name="Description" not-null="true"/>
    </property>
    <set inverse="true" name="items">
      <key>
        <column name="IdCategory" not-null="true"/>
      </key>
      <one-to-many class="test.CategoryItem"/>
    </set>    
  </class>

  <class catalog="test" name="test.CategoryItem" table="CategoryItem">
    <composite-id class="test.CategoryItemId" name="id">
        <key-many-to-one name="category" class="test.Category">
            <column name="IdCategory" not-null="true"/>
        </key-many-to-one>
      <key-property name="id" type="string">
        <column name="Id"/>
      </key-property>
    </composite-id>
    <many-to-one name="category" class="test.Category" fetch="select" insert="false" update="false">
      <column name="IdCategory" not-null="true"/>
    </many-to-one>
    <property name="description" type="string">
      <column name="Description" length="100" not-null="true"/>
    </property>
  </class>

<class catalog="test" name="test.Person" table="Person">
    <id name="id" type="int">
      <column name="Id"/>
      <generator class="increment"/>
    </id>
    <property name="name" type="string">
      <column length="30" name="Name" not-null="false"/>
    </property>
    <many-to-one name="gender" class="test.CategoryItem" fetch="select">
        <column name="IdGender" not-null="true"/>
    </many-to-one>
    <many-to-one name="maritalStatus" class="test.CategoryItem" fetch="select">
        <column name="IdMaritalStatus" not-null="true"/>
    </many-to-one>
    <many-to-one name="countryOrigen" class="test.CategoryItem" fetch="select">
        <column name="IdCountryOrigen" not-null="true"/>
    </many-to-one>
  </class>

I'm trying with many-to-one but I'm getting this error: 'must have same number of columns as the referenced primary key'. This makes sense because I'm pretending to map just CategoryId(Id) on Person.hbm.xml.

I'm gonna need to specific a where or discriminator value to complete the left join condition, because CategoryItem(Id) alone is not unique.

Maybe this is not even possible with Hibernate, but any help I will appreciate. Thanks.

You should show us your classes and their mapping if you want us to tell you what's wrong with them. — JB Nizet, Oct 21 '12 at 08:46
Thanks @JBNizet, I've edited my question with the classes and mappings files. — vladiastudillo, Oct 22 '12 at 09:42
If the ID to the category item is sufficient to uniquely identify an item, then it should constitute the primary key of the table, and the ID of the entity. If it isn't sufficient, then you should have two columns in the Person table to reference the gender, two others to reference the country, and yet two others to reference the marital status. As is, your schema doesn't make much sense. I stronly advise to use single-column surrogate IDs. — JB Nizet, Oct 22 '12 at 09:50
I agree with you @JBNizet, I just have to code over this schema as it's a client requirement; I will expose to the team the alternative of adding a new column as surrogate Id on CategoryItem table. Thanks a lot for your help! — vladiastudillo, Oct 22 '12 at 10:10
As I said, if this schema makes sense, it means that the item IDs are unique, and can thus be used as Hibernate ID. — JB Nizet, Oct 22 '12 at 11:24

vladiastudillo · Accepted Answer · 2012-10-23T09:30:08.107

This is how I solve the Person mapping:

<class catalog="test" name="test.Person" table="Person">
    <id name="id" type="int">
      <column name="Id"/>
      <generator class="increment"/>
    </id>
    <property name="name" type="string">
      <column length="30" name="Name" not-null="false"/>
    </property>
    <many-to-one name="gender" class="test.CategoryItem" fetch="select">
        <formula>1</formula>
        <column name="IdGender" not-null="true"/>
    </many-to-one>
    <many-to-one name="maritalStatus" class="test.CategoryItem" fetch="select">
        <formula>2</formula>
        <column name="IdMaritalStatus" not-null="true"/>
    </many-to-one>
    <many-to-one name="countryOrigen" class="test.CategoryItem" fetch="select">
        <formula>3</formula>
        <column name="IdCountryOrigen" not-null="true"/>
    </many-to-one>
  </class>

Adding the <formula>(IdCategory)</formula> instead of <column> element causes hibernate matches the composite Id of CategoryItem and resolve correctly the left join.

Thanks.

Hibernate 4: Partial many-to-one association + where/filter discriminator

1 Answers1