-->
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.  [ 15 posts ] 
Author Message
 Post subject: Many to one mapping using non primary key
PostPosted: Thu Oct 09, 2008 5:37 pm 
Beginner
Beginner

Joined: Thu Apr 19, 2007 9:52 am
Posts: 27
Hello Everyone,

I have a situation. In my project the data model is controlled by business guy and he has created a relationship between two tables where the relationship is with a unique column on other table rather than primary key. Let me create a sample table

Table - CATEGORY
Column
ID - PK
Version -
CatergoryID - Combination of Version and this column is is Unique
Category_Desc


Table - PCM
Coulmn
ID - PK
Catergory_ID - FK to Category ID in Category Table
Version - FK to Version on Category Table
.....
.....

Now when I use hibernate to use many-to-one relationship it does not work. It does not load child Category.

I you suggest it is a bad database design. I agree with you but I can't help, its political.

So can someone suggest me how to use this scenario with hibernate.

Regards,
Mahen


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 09, 2008 7:18 pm 
Expert
Expert

Joined: Mon Nov 26, 2007 2:29 pm
Posts: 443
use the property-ref attribute in order to specify an FK relationship by a column other than the PK in the target table.

_________________
Gonzalo Díaz


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 10, 2008 10:40 am 
Beginner
Beginner

Joined: Thu Apr 19, 2007 9:52 am
Posts: 27
Got it thanks. But how do you achieve same when there are multiple columns involved like in the situation I mentioned. It uses both Version and CatergoryID to map.

Mahen


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 10, 2008 11:10 am 
Beginner
Beginner

Joined: Thu Apr 19, 2007 9:52 am
Posts: 27
Got it using <properties> tag

Mahen


Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 25, 2008 5:41 pm 
Newbie

Joined: Tue Nov 25, 2008 5:23 pm
Posts: 4
*double post*


Last edited by DeltaHuey on Tue Nov 25, 2008 5:44 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 25, 2008 5:42 pm 
Newbie

Joined: Tue Nov 25, 2008 5:23 pm
Posts: 4
Can you post your solution? I have the same problem.

Logically I want to do this:

Code:
<many-to-one class="ShippingOrder" name="ShippingOrder">
    <column name="PICK#" property-ref="PickListNumber" />
    <column name="LINE#" property-ref="LineItemNumber" />
    <column name="RELEASE" property-ref="ReleaseNumber" />
</many-to-one>


Unfortunately property-ref is only allowed in the many-to-one tag, not the column tag.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 25, 2008 10:37 pm 
Beginner
Beginner

Joined: Thu Apr 19, 2007 9:52 am
Posts: 27
You need to do this

Code:
<many-to-one class="ShippingOrder" name="ShippingOrder" property-ref="somenaturalid">
    <column name="PICK#" property-ref="PickListNumber" />
    <column name="LINE#" property-ref="LineItemNumber" />
    <column name="RELEASE" property-ref="ReleaseNumber" />
</many-to-one>

On other hbm you need to define something like this

<properties name="somenaturalid" unique="true" insert="true" update="true">
           <property name="pick" type="big_decimal">
               <column name="PICK" not-null="true" />
           </property>       
           <property name="line" type="big_decimal">
               <column name="LINE" not-null="true" />
           </property>
           <property name="release" type="big_decimal">
               <column name="RELEASE" not-null="true" />
           </property>         
</properties>


You don't need to define your properties twice in second hbm.
Hopefully this helps let me know if that works for you

_________________
Regards,
Mahen
http://www.discoverabout.net
http://funnfacts.discoverabout.net


Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 25, 2008 11:51 pm 
Newbie

Joined: Tue Nov 25, 2008 5:23 pm
Posts: 4
My apologies, I am using NHibernate. I was in the NHibernate Users forum, did a search to see if my question had already been asked and didn't notice this result was in Hibernate Users. I will have to remember to filter results next time!

I just read Hibernate documentation to see what the properties tag did, and your mapping solution is perfect for what I want. That said, it isn't provided (that I can see) in NHibernate.

Thanks for quick response!


Top
 Profile  
 
 Post subject: Re:
PostPosted: Thu May 28, 2009 1:03 pm 
Regular
Regular

Joined: Tue Feb 19, 2008 6:05 pm
Posts: 82
I had a similar problem when mapping from UserCountry table with userId (PK), countryId (FK) to Address table (one-to-many) userId (FK), countryId (FK) to get a unique address for a given UserCountry record.

In User mapping, I would get a unique address as

Code:
<many-to-one name="uniqueAddress" class="database.Address" property-ref="userIdCountryId">
         <column name="user_id"></column>   <!--  name of the foreign key column -->
         <column name="country_id"></column>   <!--  another foreign key column, the number of columns should match the property-ref -->
      </many-to-one>


In the Address mapping, I would have the property-ref defined as userIdCountryId:

Code:
<!-- properties name, logical name of the grouping - not an actual name of the property
        this name can be used as property-ref for ecField -->
        <properties name="userIdCountryId">
           <!-- the insert="false" and update="false" are needed as the fields are already used as properties -->
           <property name="dummyUserId" type="long" insert="false" update="false">
              <column name="user_id" ></column>
           </property>
           <property name="dummyCountryId" type="long" insert="false" update="false">
              <column name="country_id" ></column>
           </property>
        </properties>


