I have two tables Item and Custom_Relation. The Custom_Relation table describes abstract relationships between Items.
CREATE TABLE ITEM (
ITEM_ID INTEGER NOT NULL,
... other values ...
);
CREATE TABLE CUSTOM_RELATION (
CUSTOM_RELATION_ID INTEGER NOT NULL,
ITEM_ID INTEGER NOT NULL,
RELATED_ITEM_ID INTEGER NOT NULL,
CUSTOM_RELATION_NAME VARCHAR2(50) NOT NULL,
RELATION_SEQUENCE INTEGER NOT NULL
);
The CUSTOM_RELATION_ID is just a unique ID for each record in the CUSTOM_RELATION table (which are omitted from the example below).
What it needs to do is best explained via an example of possible rows in the CUSTOM_RELATION table:
ITEM_ID, RELATED_ITEM_ID, CUSTOM_RELATION_NAME, SEQUENCE
1, 2, smells-like, 0
1, 3, smells-like, 1
1, 2, looks-like, 0
1, 4, looks-like, 1
This means that Item 1 smells-like the list of items containing Item 2 and Item 3, where Item 2 comes first, followed by Item 3.
Also that Item 1 looks-like the list of items containing Item 2 and Item 4,
where Item 2 comes first, followed by Item 4.
Note that the sequence number has meaning within the list of items that have the same CUSTOM_RELATION_NAME.
I want my classes to look something like this:
class Item
{
long itemId;
Map relationMap;
}
where the key of the relationMap is the CUSTOM_RELATION_NAME
and the value is an ordered list of related Items, e.g. for Item 1:
relationMap is:
[smells-like] => { 2, 3 }
[looks-like] => { 2, 4 }
Can this be done via Hibernate mappings (and if so, please help me by giving an example) or do I need to split the CUSTOM_RELATION table into multiple tables? Note that it's OK if the list of related items is really some kind of class that has a list of related items.
Thanks,
-Enno
|