"where exists" in Hibernate HQL

Question

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:

<!-- School.hbm.xml -->     <class name="com.companyname.School"              table="SCHOOL"              >        <!-- 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>  <!-- Student.hbm.xml -->    <class          name="com.companyname.Student"          table="STUDENT"          >        <!-- 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> <!-- Status.hbm.xml. This is a table of lookup data-->    <class          name="com.companyname.Status"          table="LK_STATUS"          mutable="false"          >       <id             name="id"             type="java.lang.Integer"             column="ID"             >        </id>       <property             name="name"             type="java.lang.String"             column="NAME"             not-null="true"             unique="true"             >       </property>    </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.<init>(HQLQueryPlan.java:77)         at org.hibernate.engine.query.HQLQueryPlan.<init>(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.<init>(SessionFactoryImpl.java:352) 

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

Thank you!

Answer 1

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