-->
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.  [ 6 posts ] 
Author Message
 Post subject: Join condition in Criteria possible?
PostPosted: Wed Dec 12, 2007 6:56 am 
Newbie

Joined: Wed Dec 12, 2007 6:48 am
Posts: 4
I've got a critical bug to fix for a sick colleague who used hibernate, which is new to me. A developers dream. ; )

The problem is that I need to put a join condition in a Criteria object. I know it's easy when using HQL, but I can't find any reference to it in the Criteria API or documentation.

This is what I need to get:
Code:
select count(*)
from ...
left outer join EVENTSOURCE es on r.ID_EVENTSOURCE=es.ID and es.ENABLED='Y'
where ...


The left outer join is easy, a condition in the where clause is easy, but how do I specifiy "es.ENABLED='Y'" in my join condition??

My current code doesn't work as hibernate says it has no reference to eventsource:
Code:
criteria.setFetchMode("eventsource", FetchMode.JOIN).add(Restrictions.eq("enabled", true));

But if I add a reference to eventsource in the criteria, he adds the restriction in the where clause instead of condition.

How should I solve this??


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 12, 2007 9:13 am 
Newbie

Joined: Wed Dec 12, 2007 6:48 am
Posts: 4
I can solve it as follows, but it's not a very pretty nor typesafe solution:

Code:
criteria.setFetchMode("eventsource", FetchMode.JOIN);
            criteria.add(Restrictions.sqlRestriction("(ENABLED = 'Y' or ENABLED is null)"));


This will work as long as there are no other 'enabled' columns on the other tables.

For some odd reason, if I add a normal Restriction on the enabled field, hibernate changes the outer join to an inner join. : /


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 12, 2007 9:55 am 
Beginner
Beginner

Joined: Tue Apr 24, 2007 12:53 pm
Posts: 28
ouwenbelg --


Can you post the hibernate mapping files? If i see them, i should be able to show you how to write a Criteria for the association.

if not here some info to get you started maybe:

Basically there must be a defined association EVENTSOURCE table and the other table you are trying to join on.

Using the association you can do a:

Code:
).createCriteria(String associationPath, String alias, int joinType).add(Restrictions.eq("enabled", true));


Though you may have to qualify the "enabled" column name with the associationPath prefix, but maybe not: check the generated SQL to be sure.

Hope that helps.

-B[/i]


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 12, 2007 10:31 am 
Newbie

Joined: Wed Dec 12, 2007 6:48 am
Posts: 4
Thanks for the useful tip, panth13, I forgot about the xml file.

Code:
<class name="a.b.c..ChangeRequest" table="SERVICEREQUEST">
        <id name="id" column="ID" />
        <property name="urgentId" column="URGENCY" />
...

        <many-to-one name="eventsource" column="ID_EVENTSOURCE" lazy="false" />       
...   
    </class>


Looking at the hibernate documentation, it should be possible to add a restriction on the many-to-one relation with eventsource, using the "formula" attribute.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 12, 2007 10:48 am 
Beginner
Beginner

Joined: Tue Apr 24, 2007 12:53 pm
Posts: 28
ouwenbleg--

The following should work:

Code:

Cirteria serviceRequestCrit = session.createCriteria(ChangeRequest.class).add(Restrictions.eq("somevalue" , "somevalue");
// Do your restrictions for the SERVICEREQUEST table here

Criteria eventSourceCrit = serviceRequestCrit.createAlias("eventsource", "es", Criteria.INNER_JOIN).add(Restrictions.eq("es.enabled", true);
// Do any other restrictions on the EVENTSOURCE table here

List results = eventSourceCrit.list(); // Or the serviceRequestCrit, it doesn't matter.



Hope that helps!

-B


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 12, 2007 11:58 am 
Newbie

Joined: Wed Dec 12, 2007 6:48 am
Posts: 4
Thanks! Your example had some flaws (outer join, not inner join), but it did help me find the solution:

Code:
criteria = criteria.createAlias("eventsource", "es", Criteria.LEFT_JOIN);
            criteria.add(Restrictions.or(Restrictions.eq("es.enabled", true), Restrictions.isNull("es.enabled")));


problem solved :)


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