"Caveat Emptor": using <idbag> for Categoriz

Jim Stewart · **Posted:** Thu Dec 16, 2004 9:45 pm

Referring to the Caveat Emptor example application:

Could you please provide an example CategorizedItem class that allows use of <idbag> rather than <set> in Category.hbm.xml to store Category.categorizedItems() in a List rather than a Set? There's an example <idbag> configuration (commented out) in Category.hbm.xml, but as the comment states the packaged CategorizedItem class will not work with the <idbag> mapping.

The Category-CategorizedItem-Item relationship represents exactly what I need to do in my application: connect two tables using a mapping table where the mapping table contains additional fields.

I can't use a Set because I'm displaying this data on a Spring form and need a List to allow indexing and enable editing collections on a single form (per http://opensource.atlassian.com/projects/spring/browse/SPR-52).

I've tried numerous implementation variations on my own and haven't been able to get any of them working. Rather than cause confusion by listing all my attempted mappings and their respective errors, I was hoping someone could provide the definitive answer here.

Thanks.
Jim

Jim Stewart · **Posted:** Thu Dec 16, 2004 11:17 pm

I hate to be "one of those people" but I've already found the problem I was having. The example uses a surrogate key mapped with <collection-id> and requires an additional primary key to be added to the mapping table, which I hadn't done in my database.

I've managed to get my application working by adding a surrogate identity key to the mapping table.

This still leaves me with the following question:

How do you map a many-to-many collection using a mapping table with extra columns and access the mapping table objects as a list (<list>, or <bad>/<idbag> with order-by), without using a surrogate key in mapping table?

An example using the Category-CategorizedItem-Item model from Caveat Emptor would be appreciated. Or, more simply, using:

Code:

foo.id
foo.foo_col

bar.id
bar.bar_col

foo_bar.foo_id
foo_bar.bar_id
foo_bar.foo_bar_col

Using the above, I would want Foo.getFooBars() to return a List of FooBar objects so I could iterate over all the FooBar objects and access FooBar.getFooBarCol() for the mapping data and FooBar.getBar().getBarCol() for the Bar data. I also need bi-directional access (Bar.getFooBars()), but once I get it working one way I don't see any problem duplicating it from the Bar side.

Again, I can't use a Set for this because of my unique Spring requirement. A List is also a much more logical representation in my case since I need to order the list by one of the mapping columns.

Using <idbag> with a <collection-id> surrogate key for the mapping table addresses all of these concerns, except I don't really need or want a surrogate key in my table, and I know I'm going to run into a situation where I can't modify the database and still want to do all of this.

Thanks,
Jim

Jim Stewart · **Posted:** Fri Dec 17, 2004 2:54 pm

Ok, I have no idea what I was doing wrong before, but I got it working the way I want using an ordered <bag> and bidirectional mapping and everything. It's incredibly simple and works fine. I assume my original problem had something to do with not having a surrogate key in the mapping tables (I'd still like to see an example that works without the surrogate key).

Anyway, since I've seen this topic a bunch of times in the forums and I've never seen a complete answer, I'm providing mine here for others to examine.

The complete self-contained example can be downloaded from http://slac.com/jim/hibernate_test.zip. It includes everything you need to test out, just change your DB settings in resources/hibernate.properties, and examine the DB schema in db/schema.sql (remove the "type = InnoDB" part and "auto_increment" for non-MySQL). You'll probably have to set your classpath to run it outside of Eclipse (Eclipse project metadata is included as well).

This example demonstrates how to implement bi-directional many-to-many mapping with additional data in the mapping tables, and obtain the mapping objects as an ordered List. I implemented two different many-to-many mappings as well to make sure I didn't run across any of the duplicate column problems I was having earlier.

Here's the table schema:

Code:

