Hibernate Criteria object - how to... ?

[Somak Dalui]

I have to execute a query like : Select * from Application a where a.appNum in (select appNum from Deal d where d.appNum = a.appNum); I tried this :

Criteria criteria = session.createCriteria(ApplicationModel.class);
criteria.setProjection(Projections.property("appNum"));
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(DealModel.class);
detachedCriteria.setProjection(Projections.property("appNum"));
criteria.add(Property.forName("appNum").in(detachedCriteria));

criteria.list() gives an error com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near ','.. The hibernate query generated is

select this_.appNum as y0_ from Application this_ where this_.appNum in (select this0__.appNum as y0_ from Deal this0__)

. I am using Sybase DB and Hibernate 3. Please help..

[Mark Spritzler]

So why are you using a DetachedCriteria object in this case?

Mark

[Somak Dalui]

oh ! ok So what am I missing here ? To fire a subquery what can I do instead of DetachedQuery ?

[Mark Spritzler]

add a Criteria object to the Criteria object. A Detached is for creating an object outside of a Session, and later on setting the session on the detached object, then executing the detached criteria.

Mark

[Halcon Guatemala]

Hi to @ll, Mark, could you illustrate it? Could We nest distinct queries attaching criterias repeteadly?

Thanks in advance

[Mark Spritzler]

Sort of, it is more subqueries that you are adding to the main query in the Criteria object. You can do a search at hibernate's web site to see more

http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#querycriteria

Mark

[Somak Dalui]

Thank you Mark.