List all Cats which does not have any white kitten

rags_1979 · **Posted:** Wed Oct 04, 2006 7:02 am

Lets say my Cat class has a collection of kittens.

The following code will work for listing all cats which has atleast one white kitten

Code:

session.createCriteria(Cat.class).createCriteria("kittens").add(Expression.eq("color", "white");

How will this code change when I want to list all cats which do not have any white kitten?

I was tempted to write:

Code:

session.createCriteria(Cat.class).createCriteria("kittens").add(Expression.ne("color", "white");

But clearly the above code wont work because a cat with one white kitten and one black kitten would also qualify according to the above criteria by virtue of having a black kitten. Whereas my original requirement should be to eliminate any such cat, because it has a white kitten afterall (even though the other one is black).

Can someone help?

Thanks.
Raghu

CWitherow · **Posted:** Wed Oct 04, 2006 9:51 am

Create a DetachedCriteria that retrieves Cats w/ at least one white kitten. Then use that DetachedCriteria as a subquery to another Criteria. I.E.

Code:

DetachedCriteria subquery = DetachedCriteria.forClass(Cat.class, "c").setProjection(Projections.property("c.id").createAlias("kittens", "k").add(Restrictions.eq("k.color", "white");
Criteria crit = session.createCriteria(Cat.class).add(Subqueries.notExists(subquery));

This may not be exact -- I didn't try to run this and I may have botched the exact syntax, but it should be close.

Good luck,
Curtis ...

rags_1979 · **Posted:** Wed Oct 04, 2006 3:28 pm

Thanks a lot for your response.

I think I now get the 'flow' of how to achieve this. But I am not able to make it work.

I am trying this piece of code to learn a bit about this detachedcriteria and notexists concept.

Each subject has a few topics. I am trying to 'subtract' all topics that belong to subjectId=1 from the list of all topics.

Let us say Topic is a simple class with id,name and subject as its properties.

Subject similarly has id, name and topics (set) as its properties.

The code that I am using right now is:

Code:

      DetachedCriteria topicDetachedCriteria = DetachedCriteria.forClass(Topic.class).setProjection(Projections.property("id"));
        topicDetachedCriteria.createCriteria("subject").add(Expression.eq("id",1));
        Session session = HibernateUtil.currentSession();

        Criteria c = session.createCriteria(Topic.class);
        c.add(Subqueries.notExists(topicDetachedCriteria));
        List list = c.list();
        System.out.println("List = "+list);

This is giving me a grammar exception. If i remove the projection, I am getting a NullPointerException. Am not sure why Projection is needed in the first place, but some googling told me that adding it takes care of the nullpointer exception.

Can you please help me correct the mistake I am doing?

Thanks.
Raghu

ps: The exception I am getting:

Hibernate: /* criteria query */ select this_.Id as Id0_, this_.name as name19_0_, this_.SubjectId as SubjectId19_0_, this_.remarks as remarks19_0_ from topic_master this_ where notExists (select this0__.Id as y0_ from topic_master this0__ where subject1_.Id=?)
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at Main.main(Main.java:566)
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:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select this0__.Id as y0_ from topic_master this0__ where subject1_.Id=1)' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3124)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1149)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1262)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:75)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)

niqueco · **Posted:** Wed Oct 04, 2006 5:35 pm

Aren't you using version if MySQL which doesn't support nested queries (aka subquieries)?