[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1348) Join w/ Foreign Key within a Single Object not functioing

[Rory Winston (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1348?page=comments#action_24796 ] 

Rory Winston commented on HHH-1348:
-----------------------------------

Yeah, thats not going to work. You will need to do it at a higher level than createJoin() (trying to look at this issue myself).

> Join w/ Foreign Key within a Single Object not functioing
> ---------------------------------------------------------
>
>          Key: HHH-1348
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1348
>      Project: Hibernate3
>         Type: Bug

>  Environment: Webshpere 5.0
>     Reporter: Michael Lambert

>
>
> I have a legacy database I need to join into using something other than the main objects primary key and it simply wont let me join on any field other than the primary key.
> No matter what I insert into "property-ref" it ALWAYS uses the objects primary key in the inner join:
> <?xml version="1.0"?>
> <!DOCTYPE hibernate-mapping PUBLIC 
> 	"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
> 	"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
> <hibernate-mapping package="com.dcx.ps.gpsis.ibsc.data.entities" default-lazy="false"> 
> 	<class 
> 		name="HPerson" 
> 		table="PIBPERSN">
> 		<cache usage="read-write" />
> 		<id name="id" column="I_PERSN_IBSC" type="long">
> 			<generator class="increment"/>
> 		</id>
> 		<timestamp name="lastUpdated" column="T_STMP_UPD" />
> 		<property name="lastUpdatedBy" type="string" column="I_USER_UPD" not-null="true" />
> 		<property name="tid" column="I_USER_LDAP" type="string" not-null="true" unique="true" />
> 		<property name="description" column="N_USER_LDAP" type="string" not-null="true"/>
> 		<set name="positions" inverse="true" cascade="save-update">
> 			<cache usage="read-write" />
> 			<key column="I_PERSN_IBSC" />
> 			<one-to-many class="HPosition" />
> 		</set>
> 		<join table="PUSRPRF" inverse="true">
> 			<key column="I_USER_LDAP" property-ref="tid" />
> 			<property name="firstName" type="string" column="N_FIRST" />
> 			<property name="lastName" type="string" column="N_LAST" />
> 		</join>
> 	</class>
> 	<query name="findPersonsByName">
> 		<![CDATA[
> 			from HPerson person where person.firstName like :firstname and person.lastName like :lastname
> 		]]>
> 	</query>
> 	<query name="findPersonsByFirstName">
> 		<![CDATA[
> 			from HPerson person where person.firstName like :firstname
> 		]]>
> 	</query>
> 	<query name="findPersonsByLastName">
> 		<![CDATA[
> 			from HPerson person where person.lastName like :lastname
> 		]]>
> 	</query>
> 	<query name="findPersonByTid">
> 		<![CDATA[
> 			from HPerson person where person.tid like :tid
> 		]]>
> 	</query>
> 	<query name="findPersons">
> 		<![CDATA[
> 			from HPerson
> 		]]>
> 	</query>
> </hibernate-mapping>
> Results in the following query and error when I execute any query (in this case findPersonByTid has been run):
> could not execute query:
> [select hperson0_.I_PERSN_IBSC as I1_4_, hperson0_.T_STMP_UPD as T2_4_, hperson0_.I_USER_UPD as I3_4_, hperson0_.I_USER_LDAP as I4_4_, hperson0_.N_USER_LDAP as N5_4_, hperson0_1_.N_FIRST as N2_5_, hperson0_1_.N_LAST as N3_5_ from T5279KN.PIBPERSN hperson0_ left outer join T5279KN.PUSRPRF hperson0_1_ on hperson0_.I_PERSN_IBSC=hperson0_1_.I_USER_LDAP where hperson0_.I_USER_LDAP like ?]
> COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0401N The data types of the operands for the operation "=" are not compatible. SQLSTATE=42818 
> Where the outter join SHOULD be hperson0_.I_USER_LDAP =hperson0_1_.I_USER_LDAP; I_PERSN_IBSC is the primary key. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira