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

Question

I am trying to make a query in hibernate "criteria" (not in hql) but I am not getting 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 made it with two different queries and then use Java function to get the result, BUT I need one single criterion query for it.

Hibernate criteria is usually written in native SQL, but not in HQL. And it also contains only what goes after "WHERE". — Anthony
– Anthony, Commented Sep 27, 2012 at 11:14
jubinPatel, Since this seems to be what you really wanted to ask, from your other question, you should close and delete that question as this is a duplicate otherwise. — Brock Adams
– Brock Adams, Commented Sep 27, 2012 at 11:19
Yeah, you don't have enough rep yet. Couple things you can do: (1) Edit the question to just say that it is a duplicate, and please close it, (2) Open a question on meta asking for the question to be deleted. Or, if you get 15 rep, you can flag the question for a moderator. — Brock Adams
– Brock Adams, Commented Sep 27, 2012 at 11:29
The query is not quite the same as in the previous question. I suspect a typo here that you might want to correct. — Don Roby
– Don Roby, Commented Sep 27, 2012 at 12:23

Don Roby · Accepted Answer · 2012-09-28 02:28:47Z

7

Modulo knowing what your actual entity mappings are (and this is of course thoroughly untested), it's likely solved by something resembling

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 need adjustment, as "column1" and "column2" are sql field names, and what you need in these places are java properties.

edited Sep 28, 2012 at 2:28

answered Sep 28, 2012 at 0:46

Don Roby

41.2k6 gold badges96 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Atif Imran · Accepted Answer · 2012-09-27 11:22:25Z

0

Create a criteria select all 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:crit.add(Restrictions.not(Restrictions.in("YourNotInCondition", YourNotInCondition)));

answered Sep 27, 2012 at 11:22

Atif Imran

2,1092 gold badges19 silver badges34 bronze badges

4 Comments

Jubin Patel Over a year ago

i made it like that but for 2nd condition i am not getting proper solution

Atif Imran Over a year ago

Did you try nesting your restriction inside the NOT IN?

Jubin Patel Over a year ago

if u have any proper place where i can find it or see at least one example

Atif Imran Over a year ago

I haven't tried any nesting before if I do find something, I'll let you know.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related