“哪里存在"在休眠 HQL 中

问题描述

如何写一个不存在"?在 HQL 中查询?我正在尝试获取一个 HQL 不存在查询，该查询返回与此 Oracle SQL 查询相同的结果:

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))

我的映射文件如下:

<!-- 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>

我在 School.hbm.xml 文件中尝试了以下标签

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>

我得到了这个堆栈跟踪

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?

谢谢！

推荐答案

您命名的查询无效(school_id 不是 Student 实体的属性)，其中防止 SessionFactory 被实例化.您需要考虑对象和关联，而不是列.试试这个:

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)
)

参考资料

• Hibernate 核心参考指南
  • 14.13.子查询

  这篇关于“哪里存在"在休眠 HQL 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！