0

How can provide OR clause in LEFT_JOIN in hibernate criteria.

Criteria mainCrit=hibernateSession.createCriteria("Main.class");    
Criteria aPropertyCrit=mainCrit.createCrieria("child",CriteriaSpecification.LEFT_JOIN);

It generates sql as

select this_.Id,this_childId....
from main as this_
left join child as child1_ on child1_ .id=this_.childId
where.....

And I need to generate the sql as

select this_.Id,this_childId....
from main as this_
left join child as child1_ on child1_.id=this_.childId or child_.ParentId=this_.childId
where.....

How do I provide OR clause in LEFT_JOIN in above criteria.

pinkb
  • 533
  • 2
  • 6
  • 15

1 Answers1

0

The extending of the JOIN ... ON part, could be done, even with Criteria API. But only to be more restrictive, i.e. adding the AND part.

Take a look on the overloads of the createCriteria method, mostly the last parameter withClause:

Criteria createCriteria(String associationPath,
                        String alias,
                        JoinType joinType,
                        Criterion withClause)
                        throws HibernateException

Create a new Criteria, "rooted" at the associated entity, assigning the given alias and using the specified join type.

Parameters:

  • associationPath - A dot-seperated property path
  • alias - The alias to assign to the joined association (for later reference).
  • joinType - The type of join to use.
  • withClause - The criteria to be added to the join condition (ON clause)

In fact, any other approach (e.g. adding the OR), is breaking the mapping principal, introducing the ability to have the cross join at the end. see Hibernate criteria: Joining table without a mapped association

Community
  • 1
  • 1
Radim Köhler
  • 117,533
  • 47
  • 231
  • 321
  • Criteria mainCrit=hibernateSession.createCriteria("Main.class","main"); Criteria aPropertyCrit=mainCrit.createCrieria("child","child",JoinType.LEFT_OUTER_JOIN,Restrictions.eqProperty("main.childid","child.parentid")); Now it inserts left outer join child child_ on this_.childid=child_.Id and ( this_.SectionId=child_.parentId ) . And I wanted OR clause instead of AND in the generated sql. – pinkb Sep 26 '13 at 09:25
  • 1
    In the first paragraph I am explaining that the only way, how to work with JOIN ... ON is with the **AND**. There is no support for OR, because that would break the mapping and realtionship. That's the correct ORM behaviour – Radim Köhler Sep 26 '13 at 09:40
  • So now how do I achieve the result I wanted. I guess I would now try to do it with DetachedCriteria. Any inputs from your end would be highly appreciated. – pinkb Sep 26 '13 at 10:23
  • What I would suggest is to go with the `Subquery` filtering, I.e. Create a SQL Script, returning the ID we want to be used for filtering and then wrap it in `DetachedCriteria`. Add a Criterion as a Restriction. Please, try to check the basic concept here http://stackoverflow.com/a/12637204/1679310. We can use OR expression wrapping two subqueries (child id and child.parentId) either... – Radim Köhler Sep 26 '13 at 10:29