Hi all,
I've been trying to find something similar in the docs, but maybe I'm just not looking in the right place...

I have a 2 tables:
person(ID, name, profid)
profession(ID, name)

All I want is to make a single class called WorkingPerson, that exposes:
the person's NAME
the profession NAME

The sql query I would expect is something like:


Can someone show me the correct map file for this?
The tables are:

Thanks!

Hibernate version: 3.1

From Hibernate v3 Reference:

Here's another example for ya too. Hope this helps!

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="com.foo.model.project">

<class name="Project" table="prolog.dbo.Projects" >

<id name="projectId" column="ProjectID" unsaved-value="null">
<generator class="increment"/>
</id>

<property name="address" column="Address"/>
<property name="architectCompanyId" column="Udf_Arch_CompanyID" />
<property name="bidDueDate" column="UserText1"/>
<property name="bidDueTime" column="UserText2"/>
<property name="bidType" column="TypeOfConstruction"/>
.
.
.
.
<join table="prolog.dbo.SDC_Project_Creation" fetch="join" optional="true">
<key column="ProjectID"/>
<property name="marketingDesc" column="MktgDescription"/>
<property name="marketingDescApproved" column="MktgDescIsApproved"/>
<property name="LEED" column="IsLEED"/>
<property name="LEEDCertified" column="IsLEEDCertified"/>
<property name="lienSentDate" column="LienSentDate"/>
<property name="lienTypeId" column="LienTypeID"/>
</join>

<join table="prolog.dbo.tbl_Pp_Projects" fetch="join" optional="true">
<key column="PjtID"/>
<property name="linkedToWebsite" column="IsLinkedToWebSite"/>
<property name="websiteUrl" column="WebSiteURL"/>
</join>

</class>

</hibernate-mapping>

I have been trying to do this exact same thing and I am seeing some very strange behavior. Using the previously posted example, this is how hibernate is handling it.

Hibernate is ignoring the the key column provided and is instead using the id property of the class. It is using the key column provided as the primary key for the table I am trying to join.

So, instead of generating this:
select person.name, profession.name from person inner join profession on person.profid = profession.id

it is generating this
select person.name, profession.name from person inner join profession on person.ID= profession.profid

This of course throws an invalid column exception because the profession table has no column called profid. Has anyone else seen this strange behavior?

hibernate 3.1

Swap round the Person and the Profession tables. Or experiment with the inverse="true|false" attribute.
The documentation under section 6.1.18. is pretty clear.

Not sure what you mean by "swap round the Person and Profession tables." If I swapped them around in the <join> element, I would be joining a table to itself.

As for all the other options discussed in section 5.1.18 in the documentation, no amount of tweeking has fixed this problem. It boils down to hibernate generating the wrong SQL for the join.

I mean swapping them with each other. Like this:
Instead of this mapping:

Use this mapping:


Looking closer at the document example, perhaps it's not so clear. The PERSON table has an ADDRESS_ID column which FKs to the ADDRESS table.

This is not the way I have it set up in some working code of mine. I have the table referenced by the <join> having a FK column to the table referenced by the <class>, which is why I suggested swapping them.

If I was to swap the classes around, wouldn't I see the same problem in reverse?

Also, it doesn't make much sense to me to map the profession class and then join on the person table when what I am really need is a person class with one piece of information coming from the profession table.

Why are you asking me this instead of trying it out?



I'm sorry your database design "doesn't make much sense".



Just maybe it is NOT hibernate "generating the wrong SQL".
Just maybe it is YOU who doesn't understand <join>.
Just maybe the correct thing to do is isolate what you don't understand and get it clarified by:

(a) double-checking you have read the docs correctly;

(b) double-checking you understand them as written;

(c) double-checking your own code for typos etc; and

(d) trying out suggested solutions and examine hibernate's behaviour in order to bring about an understanding on your part.

Just maybe you should read http://www.catb.org/~esr/faqs/smart-questions.html

Stewie, did you edit your last post or was it a moderator?

I edited it myself.
I took a walk round the block, came back and considered I should calm the post down a little. My initial reaction was one of having my patience tested. Maybe I could calm it down further still.

But for me, the bottom line is this:

I have had some trouble getting <join> to work in the past, but I am using it successfully now. I looked at how I am using it in a live app, and tried to pass that on to you.
I am using it successfully on this pattern:
The table referenced by the <join> has a FK column to the table referenced by the <class>. That is the column that goes in the <key>.

Your person / profession tables have the FK going in the other direction. That is why I suggested using inverse="true". That is why I suggested swapping the table mappings around - after all, it's all part of one single entity (WorkingPerson) so who cares which way the tables are mapped in the <class> and <join>.

I also note that the docs appear to have an example that's contrary to this. However there is more in the docs under the chapter on Inheritance Mapping (using <join> with <subclass>).

If this information, combined with your own reading of the docs, and your own experiments with mappings leaves you no further forward, then I'm sorry, but helping you is beyond my current abilities. I've given it a sincere shot and spent time and effort I'm not obliged to spend.

In order for A to help B, two conditions need to be present: A needs to offer help worth receiving, and B needs to be capable of being helped. Ultimately, what A offers can only ever be a catalyst for B helping himself.
I've helped many in the past and trained a few people on computer technologies amongst other subjects. I find myself being very patient up to the point that I get the idea that the student isn't actually trying. Maybe that perception was inaccurate in this instance. I apologise for that. But there it is. That's me.