Hi!

Unfortunately I am compeled to work with an old database.
Three of the tables are:
Profile (id, subjectId)
Person (id, ...)
Organization (id, ...)

The problem is that subjectId form Profile table can be a person id or an organization id :(
The only logical relations can be one-on-one from person (respectively organization) to Profile.

I must make an outer join to display profiles and their organizations and I don't know how to do this with hsql.

If you can, please help. Thank you.

It sounds like what you're doing should work. Please post your mapping files.

Seems to me like you're missing a key piece of information. From the SQL alone it would be challenging to determine the "type" of the profile. I suspect that you determine the type by using what I like to call "Programmer MetaData" that's embeded not in the table structure, but in the query itself. For example, the "programmer" knows he wants an Organization Profile, so he writes the query such that the join starts with the Organization and links to the Profile. Same scenario for the Person.

But, what if you wanted to start at the Profile table alone? You'd have no way of knowing what type if Profile it is. The table is missing the metadata required to inform us of what kind of profile it is.

Hibernate is in this same boat. There's no way it can know what type of profile it is. The easiest and best way to solve this would be to introduce a discriminator column. You can then create a polymorphic Profile type: PersonProfile and OrganizationProfile. Then, you can have a one-to-one relationship from the Organization to an OrganizationProfile and a one-to-one relationship from the Person to the PersonProfile.

In the mapping, you can still use the same column (subjectID) for both mappings (though personally, I'd never do that, I like having foreign keys!). That way you can easily query between the two.

You are perfectly right! This is exactly what I would have done if I could modify the database. A discriminator column and use of subclass. Unfortunately I can't add a new column.

How about this, then. Try modelling it as a unidirectional one-to-many, with the many side being the Profile.

I often map one-to-one as many-to-one, as I find that one-to-many is easier to set up. There's really no penalty for doing so. I've seen several articles and postings about doing this and have had great success in strange cases where one-to-one just didn't fit right.

:)
I was shamed to do this, but I did it a few day ago. It didn't seemed right though. I put it there just to force hibernate to generate what I want.
This is what I was using when I started the topic :)

Though, could you give me a link to such an article? I'm interested to read more on the subject.

The material I've sen on one-to-one is mostly from the book "Hibernate in Action." There are several references in Chapter 6 to using (and not using) one-to-one mappings. They treat the one-to-one mapping as if it's an exotic mapping that is best handled by using many-to-one and component mappings. I recommend reading chapter 6 yourself, but here's a few quotes:


and

This is just the tip of the iceberg. Get a copy of the book and check it out. It will make you feel much better about using many-to-one associations in place of one-to-one. In fact, I rarely use one-to-one and most often favor components and many-to-one for my applications.