"where exists" in Hibernate HQL

Question

Hello Overflowers,

How can I write a "not exists" query in HQL? I am trying to get an HQL 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))

My mapping files are below:

  <!-- primary key ommitted -->
<set name="students"
     cascade="all" fetch="select" lazy="false" >
    <key column="SCHOOL_ID" />
    <one-to-many class="com.companyname.Student" />
</set>
</class>

  <!-- primary key ommitted -->
 <many-to-one name="school" 
              column="SCHOOL_ID"
              class="com.companyname.School" fetch="join" lazy="false"/>

 <many-to-one name="status"
        class="com.companyname.Status" column="STATUS_ID" />
</class>

I tried the following tag in my School.hbm.xml file

<query name="myQuery">
    <![CDATA[
    from School s where not exists from Student st
    where st.school_id=s.id and st.status.id not in (0,1,2,3,4)
    ]]>
</query>

and I got this stack trace

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in class path resource [applicationContext.xml]: Invocation of init method failed; nested exception is java.lang.NullPointerException Caused by: java.lang.NullPointerException at org.hibernate.hql.ast.HqlParser.negateNode(HqlParser.java:117) at org.hibernate.hql.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2378) at org.hibernate.hql.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2331) at org.hibernate.hql.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2296) at org.hibernate.hql.antlr.HqlBaseParser.expression(HqlBaseParser.java:2082) at org.hibernate.hql.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:1858) at org.hibernate.hql.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:454) at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:708) at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296) at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159) at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:248) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111) at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:77) at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:56) at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72) at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:402) at org.hibernate.impl.SessionFactoryImpl.(SessionFactoryImpl.java:352)

Can somebody let me know what I'm doing wrong?

Thank you!

Answer 1

Try this:

from School s where (select count(st) from Student st
  where st.school_id=s.id and st.status.id not in (0,1,2,3,4)) = 0

Answer 2

Your named query is not valid (school_id is not a property of the Student entity), which prevents the SessionFactory from being instantiated. You need to think object and associations, not columns. Try this instead:

from School as s
where not exists (
  from Student as st
  where st.school = s
  and st.status.id not in (0,1,2,3,4)
)

References

• Hibernate Core Reference Guide
  • 14.13. Subqueries