Hi all. Being a beginner I've ran into a rather delicate situation I simply can't set straight. Using PostgreSQL 7.3 tables looking like this:
<code>
-- Nested categories, one parent <-> many children

CREATE TABLE Categories (
id SERIAL PRIMARY KEY,
name VARCHAR (32) NOT NULL,
parent INTEGER
);
ALTER TABLE ADD CONSTRAINT Category_p_FK FOREIGN KEY (parent) REFERENCES Category (id);
</code>

In the Java class I'd like to be able to use it both-ways:
<code>
public Category getParent() { return this.parent; }
public Set getChildren() { return this.children; }
</code>

For this, the Category.hbm.xml looks like:
<code>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="se.barnvagnsfabriken.shop.beans.Category" table="Categories">
<id name="id" column="id">
<generator class="native"/>
</id>
<property name="name" column="name" not-null="true" />

<!-- the parent category, or null -->
<one-to-many name="parent" class="se.barnvagnsfabriken.shop.beans.Category" />

<!-- the child categories -->
<set name="children" lazy="true">
<key column="parent" />
<one-to-many class="se.barnvagnsfabriken.shop.beans.Category" />
</set>
</code>

Somehow, I make no sense from the reference manual getting this right. To add up, the categories are filled with products (which may reside anywhere and at multiple places in the "category tree"):

<code>
CREATE TABLE Product (
id SERIAL PRIMARY KEY,
key VARCHAR (32) NOT NULL,
name VARCHAR(32) NOT NULL,
description TEXT,
price NUMBER(12, 2) NOT NULL,
vat NUMBER(12 ,2) NOT NULL
);

CREATE TABLE CPM (
id SERIAL PRIMARY KEY,
category INTEGER NOT NULL,
product INTEGER NOT NULL
);
ALTER TABLE CPM ADD CONSTRAINT CPM_c_FK FOREIGN KEY (category) REFERENCES Categories (id);
ALTER TABLE CPM ADD CONSTRAINT CPM_p_FK FOREIGN KEY (product) REFERENCES Products (id);
ALTER TABLE CPM ADD CONSTRAINT CPM_Unique UNIQUE (category, product);
</code>

In java, I'd like to skip the in-between-mapping-object, and have
<code>
In Category: public Set getProducts() { return this.products; }
In Product: public Set getCategories() { return this.categories; }
</code>

Any help would be greatly appreciated.

Given the table definition as-is:


You would only be able to handle this using the <idbag> collection mapper:


The reason is the PK constraint definition on CPM. Without that, you could map this in Hibernate using any type of <many-to-many>:

Should be

many children to 1 parent


CPM must be the table of the many-to-many relationship between Category and Product

Have a look at the last example of the 5.12 section of hib ref guide

Oh yeah, for the category hierarchy, there's even a section on the desing pattern Wiki describing how to set this exact mapping up:
http://www.hibernate.org/86.html

Thanks to you all. I'll remove the PRIMARY KEY constraint as well as the entire id column in CPM. The unique constraint is enough, of course.