I have the following problem: I have an "extension" table that's used to keep extra text information for various objects in the system. It looks like this (Oracle syntax):

create table extra_info (
id number,
seq number,
text varchar2(1000)
)

If I want to support extra text information for an object, I just add an extra_info_id in that table, which references the appropriate row(s) in the extra_info table. For example:

create table person (
id number,
name varchar2(50),
extra_info_id number
)

When I want to add extra info to a person I generate a unique id for the extra_info_id, and add multiple rows to extra_info (with sequential values in seq).

It's important to note that the extra_info table is used by multiple obejects in the system, so I can't use the person.id column as the key in extra_info, I need a separate key.

My problem - I'm unable to create a one-to-many mapping from the Person object to a list of ExtraInfo objects. A simple one-to-many relation joins based on person.id = extra_info.id instead of person.extra_info_id = extra_info.id. I tried looking at the reference docs and found <key property-ref="...">, but it doesn't seem to work for one-to-many relations.

I just want to get a List (or Set) of extra_info rows for Person (hopefully, ordered by the seq column).

Is there any way to do that in Hibernate?

Hibernate 3.1.2

If I understood your problem correctly, each row in extra_info is described by a combination of its id and its seq. So your primary key is actually id+seq, a "composite key."

If that is the case, you have to tell Hibernate:
http://www.hibernate.org/hib_docs/reference/en/html/mapping.html#mapping-declaration-compositeid

Instead of "composite-id," you can use a new class ExtraInfoPK with two attributes id and seq, and tell Hibernate the primary key of extra_info has the type ExtraInfoPK.

With such an extra class, your order criterion gets a bit more complex (in HQL):

Yuunli,
You're correct, the key is indeed composite, but unfortunately, that's irrelevant. I want to be able to fetch a list of all the extra text rows attached to a Person (and other objects), which mans I want to fetch based on a part of the key - the ID. If the ID would have been the same as the person ID, I could have used <one-to-many ...> from the Person object to a List of ExtraInfo objects, but I'm using (and have to use) a different key (which is not the primary key of the Person object). Basically, when calling Person.getExtraTextInfos() the following SQL should be generated (simplified):

While it's true the key is composite, it's irrelevant here, and this fetch would be very efficient, since it's using the primary-key prefix as a filtering criterion.
I've used this method with SQL (before Hibernate) many times.
Also, I'm not interested in HQL code to fetch these rows for a specific person (or extraInfoId value), I want to have this as a getter on the Person class. Fetching the rows using HQL is easy, but I need to have easy access to the information once I have a Person object in my hands.

Just for completeness, here's the (abbreviated) HBM for the extra info class:


Please advise.

Barakori,

I'm not sure if have all the values of the ExtraInfo PK in Person, so assuming that you have a person.extra_info_id and person.extra_info_indext, you can do the following:



Now, if you only have person.extra_info_id you may do the following, which I think will work:
a) Map extra_info.id with another name, say id2 (use insert="false" and update="false") in the ExtraInfo mapping file.
b) Use a many-to-many instead of one-to-many with property-ref="id2" in the Person mapping file.



Hopefully this will help

Jcervera,

Thanks for your reply. Unfortunately, I only have the extra_info_id in the person class - I know that all the rows with that extra_info_id belong to a single person (hence the 1:m relation). I've used many-to-many in some cases where one-to-many is needed as you decribed in your solution. Unfortunately, I can't use it here.

Barakori,

What is the issue with using the second solution I mention? I think that it should short out your problem.
The only inconvenience is that it will leave your ExtraInfo object with an extra property you are unlikely to use in your model ... maybe there are better solutions though.

Jcervera,

I'm sorry, I didn't see you actually have 2 suggestions in your reply. I'll check the second one now.

Jcervera,

I tried what you suggested. As written, it doesn't work since the generated SQL tried to fetch from a table called "extraInfos" (based on the set's name). I added a table attribute, now it tried to access extra_info.extra_info_id (because of the set/key/column/@name). I had to switch back to "id", now, even with the property-ref, it tried to generate a query with an invented column called "elt", I have no such column anywhere. It's probably automatically generated trying to compensate for the fact that the extra text table has 2 columns in its key. I don't want to fetch based on the key. Having the 'id2' column seemed like a good idea, but it doesn't work.