When I say

Code:
from User left join Address
where userId = 10


the sql query does join both of the columns from the user table to the address table, it nicely joins both the fields from user to address i.e.,
Code:
from User user left outer join Address address
on user.user_id = address.user_id and user.country_id = address.country_id


What is the difference between using one-to-one and many-to-one or are there any other options besides these?

When will the simple left join on any other class be introduced without having to introduce all these?


Top
 Profile  
 
 Post subject: mapping using multiple columns and invoke left join
PostPosted: Thu May 28, 2009 3:38 pm 
Regular
Regular

Joined: Tue Feb 19, 2008 6:05 pm
Posts: 82
Now here is where the mapping problem starts, Let's say I need to lookup Address table for a given user and if the address does not exist, I expect a null. I am also looking for a specific address with state 'NJ' and that the user table does not have a state column to link with address table, my simple sql would be:

Code:
user left join address where user.userId = address.userId and user.countryId = address.countryId
and address.stateId = 'NJ'
otherwise I will end up getting many addresses, in my case there is no stateId in the user table
Code:
<many-to-one name="uniqueAddress" class="database.Address" property-ref="userIdCountryId">
         <column name="user_id"></column>   <!--  name of the foreign key column -->
         <column name="country_id"></column>   <!--  another foreign key column, the number of columns should match the property-ref -->
      </many-to-one>

<!-- properties name, logical name of the grouping - not an actual name of the property
        this name can be used as property-ref for ecField -->
        <properties name="userIdCountryId">
           <!-- the insert="false" and update="false" are needed as the fields are already used as properties -->
           <property name="dummyUserId" type="long" insert="false" update="false">
              <column name="user_id" ></column>
           </property>
           <property name="dummyCountryId" type="long" insert="false" update="false">
              <column name="country_id" ></column>
           </property>
        </properties>

This above mapping works fine when I write my HQl as

Code:
from User user left join user.uniqueAddress address
where address.stateName = 'NJ'


and I get what I need, however because it is mapped as a many-to-one and the user table does not include the state, every time I say

Code:
from User

it would throw an exception saying that it found multiple addresses for the same user. So in such a case there is no way I can map a user and address table?

I see that there is no solution to this problem unless I had a specific way to write my left join on Address as in my case my User does not have country field at all. Is there left join available or is there an alternative solution to this problem?

Help please!!!


Top
 Profile  
 
 Post subject: Re: mapping using multiple columns and invoke left join
PostPosted: Thu May 28, 2009 3:57 pm 
Regular
Regular

Joined: Tue Feb 19, 2008 6:05 pm
Posts: 82
update, insert (optional - defaults to true) : specifies that the mapped
columns should be included in SQL UPDATE and/or INSERT statements.
Setting both to false allows a pure "derived" property whose value is
initialized from some other property that maps to the same colum(s) or
by a trigger or other application.

from the above, when I set the update and insert to false, I was expecting that when I say

Code:
from user


it should not fetch even a proxy of the address, how else can I map the address in user so that it is not fetched unless I explicitly request? as the query breaks saying

Code:
org.hibernate.HibernateException: More than one row with the given identifier was found: database.Address@1eb02cd, for class: Address


which makes sense that I mapped it as many-to-one


Top
 Profile  
 
 Post subject: Re: Many to one mapping using non primary key
PostPosted: Fri May 29, 2009 4:49 am 
Newbie

Joined: Fri May 29, 2009 4:44 am
Posts: 2
Thanks!


Top
 Profile  
 
 Post subject: Re: Many to one mapping using non primary key
PostPosted: Fri May 29, 2009 4:50 am 
Newbie

Joined: Fri May 29, 2009 4:44 am
Posts: 2
Thanks!


Top
 Profile  
 
 Post subject: Re: mapping using multiple columns and invoke left join
PostPosted: Fri May 29, 2009 6:30 pm 
Regular
Regular

Joined: Tue Feb 19, 2008 6:05 pm
Posts: 82
One-to-one will not work the way we want it as it just cannot be used to fetch lazily for 1<---->0 or 1 relationship, https://www.hibernate.org/315.html and https://www.hibernate.org/162.html

But why is the many-to-one being eagerly loaded, when property-ref is used?

A many-to-one maps with lazy="proxy" by default, meaning it does not eagerly fetch the parent object, however, I noticed that if I use the property-ref to map to an other property field, they it is by default doing an eager fetch, no matter what other property options I set.

When will I find an answer to this problem?

Regards
Krishna


Top
 Profile  
 
 Post subject: Re: mapping using multiple columns and invoke left join
PostPosted: Sun Jul 19, 2009 12:18 pm 
Regular
Regular

Joined: Tue Feb 19, 2008 6:05 pm
Posts: 82
Alright, raised a JIRA

http://opensource.atlassian.com/project ... e/HHH-4046

Regards
Krishna


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 15 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.