Organizational Research By

Surprising Reserch Topic

where exists in hibernate hql

where exists in hibernate hql  using -'java,hibernate,orm,hql'

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
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" />


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

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


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

<query name="myQuery">
    from School s where not exists from Student st
    where and not in (0,1,2,3,4)

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(
        at org.hibernate.hql.antlr.HqlBaseParser.negatedExpression(
        at org.hibernate.hql.antlr.HqlBaseParser.logicalAndExpression(
        at org.hibernate.hql.antlr.HqlBaseParser.logicalOrExpression(
        at org.hibernate.hql.antlr.HqlBaseParser.expression(
        at org.hibernate.hql.antlr.HqlBaseParser.logicalExpression(
        at org.hibernate.hql.antlr.HqlBaseParser.whereClause(
        at org.hibernate.hql.antlr.HqlBaseParser.queryRule(
        at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(
        at org.hibernate.hql.antlr.HqlBaseParser.statement(
        at org.hibernate.hql.ast.QueryTranslatorImpl.parse(
        at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(
        at org.hibernate.hql.ast.QueryTranslatorImpl.compile(
        at org.hibernate.engine.query.HQLQueryPlan.<init>(
        at org.hibernate.engine.query.HQLQueryPlan.<init>(
        at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(
        at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(
        at org.hibernate.impl.SessionFactoryImpl.<init>(

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

Thank you!

asked Oct 7, 2015 by 20shahi
0 votes

Related Hot Questions

1 Answer

0 votes
answered Oct 7, 2015 by sanjaypal1983