Criteria on associations

pgl · **Posted:** Wed Feb 22, 2006 9:41 am

Hi everybody,

I'm having a little trouble using Criteria's for the following situation

Class Product
- Property title [String]
- Property descriptions [List<ProductDescription>]

Class ProductDescription
- Property title

Now i'd like to do something like (in pseudo SQL)

Code:

SELECT FROM Product 
  WHERE title LIKE '%foo%' 
     OR descriptions.title LIKE '%foo%'
     OR descriptions EMPTY

which means fetch all Product instances, where either their title or one of the descriptions title matches a given String - with the special case, that there might be no descriptions existing.

First I tried this

Code:

  Criteria criteria  = session.createCriteria(Product.class);
  criteria.add(Restrictions.like("title", "foo", MatchMode.ANYWHERE);
  Criteria descC     = criteria.createCriteria("descriptions");
  descC.add(Restrictions.like("title", "foo",MatchMode.ANYWHERE);
  List<?> result     = criteria.list();

Which results in an AND conjunction, instead of an OR conjunction. The next thing I tried was:

Code:

  Criteria criteria  = session.createCriteria(Product.class);
  criteria.createAlias("descriptions", "desc");
  criteria.add(Restrictions.or(Restrictions.like("desc.title", titleField.getText(), MatchMode.ANYWHERE),
                               Restrictions.like("title", titleField.getText(), MatchMode.ANYWHERE)
              ));

This is better, but only Product objects which actually do have at least one ProductDescription are being fetched. Any product which doesn't have any ProductDescriptoins associated is ignored.

Is there any way to also include these records into the Criteria result?

---

Hibernate version:
3.0

Name and version of the database you are using:
MySQL 5

slepit · **Joined:** Fri Aug 06, 2004 1:01 pm **Posts:** 13

Hi,

I have the same problem and don't have any solution yet ...
Does anybody have an idea ?

vikas_s · **Posted:** Wed Mar 08, 2006 4:35 pm

Hi,

It seems that you are looking for a LEFT JOIN between Product and ProductDescription tables.

Assuming that this is indeed what you need, I believe the following simple HQL query should do the trick :

Code:

from Product prod 
    left join prod.descriptions descs 
where prod.title like :prodTitle
    or descs.title like :descTitle

AFAIK, doing a LEFT JOIN is not that easy using criteria, as compared to HQL.

pgl · **Posted:** Thu Mar 09, 2006 11:47 am

vikas_s wrote:

AFAIK, doing a LEFT JOIN is not that easy using criteria, as compared to HQL.

If a complex/difficult solution is the only way, then I'll take it. But from the various approaches I've taken and from what I've read in the net it seems to me, that it isn't possible at all - is it?

simonwillnauer · **Posted:** Thu Mar 09, 2006 12:24 pm

It is.....

you need to use a left outer join, this is possible use the following code:

Code:

Criteria crit = session.createCriteria(Product.class)      .createAlias("descriptions","desc",CriteriaSpecification.LEFT_JOIN);
      crit.add(Restrictions.or(Restrictions.like("title",titleField.getText()),
Restrictions.or(Property.forName("descriptions").isEmpty(),
Restrictions.like("desc.title",titleField.getText())))); 

try it.

simon

pgl · **Posted:** Fri Mar 10, 2006 4:29 am

simonwillnauer wrote:

try it.

Unfortunately I can't find the constant LEFT_JOIN within the CriteriaSpecification and there also seems to be no matching createAlias method accepting the parameter within the 3.0 release.

simonwillnauer · **Posted:** Fri Mar 10, 2006 5:06 am

This is true, AFAIK this feature comes with hibernate 3.1. I couldn't achieve a left outer join using the Criteria API without this feature.
You might upgrade to the latest version of hibernate....

sry... simon

pgl · **Posted:** Fri Mar 10, 2006 8:57 am

simonwillnauer wrote:

This is true, AFAIK this feature comes with hibernate 3.1. I couldn't achieve a left outer join using the Criteria API without this feature.

Okay, thanks. Since the 3.1.2 Version still has a bug pending I'll wait for the 3.1.3 release - anyone knows when the release is scheduled?

pgl · **Posted:** Wed Mar 22, 2006 10:55 am

Just a quick note: It's finally working - thanks a lot!

DoWn · **Posted:** Wed Apr 05, 2006 5:12 am

Hi...

I tried several times to manage to make a left outer join because i need it

I update my hibernate to 3.1.3

and i tried to run the following lines using junit :

Criteria crit2 = HibernateUtil.currentSession().createCriteria(Critere.class).createCriteria("listeValeurs","valeur",CriteriaSpecification.LEFT_JOIN);

lstCritere = crit2.list();

I obtain always the same error message :

java.lang.NoSuchMethodError: org.hibernate.Criteria.createCriteria(Ljava/lang/String;Ljava/lang/String;I)Lorg/hibernate/Criteria;
at
[....]findAllCriteresAndValeursByClient(CritereDAOBase.java:110)
at
[...].testFindAllCriteresAndValeursByClient(CritereDAOTest.java:164)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

can anyone help ?

thank you..