1

How can I write a "not exists" query in hibernate criteria or named queries? I am trying to get a named query not exists query which returns the same results as this Oracle SQL query:

select *
from SCHOOL a
where not exists (select 1
from STUDENT b
where B.SCHOOL_ID=a.id
and B.STATUS_ID not in (0,1,2,3,4))
Benoit Wickramarachi
  • 6,096
  • 5
  • 36
  • 46
user2679352
  • 81
  • 1
  • 2
  • 5

2 Answers2

2

Use sqlRestriction. This will be injected directly in the final query, so you need to use the database column names.

School.createCriteria().list {
  sqlRestriction(" not exists(select 1 from student s where s.school_id = this_.id and ...)")
}
  • isn't the sqlRestriction make the application dependent to the under lining database engine. Is there a library extending the HibernateCriteriaBuilder? – user2679352 Aug 15 '13 at 14:49
  • You meant **not** exists I guess. – dmahapatro Aug 15 '13 at 15:01
  • @user2679352 Yes, but this query seems pretty standard to me. If you don't use database functions you're good to go. Another solution is to use HQL like JB answers you. –  Aug 15 '13 at 15:49
  • How did you know to use `this_` for the School table. Is this mentioned somewhere in hibernate docs? – mowwwalker Dec 30 '15 at 06:03
  • 1
    @mowwwalker I looked at the generated query –  Jan 07 '16 at 16:59
2

In HQL:

select s from School s where not exists (
    select st.id from Student st 
    where st.school = s 
    and st.statusId not in (0,1,2,3,4))
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • 1
    Is there a way to put the HQL in a named query? – user2679352 Aug 15 '13 at 14:49
  • That's the principle of a named query: it's an HQL query with a name. I don't get your question. – JB Nizet Aug 15 '13 at 15:09
  • When implementing static namedQueries = { mynamedquery {}} on the domain object. it expect a HibernateCriteriaBuilder definition. Is there a way to write HQL for HibernateCriteriaBuilder? – user2679352 Aug 15 '13 at 15:22
  • 1
    In my experience, forget about not using HQL. It can do things much easier than criteria queries. That is just my 2c though. – James Kleeh Aug 15 '13 at 15:41
  • @user2679352 You are not getting the point. Criteria/NamedQuery ultimately converts to an HQL query while execution. You can use the above HQL directly. Moreover, you can create a static method/closure inside the domain class which uses this query to give you results, and use it the way you use dynamic finders. – dmahapatro Aug 15 '13 at 15:46
  • OK. Now I understand. Grails has it own notion of named queries which are not the same as Hibernate named queries. See http://stackoverflow.com/questions/8824465/using-hibernate-hql-named-queries-in-grails – JB Nizet Aug 15 '13 at 15:52