Joins with Hibernate Criteria API

durgakbe · **Joined:** Thu Jun 28, 2007 7:08 am **Posts:** 4

Can any body explain how to do a left outer join with the oracle 'ON' condition in Hibernate Criteria API.

Here is the sql query for which I need to get the results.

select asset.assetid from asset
left outer join assetonagreement on asset.ASSETID = assetonagreement.ASSETID

derek.ekins · **Joined:** Wed Oct 04, 2006 8:57 am **Posts:** 25

the best information I could find on this was from this thread http://forum.hibernate.org/viewtopic.php?t=967820

looks like you can only do it in hql.

amila733 · **Posted:** Wed Jul 25, 2007 8:25 am

Hi

List list=sess.createCriteria(asset .class,"a").createCriteria("assetonagreement","m",Criteria.LEFT_JOIN).list();

is this what you want

Amila

(Don't forget to rate if helps)

derek.ekins · **Joined:** Wed Oct 04, 2006 8:57 am **Posts:** 25

I found a way to simulate what we are looking for (I am using NHibernate but I presume something similar will work for hibernate)

This method creates a condition with the restrictions you require and then also allows for the case that no result exists.

Code:

        public static ICriteria CreateLeftOuterJoin(ICriteria criteria, string associationPath, string alias, string key, params ICriterion[] restrictions)
        {
            ICriteria joinedCriteria = criteria.CreateCriteria(associationPath, alias, JoinType.LeftOuterJoin);

            Conjunction conjunction = Expression.Conjunction();
            conjunction.Add(Expression.IsNotNull(key));

            foreach (ICriterion restriction in restrictions)
            {
                conjunction.Add(restriction);
            }

            joinedCriteria.Add(Expression.Disjunction()
                             .Add(conjunction)
                             .Add(Expression.IsNull(key))
                );
            return joinedCriteria;
        }

and then you use like this

Code:

CreateLeftOuterJoin(criteria, "parent.Children", "child","child.Id", 
                    Expression.Eq("child.Age", 12));

Sorry it's c#, I assume it wouldn't look to different in java though. The idea is to create the condition that you want, and then allow that condition or null.

The key parameter lets you specify a value to check for null on, I assume any property on the object will do.