Self Reference w/ non-PK, non-FK using a single Table

marchall · **Joined:** Thu Nov 04, 2004 8:20 pm **Posts:** 2

Hibernate version:
2.1.6

Hello all,

I've been trying to get a specific mapping to work for a day or two now. This is the first time I've see, or tried to do a mapping like this. I've searched all over, read the hibernate mapping examples, and docs. I'm not that new to hibernate, but at this point I think I'm just confused, and doing something fundamentally wrong.

I have a data table:

Code:

CREATE TABLE public.at_item
(
  at_item_id serial NOT NULL,
  at_item_name varchar(50),
  at_item_simil_key varchar(3),
  CONSTRAINT at_item_pk PRIMARY KEY (at_item_id)
) WITHOUT OIDS;

and domain object:

Code:

public class AtItem extends BaseObject {
   private long id = -1;
   private String name;
   private Set similarItems;
.
.
}

I would like to have an AtItem object reference similar AItems via a Set mapping using at_item_simil_key as a key. Here's the XDoclet meta-tags:

Code:

 * @hibernate.set name="similarItems" lazy="true"
    * @hibernate.collection-key column="at_item_simil_key" 
    * @hibernate.collection-one-to-many class="com.domain.AtItem"

AtItems.getSimilarItems returns nothing. I do not get any errors. I activated "show_sql" and the query printed works if I manually run the query and plugin a value for "at_item_simil_key".

I hope this is clear.

So, can somebody tell me is this is just fundamentally wrong, or if a relationship like this is possible but I'm just going about it the wrong way.

Thanks,
Nick.

emmanuel · **Posted:** Sat Nov 06, 2004 8:16 am

id is a seriala nd simil_key is a varchar

David Harkness · **Posted:** Sun Nov 07, 2004 4:10 am

From your description, it seems that you'd like to have each AtItem maintain the same set of objects: all AtItems with the same simil_key. Is this correct?

That's kind of a strange way to set up a relation. For that I would provide a DAO method "getAllSimilarItems(key)" that returns a collection of AtItems with the matching simil_key using a normal query.

From Hibernate's point of view, if you were to map it as you'd like, then loading two similar AtItems should result with both of their similarItems references pointing to the same Set instance. I doubt Hibernate would like that.

If you are really set on a relationship rather than a query, you could create another table and mapping for SimilarAtItemSet that has just the simil_key (in the table) and a one-to-many with AtItems using simil_key (though I'd recommend using a long ID as PK/FK as you did for AtItem). Then make a many-to-one back to SimilarAtItemSet. Thus you are modeling the Set as a domain object.

To access similar items you would use

Code:

Set similars = atItem.getSimilarSet().getSimilarItems();

emmanuel · **Posted:** Sun Nov 07, 2004 4:09 pm

David Harkness wrote:

From your description, it seems that you'd like to have each AtItem maintain the same set of objects: all AtItems with the same simil_key. Is this correct?

No, Your PK and FK are *structurally* different, this sound weird.
To proper help you have to give us the hbm.xml files. That's our native speaking language ;-)

David Harkness · **Posted:** Sun Nov 07, 2004 4:28 pm

emmanuel wrote:

No, Your PK and FK are *structurally* different, this sound weird.

That was my point. From Nick's description it seems he wants at_item_simil_key to be both PK and FK: PK in the sense that each unique value is the PK of a logical entity -- the set of similar AtItems -- and FK in that each AtItem with the same at_item_simil_key belongs to that logical set.

Thus the solution I proposed: model the set of similar AtItems as a full entity in a separate table with its own PK. Then switch at_item_simil_key to be the FK to that entity:

Code:

CREATE TABLE public.at_item
(
  at_item_id serial NOT NULL,
  at_item_name varchar(50),
  at_item_simil_set_id number(10),  -- whatever type is appropriate for your DB
  CONSTRAINT at_item_pk PRIMARY KEY (at_item_id)
) WITHOUT OIDS;

CREATE TABLE public.at_item_simil_set
(
  at_item_simil_set_id serial NOT NULL,
  key varchar(3) NOT NULL,
  CONSTRAINT at_item_simil_set_pk PRIMARY KEY (at_item_simil_set_id)
) WITHOUT OIDS;

marchall · **Joined:** Thu Nov 04, 2004 8:20 pm **Posts:** 2

First of all, thanks to both Emmanuel and David for their attention to this.

David, I did consider both of your proposals; 1) providing a DAO method, or 2) Mapping the relation as a separate entity. I was just hoping I could find a way to do the mapping without using an extra entity, or a DAO method, because at this point I think it's an appropriate method/relation for the object domain.

Emmanuel, if you're still interested on Monday, I will provide the hbm file, but it is xdoclet generated, and I thought attributes I provided would indicate how I have defined the relationship.

Until then I'll try to clarify what simil_key is. It is my intent that simil_key be different from the PK. Basically, it's a "legacy" field, that allows items to be grouped. It's read only, and will never be changed by my application. It's basically a item-category relationship, but without the obvious need for a separate entity object for category. Further, an item belongs to only one category.

All these reasons, lead me to think that maybe I could get away with mapping this relationship without using an extra entity and table, or using a DAO layer query.

I'm still learning what I can/can't or should/shouldn't do with Hibernate. I'm curious whether my approach is a can't or a shouldn't.

Thanks.

emmanuel · **Posted:** Mon Nov 08, 2004 7:38 pm

Ok guys I was quite off scope.I get it now.
I'd probably use a DAO to reach all items in a particular category if I had to.