Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 11 posts ] 
Author Message
 Post subject: Restrictions.isNotNull and one-to-one association
PostPosted: Fri Oct 17, 2008 2:41 am 
Beginner
Beginner

Joined: Thu Oct 16, 2008 11:19 am
Posts: 23
Location: Norway
I'm having trouble using Restrictions.isNotNull on one end of a one-to-one association. The association looks like this on one side:

<many-to-one name="station" column="STATION_ID" class="Station" cascade="all" unique="true" not-null="true"/>

...and this on the other:

<one-to-one name="office" class="Office" property-ref="station"/>

It is on the latter I'm trying to filter/search using QBC. The part relating to this association looks like this:

sortByThese = Restrictions.and( sortByThese, Restrictions.isNotNull( "office" ) );

However, it doesn't filter at all, and I get the full set of Station objects. There is nothing wrong with the surrounding code, as when I exchange "office" for a property of the Station class then it works like a charm.

Am I going about this the wrong way? ...can I not use isNotNull on an association? If not, does anyone have a suggestion for how to do it?


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

Joined: Tue Sep 09, 2008 5:42 am
Posts: 22
Location: Romania
Ciao,

I don't understand exactly your domain model and the classes envolved, but I think that if there is like you said a one-to-one relation between the two of them, then you should map in both of the classes the referenced property like a one-to-one relation.

Bellow I inserted a example of how I map the one-to-one ralation in two tables that I used and works in NHibernate:

Let's say you have two tables: Person where you have some Personal Information and another table with Details. In the Person class you have a reference to a Detail object, that can also be NULL in some casses, and both Person and Detail tables have the same primary key. When I map, I put in both of the mapping files the relation as one-to-one like this:

In Person:

Code:
    <one-to-one name="Detail" class="Detail" cascade="none" fetch="join"/>


In Detail:

Code:
    <one-to-one name="Person" class="Person" cascade="none" fetch="join"/>


And on the Person class you have a property of type Detail (that can be NULL) and on the Detail you have a property of type Person.

I hope this will give you a clear ideea of the use of one-to-one relation.

And when you want to get only the Persons that have a Detail you can use this code:

Code:
ICriteria criteriaPerson=session.CreateCriteria(typeof(Person));
ICriteria criteriaDetail=ctiteriaPerson.CreateCriteria("Detail",NHibernate.SqlCommand.JoinType.RightOuterJoin);

IList listWithOnlyThoseWhoHaveDetail=criteriaPerson.List<Person>();


If you want all Persons with or without Detail you simply have to change the JoinType to LeftOuterJoin.

Good luck my friend! Hope this helps!


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 17, 2008 9:17 am 
Beginner
Beginner

Joined: Thu Oct 16, 2008 11:19 am
Posts: 23
Location: Norway
Thanks for the reply.

tomaandtoma wrote:
I don't understand exactly your domain model and the classes envolved, but I think that if there is like you said a one-to-one relation between the two of them, then you should map in both of the classes the referenced property like a one-to-one relation.

I don't know. The association is done "by the book" (Hibernate in Action, Manning), and I know from others parts of my application that it works. It just seems that Restrictions.isNotNull doesn't go very well with associations.

tomaandtoma wrote:
In Person:

Code:
    <one-to-one name="Detail" class="Detail" cascade="none" fetch="join"/>


In Detail:

Code:
    <one-to-one name="Person" class="Person" cascade="none" fetch="join"/>

But, if you do it like that, how do you identify which Person object goes with which Detail object? Surely you need a foreign key somewhere.


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

Joined: Tue Sep 09, 2008 5:42 am
Posts: 22
Location: Romania
You match the Person primary key with the Detail primary key. The Detail primary-key if it is composed only from one field shouldn't be auto-incremented.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 17, 2008 9:45 am 
Beginner
Beginner

Joined: Tue Sep 09, 2008 5:42 am
Posts: 22
Location: Romania
Or you can keep your mapping and try this:

Code:
ICriteria crit=session.CreateCriteria(typeof(Person));
crit.Add(Expression.IsNotNull("Detail"));
crit.Add(Expression.Equals("OtherField"));
......

IList myList=crit.List<Person>();


And the Add method creates an AND between all the expresions you add.

See if this works.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 17, 2008 12:12 pm 
Beginner
Beginner

Joined: Thu Oct 16, 2008 11:19 am
Posts: 23
Location: Norway
I replaced it with an SQLRestriction and managed to get it to work.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 17, 2008 2:38 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
There are two types of one-to-one associations as we have seen in this post:

1. The primay key is the link. Both sides can be mapped with <one-to-one> as suggested by tomaandtoma.
2. There is a foreign key in one of the tables. This side is mapped with <many-to-one unique="true" ...> and the other side is mapped with <one-to-one property-ref=...> as shown by the original post.

I am not very familiar with Criteria queries, but if I have done the exact same thing with HQL both with IS NULL and NOT IS NULL filters and it does work. The one thing to be aware of is that you have to do an explicit LEFT JOIN otherwise Hibernate uses an INNER JOIN which also acts like a kind of filter. But this usually means that you don't get any results at all so I don't understand why you are getting too many stations. Can you post the SQL that is generated?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 22, 2008 8:03 am 
Beginner
Beginner

Joined: Thu Oct 16, 2008 11:19 am
Posts: 23
Location: Norway
When I filtered on only that association I got the following SQL sentence:

Code:
Hibernate: select this_.ID as y0_ from db.STATION this_ where (1=1 and this_.ID is not null) order by this_.NUMBER asc


...which fits well with the fact that I'm getting everything, but how the Criterion in question becomes "this_.ID is not null" is a mystery to me.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 22, 2008 8:21 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Are you joining the offices to the criteria? If you need to filter on associations you need to call either Criteria.createAlias() or Criteria.createCriteria().


Top
 Profile  
 
 Post subject: Re: Restrictions.isNotNull and one-to-one association
PostPosted: Thu May 17, 2012 2:41 pm 
Beginner
Beginner

Joined: Wed Jun 15, 2005 1:28 pm
Posts: 34
Location: United States
I just ran into this at Hibernate Core 3.6.6.Final. I added a Restrictions.isNull to a one-to-one association after adding an alias to the criteria, using CriteriaSpecification.LEFT_JOIN, expecting to get all of the entities with null association property. Instead, the id of the ROOT entity was filtered to null, returning zero results (id is never null).


Top
 Profile  
 
 Post subject: Re: Restrictions.isNotNull and one-to-one association
PostPosted: Thu May 17, 2012 2:56 pm 
Beginner
Beginner

Joined: Wed Jun 15, 2005 1:28 pm
Posts: 34
Location: United States
As a simple workaround, I changed our code to use alias.id as the restriction property, instead of only the association property name as found on the root entity, and that works as expected. Our code was tested and working when written the other way at a previous release of Hibernate, so this must have been a regression.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 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.