Request Hibernate criteria from a simple SQL query (with a subquery nested)

Question

I am trying to make a request in hibernate "criteria" (not in hql), but I do not understand what to do or what to use.

select * from foo where column1 = 8 and column2 not in ( select column2 from foo where column1 = 11 ) 

I did this with two different queries, and then I will use the Java function to get the result, BUT I need one criterion for it.

Answer 1

Having realized that your actual entity mappings (and this, of course, is completely untested), are probably solved by something similar to

 Session hibernateSession = ... (however you get it). DetachedCriteria d = DetachedCriteria.forClass(Foo.class) .add(Restrictions.eq("column1", 11)) .setProjection(Projections.property("column2")); Criteria criteria = hibernateSession.createCriteria(Foo.class) .add(Restrictions.eq("column1", 8)) .add(Subqueries.propertyNotIn("column2", d)); List<Foo> result = criteria.list(); 

This will almost certainly require adjustment, since "column1" and "column2" are sql field names, and what you need in these places are java properties.

Answer 2

Create criteria, select all Criteria cr = session.createCriteria(Your.class); List list = cr.list(); Criteria cr = session.createCriteria(Your.class); List list = cr.list(); Then you can add a constraint to it, that is, where the column is 1 = 8, etc. For example: cr.add(Restrictions.eq("YourCondition", YourCondition));

Finally, you can provide an offer not in the following form: crit.add(Restrictions.not(Restrictions.in("YourNotInCondition", YourNotInCondition)));