Hibernate Criteria With Property Not In (Subquery)

Question

I want to execute query something like

Select id, name from information where name not in (select firstname from contact where id  = 1)

Information
Id Name
1  Test

Contact
id firstname
1  name
2  Test

If I am using neProperty() function, it will returns records as name != Test.

How can I implement using hibernate criteria?

Thanks

Answer 1

You could use DetachedCriteria to build the subquery.

// This corresponds to (select firstname from contact where id  = 1)
DetachedCriteria subquery = DetachedCriteria.forClass(Contact.class)
    .add(Restrictions.eq("id", 1))
    .setProjection(Projections.property("name"))

// This corresponds to (select information where name not in (subquery))
ICriteria criteria = session
    .createCriteria(Information.class)
    .add(Subqueries.notIn("name", subquery));

Instead of using a subquery, you can just load the contact using session.get, with a chance to hit the cache:

Contact contact = session.Get<Contact>(1);

ICriteria criteria = session
    .createCriteria(Information.class)
    .add(Property.ne("name", contact.getName()));

Disclamer: I'm a) not a java programmer and b) may have made mistakes so it probably does not compile. The code is more to roughly show the idea and is hopefully helpful anyway.

Answer 2

Create a select-all criteria:

Criteria cr = session.createCriteria(Your.class); 
List list = cr.list(); 

Then you can add you restriction to it, i.e. where column 1=8 etc like this:

cr.add(Restrictions.eq("YourCondition", YourCondition));

Finally, you can provide the not in clause like this:

cr.add(Restrictions.not(Restrictions.in("YourNotInCondition", YourNotInCondition)));