You're trying to do too much work.
First, Cayenne will handle PK's for you, but you have to use INTEGERs.
I just make it a habit to put an
id attribute in every table. If you want, you can add a UNIQUE
descriptor to your SQL before you create
your database to insure categories don't show up more than once.
Try this:
category
------------
attributes: id (INTEGER), name (VARCHAR)
you may want to make name UNIQUE
subcategory
----------------
attributes: id (INTEGER), categoryID (INTEGER), name (VARCHAR)
you may want to make (categoryID, name) UNIQUE
item
------
attributes: id (INTEGER), subcategoryID (INTEGER), etc.
Now, the interesting part. You have to set up relations from each thing
to what it should be related to.
In subcategory, set up a relationship called "category". Under target
choose category, then click on
Database Mapping. For the reverse relationship, category will map back
to all its subcategories, so I'd
put "subcategories" as the reverse relationship. Click "Add" then
choose categoryID as the source and
id as the target. The relationship will exist when those two are the
same.
Similarly, create a relationship from item to subcategory. My suggested
reverse relationship name is
"items".
Finally, create Item, Category, and Subcategory object entities and map
them to the corresponding tables.
You can create a Category or Subcategory in the normal way. If dc is a
DataContext:
Category cat = (Category) dc.createAndRegisterNewObject(Category.class);
cat.setName("Biology");
Subcategory subcat = (Subcategory)
dc.createAndRegisterNewObject(SubCategory.class);
subcat.setCategory(cat);
subcat.setName("Botany");
Item item = (Item) dc.createAndRegisterNewObject(Item.class);
item.setSubcategory(subcat);
Then, item.getSubcategory().getName() will return "Botany".
item.getSubcategory().getCategory().getName() will return "Biology".
The only hitch with this stuff is that you have to query to get Objects
of the Category and Subcategory type when you're creating new items.
For example, if you've previously created a Subcategory called
"Biochemistry" and you want to make your current item that subcategory,
you have to do something like
Subcategory s = (Subcategory) dc.performQuery(Subcategory.class,
ExpressionFactory.matchExp("name", "Biochemistry")).get(0);
item.setSubcategory(s);
This code will die if "Biochemistry" isn't in there. For classes with
unique names that I want to grab by name from the database, I write a
static method in the class that will do that, does the type checking,
returns null if there's no such object, etc.
HTH,
Todd
On Nov 25, 2004, at 5:05 AM, Alex Gunawan Wibowo wrote:
> Hello..
>
> Apology in advance if this is rather a very simple
> question for many of you..
>
> First, let me explain the database schema..
>
> there are 3 tables..
>
> subcategory, category, and item.
>
> category has PK "category name",
> subcategory has composite PK of : "category name" &
> "subcategory name". (as it is connected to category
> table)
> item has it's own PK, item ID.. but in addition, it
> stores a foreign key of "category name" & "subcategory
> name" (as it is connected to "subcategory" table).
>
> Hope that's clear enough.
> Now, the problem is that i have an entry from "item"
> table.
>
> when i call
> anItem.getToSubCategory().getSubCategory()
> -> getToSubCategory() supposed to return a SubCategory
> object.
> -> getSubCategory() supposed to return a String, i.e.
> one of the PK.
> both method are generated by CayenneModeller.
> it says "too many objects.." (CayenneRuntimeException
> is thrown). Can't exactly remember what the error
> says.. but it's along the line of saying that there
> are too many object in the subcategory table that has
> "OTHER" as the "subcategory" field. This is because
> "anItem" has as it's entry "OTHER" as the
> "subcategory" and "bla" as the "category".
>
> I have a little difficulty in understanding the
> problem here. I know that there are a lot of of entry
> in the
> table "subcategory" that has "OTHER" as the field
> entry..(since it's a composite PK anyway).
>
> So can anyone tell me how i can retrieve the foreign
> key "subcategory" for "anItem" object above??
> The way I mentioned above works in Hibernate.. and I
> don't know why it does not work in Cayenne..
> What amuses me more is that the call above seems to
> make a connection to the database.
> (In Hibernate, "anItem" object above would have
> "SubCategory" object as one of it's field.. so the
> method above works perfectly ok).
>
>
> Basically, what I want is the "subcategory" field (of
> type "String") from the "anItem" object. I don't want
> the "SubCategory" object that I can get easily using
> anItem.getToSubCategory()
>
>
> Thanks a lot for the help!
>
> Best regards,
>
>
> Alex.
>
>
>
> __________________________________
> Do you Yahoo!?
> The all-new My Yahoo! - Get yours free!
> http://my.yahoo.com
>
>
This archive was generated by hypermail 2.0.0 : Thu Nov 25 2004 - 08:12:05 EST