Hibernate version:

Hibernate 2.1.6

Mapping documents:

Can't show

Code between sessionFactory.openSession() and session.close():

Can't show

Full stack trace of any exception that occurs:

Can't show

Name and version of the database you are using:

MS SQL Server 2000 with JSQLConnect JDBC driver

The generated SQL (show_sql=true):

Can't show

Debug level Hibernate log excerpt:

Dunno

Question:

I only got a questions that I'd like you guys to give your opinion on.

I got a many-to-many realtionship between two tables. The intermediate table primary key is a composite key of the primary keys from the two "entity" tables and another attribute (like line number).

Ex:

TableA
--------
A_PK -> Primary key

TableAB
--------
A_PK -> Composite id
B_PK -> Composite id
Another Field

TableB
--------
B_PK -> Primary key

How do I map this so it is part of the entity tables cascades, inserts, updates, deletes etc. I've read in the forum and it seems to be a lot of problem around composite ids(?)
Ideally I wouldn't have to take care of this intermediate table at all e.g. calling load(), delete(), update() on it explicitly.

Would you guys please help out?

Kind regards, Andreas Eriksson

You might want to check out all the FAQ questions. This one will help.
[url]
http://hibernate.bluemars.net/118.html#A11
[/url]

If you have the power to modify your design...use an auto generated key for the primary key of your middle table.

As in....

auto_key, long
first_table_key, long
second_table_key, long

Then you treat it like you would any other table. There really is no reason to make the key a composite of the first and second table keys...then append a unique value. Just use a generated key that is unrelated to the two other fields.

Lee

So, generally any composite id or element is a bad idea? Think I've read somewhere that a intermediate (relation) table is better mapped as an entity(?).

Giving it its own primary key and map it with like an entity would give me less "problem"?

By the way, havn't read enough about composite elements but will ASAP, will they take part of the parents SQL INSERT, UPDATE, DELETE, cascading etc.?

Thanks guys!

/Andreas

composite id are not bad idea but surrogate key a real good idea :))

there is no problem with composite element, prefer this solution to a many to many, also check idBag.
About cascading, just understand how it works...

Thanks Anthony!

I'll try out the composite element solution for my many-to-many relationships.

If anyone else have a comment, feel free to post me a reply on the topic!

Regards, Andreas

There is no value to a composite key....you have to deal with them when you have no control over the design...but otherwise, there is never a reason to have them.

As a design rule, all of my tables have a unique key that is auto generated. In oracle with a sequence, in sql server with an identity column.

Generated keys have no business purpose...which makes them "safe". You never show them to the users...thus they never want to "renumber" them. They are not vulnerable to business rule changes. They are small...search very fast...and are totally in control.

Not to mention that any and all database tools support the concept of generated keys, single field primary keys as the easiest and most straight forward thing to do.

Thus...since composite keys offer no value at all....there really is no reason to use them.

Again, unless you've inhereted a system and can't change the design.

So yes, even my "resolve many to many join" tables have a unique, autogenerated key. Makes life much simpler.

Keep in mind the principle that good design should simplify development.

Lee

Thanks Lee for your thoughts!

Well, the data modell we're working with allows very little change :-/ All entities use a GUID as primary key... and the intermediate (relational) table uses two foreign keys (GUIDs) as a composite primary key.

Maybe I can argue that we should use it's own GUID primary key instead?

Anyways, thanks for your reply!

Regards, Andreas

GUID's? Why on earth would you choose them?

But...I'd rather create a new guid, than combine two WITH an additional counter to make a unique composite key.

Lee

*lol* ...not my fault. No serious, there exist a decent explenation for the use of GUIDs. Just can't bring it up here on the Hibernate forum.

By the way, would I still, if I would get rid of the composite id, map the intermediate table as a composite element and not as an entity?

composite element, you'll surely have extra info in this table so go for composite element

Thanks again Anthony!

Regards, Andreas

Hmmm... got a problem with the composite element not loading its foreign keys, what's the problem?

Anthony, you would be of great help here!

Regards, Andreas

how do you want me to know?

Yeah, of course!

Well, I tried the composite element mapping example in the reference guide with Customer -> Order -> Product.
And it works well to add an Order and Product etc. as long as doing it in the right order.

Ex:



But(!), if I get the orderlines from Order.getOrderLines(), these objects is not populated with their primary key -> which are the foreign keys from table Order and Product.

How do I get the OrderLine composite element to be populated with its primary key?

Thanks!

Regards, Andreas