How to get rid of 2-col PK for lists

stijndewitt · **Posted:** Thu Dec 01, 2005 1:42 pm

Hi,

I am not happy with the schema generated for my object model. Everything works (no exceptions), but I would prefer a different kind of mapping.

I am trying to create a Person that can have multiple Addresses. The addresses should have a determined order. My List mapping looks like this:

Code:

<list name="addresses" table="uzn_personaddress" cascade="all" access="field">
    <key column="id"/>
    <index column="idx"/>
    <composite-element class="nl.uitzendnet.impl.AddressImpl">
        <property name="street" column="street" type="string"
               length="32" update="true" insert="true" />
      
        <property name="houseNr" column="houseNr" type="string"
               length="8" update="true" insert="true" />
      
        <property name="postalCode" column="postalCode" type="string"
               length="8" update="true" insert="true" />
                     
        <property name="city" column="city" type="string"
               length="32" update="true" insert="true" />
                     
        <property name="country" column="country" type="string"
               length="8" update="true" insert="true" />
    </composite-element>
</list>

The generated schema looks like this:

Code:

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | bigint(20)  |      | PRI | 0       |       |
| idx        | int(11)     |      | PRI | 0       |       |
| street     | varchar(32) | YES  |     | NULL    |       |
| houseNr    | varchar(8)  | YES  |     | NULL    |       |
| postalCode | varchar(8)  | YES  |     | NULL    |       |
| city       | varchar(32) | YES  |     | NULL    |       |
| country    | varchar(8)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

This will work perfect, but is inconvenient for me, because I need to have a single id column (because of environment restrictions). I would actually like the schema to look like this:

Code:

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | bigint(20)  |      | PRI | 0       |       |
| personId   | bigint(20)  |      | FK  | 0       |       |
| idx        | int(11)     |      |     |         |       |
| street     | varchar(32) | YES  |     | NULL    |       |
| houseNr    | varchar(8)  | YES  |     | NULL    |       |
| postalCode | varchar(8)  | YES  |     | NULL    |       |
| city       | varchar(32) | YES  |     | NULL    |       |
| country    | varchar(8)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Or something like that. How should I change my mapping document to get Hibernate to generate this schema?

I spend quitte some time reading the docs (especially the chapter on collections mapping) and I was very impressed by all the different mapping options. I decided on a List for my mapping because it seems most convenient for the addresses to be managed by the person object and because it offers a determined order for the addresses. If I am offtrack with using a list, feel free to set me straight :p

joshua · **Posted:** Thu Dec 01, 2005 3:05 pm

The primary key that you don't like is a requirement for composite elements in indexed collections like List and Map. This is covered in the docs for composite-element.

What you want is not a composite-element, but a one-to-many relationship between Person and Address with Set as the collection. To do this, you will need to declare Address as an entity, with it's own ID. However, if you want to preserve the *ordering* of the addresses for a given person and keep using List, the index column is necessary because List is an indexed collection.

Again, this is pretty well covered in the docos for mapping collections.

stijndewitt · **Posted:** Sun Dec 04, 2005 9:24 am

Thanks for your response. One more question, if I would make Address a separate entity, is it possible to use a separate table for each entity that contains addresses?

I have a Person and an Organization class, that both have addresses. I would like to have one Address class, but two tables, PersonAddress and OrganizationAddress to store the addresses.

This is not possible with ManyToOne mapping is it? I would like the primary key of an Addresss not to change if it moves to another position in the List, but I would like to use a composite-element. Is that possible?

joshua · **Posted:** Tue Dec 06, 2005 8:41 am

Composite element would allow this, as the table for the elements is specified in the parent. I'm not sure, but aren't composite element and entity mutually exclusive?