-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 
Author Message
 Post subject: Join w/ Foreign Key within a Single Object
PostPosted: Tue Dec 20, 2005 8:56 pm 
Newbie

Joined: Tue Nov 25, 2003 4:31 pm
Posts: 12
Location: Minnesota
I'm trying to join vendor information into all product queries (read-only). For some reason, despite specifying the 'foreign-key' from our ITEM table, hibernate still generates code to join off of ITEM's primary key.

Am I missing something?

Hibernate version: 3.1

Mapping documents:
Code:
<hibernate-mapping package="com.XXXXX.model.product" default-access="field">
    <class name="Product" table="ITEM" mutable="false">
        <id name="id" column="ITEM_NO" type="long" length="22">
            <generator class="native"/>
        </id>
        <property name="idMfr" column="VEND_CAT_NUM" type="string"/>
        <property name="invoice" column="DESCRIPTION" type="string"/>
        <join table="VNDR_MSTR" inverse="true">
           <key column="VEND_ABBR" foreign-key="VENDOR_ABBR"/>
           <property name="manufacturer" column="VEND_NAME_SHORT"/>
        </join>
    </class>
</hibernate-mapping>


Name and version of the database you are using: Oracle 9i

The generated SQL (show_sql=true):
Code:
select
        product0_.ITEM_NO as ITEM1_3_0_,
        product0_.VEND_CAT_NUM as VEND2_3_0_,
        product0_.DESCRIPTION as DESCRIPT3_3_0_,
        product0_1_.VEND_NAME_SHORT as VEND2_4_0_
    from
        ITEM product0_,
        VNDR_MSTR product0_1_
    where
        product0_.ITEM_NO=product0_1_.VEND_ABBR(+)
        and product0_.ITEM_NO=?


Top
 Profile  
 
 Post subject: Try property-ref
PostPosted: Tue Dec 20, 2005 10:56 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
I think you want property-ref, not foreign-key.


Top
 Profile  
 
 Post subject: Re: Try property-ref
PostPosted: Tue Dec 20, 2005 11:03 pm 
Newbie

Joined: Tue Nov 25, 2003 4:31 pm
Posts: 12
Location: Minnesota
tenwit wrote:
I think you want property-ref, not foreign-key.


I tried that too, but traced the issue to a clause in hibernate source that was commented with "always join by pkey"-- which seems to contradict the allowed mapping configuration


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 20, 2005 11:07 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
That sounds bad. You may have to switch to a one-to-one mapping, which is actually a many-to-one with unique="true" and property-ref specified (because one-to-one is hardwired to use the PK, too).


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 20, 2005 11:16 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
the foreign-key attribute would not achieve what you are trying; it is only used for explicitly naming the FK constraint during schema generation.

the property-ref attribute is what you want. if that is not working for you then that would be a bug.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 21, 2005 4:35 pm 
Beginner
Beginner

Joined: Wed Dec 14, 2005 10:32 am
Posts: 20
Can you let me know how did you acheive this?

I have been trying to accomplish this...since a long time...

help is reallly appreciated.


Top
 Profile  
 
 Post subject: bug
PostPosted: Mon Jan 09, 2006 11:10 pm 
Newbie

Joined: Mon Jan 09, 2006 10:58 pm
Posts: 3
I think its a bug; I am running into the same problem. 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:

Code:
<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>



results in:

Quote:
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 inner join SHOULD be hperson0_.I_USER_LDAP =hperson0_1_.I_USER_LDAP. I_PERSN_IBSC is the primary key.

Is there a work around to this? If its a known problem has there been a patch jar released that I can deploy?

Thanks[/b]


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 10, 2006 2:12 pm 
Newbie

Joined: Mon Jan 09, 2006 10:58 pm
Posts: 3
It seems I cant use the <key /> tag to connect to another database table using another other than the primary key. The "property-ref" attributre doesnt seem to work under ANY scenario; I have tried creating a subclass to circumvent the join via foriegn key problem and also experience the same problem: I cannot connect into another table using anything other that the first tables primary key :(

Any suggestions? Is hibernate really that broken or am I doing something wrong?



<?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="name" 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>
<!-- this is not functioning as the key tag seems to be broken in hibernate 3.x :(

<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 HProfilePerson person where trim(person.firstName) like :firstname% and trim(person.lastName) like :lastname%
]]>
</query>
<query name="findPersonsByFirstName">
<![CDATA[
from HProfilePerson person where trim(person.firstName) like :firstname%
]]>
</query>
<query name="findPersonsByLastName">
<![CDATA[
from HProfilePerson person where trim(person.lastName) like :lastname%
]]>
</query>
-->
<query name="findPersonByTid">
<![CDATA[
from HPerson person where trim(person.tid) = :tid
]]>
</query>
<query name="findPersons">
<![CDATA[
from HPerson
]]>
</query>
</hibernate-mapping>


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 10, 2006 2:16 pm 
Newbie

Joined: Mon Jan 09, 2006 10:58 pm
Posts: 3
sorry for the not format my mapping file; here it is again:

Code:
<?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="name" 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>
      <!-- this is not functioning as the key tag seems to be broken in hibernate 3.x :(
         
         <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 trim(person.firstName) like :firstname% and trim(person.lastName) like :lastname%
      ]]>
   </query>
   <query name="findPersonsByFirstName">
      <![CDATA[
         from HPerson person where trim(person.firstName) like :firstname%
      ]]>
   </query>
   <query name="findPersonsByLastName">
      <![CDATA[
         from HPerson person where trim(person.lastName) like :lastname%
      ]]>
   </query>
   -->
   <query name="findPersonByTid">
      <![CDATA[
         from HPerson person where trim(person.tid) = :tid
      ]]>
   </query>
   <query name="findPersons">
      <![CDATA[
         from HPerson
      ]]>
   </query>
</hibernate-mapping>


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 10, 2006 4:47 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
According to JIRA issue HHH-551, this is a known problem. Apparently there's a TODO for it.

Where are the TODOs listed? I'd have thought that they'd be JIRA issues, but Gavin closed 551, rather than using it as a tracking issue. I'm sure I've raised an issue or two that I wouldn't have, if I'd looked at the TODOs first.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 01, 2006 2:41 pm 
Newbie

Joined: Fri Nov 11, 2005 12:34 pm
Posts: 18
I am curious

1. if there is a way to know what release this is planned. I got 3.1.2 and it isnt implemented.

2. another way to join from non PK column

Sincerely

MB


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 01, 2006 5:24 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
No, there's no way to find out when something is due, in an open source project. Not without directly asking, and that's likely to make enemies for you. After all, it's a labour of love for unpaid developers with other commitments.

I'd use a many-to-one unique="true" mapping and the delegation pattern to simulate a join via a non-PK column. Given TableA and TableB that you want to join into a single class PojoA, define a many-to-one unique="true" property-ref="foreign-key-column-in-A" mapping called something like "InternalLinkToB". Then delegate all the methods in PojoA to getInternalLinkToB().getXXX()/setXXX().


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 02, 2006 7:35 am 
Newbie

Joined: Fri Nov 11, 2005 12:34 pm
Posts: 18
thanks for reply and for detailed suggestion, i'll do it that way.

as for my unfriendly question, all I wanted to say is I wish I'd knew what features are mentioned in docs as possible, but are not implemented.

of course, to all people responsible for this project big thank you. great work.

mb


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.