create table person (
        person_id   int         not null    auto_increment,
        lastname    varchar(32) null,
        firstname   varchar(16) null,
        primary key (person_id)
);
create table phone (
        phone_id    int         not null    auto_increment,
        number      varchar(32) not null,
        type        varchar(16) null,
        primary key (phone_id)
);
create table person_phone_map (
        person_phone_map_id int     not null    auto_increment,
        person_id           int     not null,
        phone_id            int     not null,
        priority            tinyint not null,
        primary key (person_phone_map_id),
        foreign key (person_id) references person (person_id),
        foreign key (phone_id) references phone (phone_id)
);
create table location (
        location_id    int          not null    auto_increment,
        address1       varchar(128) not null,
        address2       varchar(128) null,
        primary key (location_id)
);
create table person_location_map (
        person_location_map_id int     not null    auto_increment,
        person_id              int     not null,
        location_id            int     not null,
        priority               tinyint not null,
        primary key (person_location_map_id),
        foreign key (person_id) references person (person_id),
        foreign key (location_id) references location (location_id)
);

Here's the hibernate mapping:

Code:

<hibernate-mapping package="test.domain">

    <class name="Person" table="person" lazy="false">
        <id name="id" type="long" column="person_id" unsaved-value="null">
            <generator class="native"/>
        </id>
        <property name="lastName" column="lastname" length="32"/>
        <property name="firstName" column="firstname" length="16"/>
        <bag name="personPhones" table="person_phone_map" order-by="priority asc">
            <key column="person_id"/>
            <composite-element class="PersonPhone">
                <parent name="person"/>
                <many-to-one name="phone" class="Phone" column="phone_id" not-null="true"/>
                <property name="priority" column="priority" not-null="true"/>
         </composite-element>
        </bag>
        <bag name="personLocations" table="person_location_map" order-by="priority asc">
            <key column="person_id"/>
            <composite-element class="PersonLocation">
                <parent name="person"/>
                <many-to-one name="location" class="Location" column="location_id" not-null="true"/>
                <property name="priority" column="priority" not-null="true"/>
         </composite-element>
        </bag>
    </class>

    <class name="Phone" table="phone" lazy="false">
        <id name="id" type="long" column="phone_id" unsaved-value="null">
            <generator class="native"/>
        </id>
        <property name="number" column="number" length="32" not-null="true"/>
        <property name="type" column="type" length="16" not-null="true"/>
        <bag name="personPhones" table="person_phone_map">
            <key column="phone_id"/>
            <composite-element class="PersonPhone">
                <parent name="phone"/>
                <many-to-one name="person" class="Person" column="person_id" not-null="true"/>
                <property name="priority" column="priority" not-null="true"/>
         </composite-element>
        </bag>
    </class>

    <class name="PersonPhone" table="person_phone_map" lazy="false">
        <id name="id" type="long" column="phone_phone_map_id" unsaved-value="null">
            <generator class="native"/>
        </id>
      <many-to-one name="person" column="person_id" insert="false" update="false" not-null="true"/>
      <many-to-one name="phone" column="phone_id" insert="false" update="false" not-null="true"/>
        <property name="priority" column="priority" not-null="true"/>
   </class>

    <class name="Location" table="location" lazy="false">
        <id name="id" type="long" column="location_id" unsaved-value="null">
            <generator class="native"/>
        </id>
        <property name="address1" column="address1" length="128" not-null="true"/>
        <property name="address2" column="address2" length="128" not-null="true"/>
        <bag name="personLocations" table="person_location_map">
            <key column="location_id"/>
            <composite-element class="PersonLocation">
                <parent name="location"/>
                <many-to-one name="person" class="Person" column="person_id" not-null="true"/>
                <property name="priority" column="priority" not-null="true"/>
         </composite-element>
        </bag>
    </class>

    <class name="PersonLocation" table="person_location_map" lazy="false">
        <id name="id" type="long" column="location_location_map_id" unsaved-value="null">
            <generator class="native"/>
        </id>
      <many-to-one name="person" column="person_id" insert="false" update="false" not-null="true"/>
      <many-to-one name="location" column="location_id" insert="false" update="false" not-null="true"/>
        <property name="priority" column="priority" not-null="true"/>
   </class>

</hibernate-mapping>

anthony · **Posted:** Fri Dec 17, 2004 3:08 pm

great you find the solution to your problem

and thank you to share your solution with the others, that's cool!