[紧急]Hibernate3.5 如何转换下列SQL 到 HQL ?

Question

各位世外高人:      本人有一条SQL,不知如何转换到HQL,请帮忙，感激不尽!!!  SQL:       SELECT  distinct A.* FROM tc_exam_item A 
LEFT OUTER JOIN  tc_item_plantcode B ON A.ID =B.ItemId 
LEFT OUTER JOIN  tc_exam_item_result C ON A.ID=C.BIZDOC_ID 
WHERE b.partno LIKE '%PN-104-%' AND C.ResultType='2';
  主表:  tc_exam_item  从表:  tc_exam_item_result  主表:  tc_item_plantcodeHQL:     Strfind="select distinct d from TcExamItem d " + 
     "left outer join TcItemPlantcode e on d.id=e.tcExamItem.id " + 
     "left outer join TcExamItemResult f on d.id=f.tcExamItem.id ";
    E_mail:[email protected]    

Answer 1

select destinct a from tc_exam_item A,tc_item_plantcode B,tc_exam_item_result C
where A.ID =B.ItemId and A.ID=C.BIZDOC_ID  
and b.partno LIKE '%PN-104-%' AND C.ResultType='2';或者 你的a表中有其他两个表的对象 一对一或一对多关系的话,也可以使用JOIN
select destinct a from tc_exam_item A join A.B表对象 B join A.C表对象
where b.partno LIKE '%PN-104-%' AND C.ResultType='2';其中表名 要改成表映射的实体类的类名, 字段改成映射类里的相对应的属性名

Answer 2

多谢楼上,请帮忙检查一下:我自己HQL:select distinct d from TcExamItem d 
left outer join TcItemPlantcode e on d.id=e.tcExamItem.id 
left outer join TcExamItemResult f on d.id=f.tcExamItem.id  
where   d.tcItemStatus.statusId='111' 
and upper(e.partno) like '%PN-101-A%' 
and upper(f.lawHk) like '%HK%'Hibernate 配置文件
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2011/2/17 ?W?? 10:18:34 by Hibernate Tools 3.2.4.GA -->
<hibernate-mapping>
    <class name="com.sae.twms.entity.TcExamItem" table="tc_exam_item" catalog="cgs">
        <id name="id" type="string">
            <column name="ID" length="30" />
            <generator class="assigned" />
        </id>
        <many-to-one name="tcItemStatus" class="com.sae.twms.entity.TcItemStatus" fetch="select">
            <column name="STATUS" length="30" not-null="true" />
        </many-to-one>
        <many-to-one name="tcItemUnit" class="com.sae.twms.entity.TcItemUnit" fetch="select">
            <column name="unit" length="50" />
        </many-to-one>
        <many-to-one name="tcItemCountry" class="com.sae.twms.entity.TcItemCountry" fetch="select">
            <column name="COUNTRY" length="30" />
        </many-to-one>
        <property name="modelNumber" type="string">
            <column name="MODEL_NUMBER" length="100" not-null="true" />
        </property>
        <property name="description" type="string">
            <column name="DESCRIPTION" length="1000" />
        </property>
        <property name="manufaturer" type="string">
            <column name="MANUFATURER" length="200" />
        </property>
        <property name="lead" type="string">
            <column name="LEAD" length="1500" />
        </property>
        <property name="eccnCode" type="string">
            <column name="ECCN_CODE" length="200" />
        </property>
        <property name="hsCode" type="string">
            <column name="HS_CODE" length="200" />
        </property>
        <property name="ctlItemFlag" type="string">
            <column name="CTL_ITEM_FLAG" length="1" />
        </property>
        <property name="notes" type="string">
            <column name="NOTES" length="1000" />
        </property>
        <property name="refNo" type="string">
            <column name="REF_NO" length="30" not-null="true" />
        </property>
        <property name="currenthandler" type="string">
            <column name="CURRENTHANDLER" length="30" />
        </property>
        <property name="createBy" type="string">
            <column name="CREATE_BY" length="30" not-null="true" />
        </property>
        <property name="createDate" type="timestamp">
            <column name="CREATE_DATE" length="19" not-null="true" />
        </property>
        <property name="lastUpdateBy" type="string">
            <column name="LAST_UPDATE_BY" length="30" not-null="true" />
        </property>
        <property name="lastUpdateDate" type="timestamp">
            <column name="LAST_UPDATE_DATE" length="19" not-null="true" />
        </property>
        <property name="userLocation" type="string">
            <column name="UserLocation" length="200" not-null="true" />
        </property>
        <property name="userName" type="string">
            <column name="UserName" length="200" not-null="true" />
        </property>
        <property name="userDepartment" type="string">
            <column name="UserDepartment" length="200" not-null="true" />
        </property>
        <property name="userTel" type="string">
            <column name="UserTel" length="200" not-null="true" />
        </property>
        <property name="userFax" type="string">
            <column name="UserFax" length="200" />
        </property>
        <property name="userEmail" type="string">
            <column name="UserEmail" length="200" not-null="true" />
        </property>
        <property name="purName" type="string">
            <column name="PurName" length="200" not-null="true" />
        </property>
        <property name="purDepartment" type="string">
            <column name="PurDepartment" length="200" not-null="true" />
        </property>
        <property name="purTel" type="string">
            <column name="PurTel" length="200" not-null="true" />
        </property>
        <property name="purFax" type="string">
            <column name="PurFax" length="200" />
        </property>
        <property name="purEmail" type="string">
            <column name="PurEmail" length="200" not-null="true" />
        </property>
        <property name="supCountry" type="string">
            <column name="SupCountry" length="200" not-null="true" />
        </property>
        <property name="supName" type="string">
            <column name="SupName" length="200" not-null="true" />
        </property>
        <property name="supCompany" type="string">
            <column name="SupCompany" length="200" not-null="true" />
        </property>
        <property name="supTel" type="string">
            <column name="SupTel" length="200" not-null="true" />
        </property>
        <property name="supFax" type="string">
            <column name="SupFax" length="200" />
        </property>
        <property name="supEmail" type="string">
            <column name="SupEmail" length="200" not-null="true" />
        </property>
        <property name="supWebaddress" type="string">
            <column name="SupWebaddress" length="300" />
        </property>
        <property name="userAdditionalInfo" type="string">
            <column name="UserAdditionalInfo" length="1500" />
        </property>
        <property name="tcAdditionalInfo" type="string">
            <column name="TcAdditionalInfo" length="1500" />
        </property>
        <property name="tcgroup" type="string">
            <column name="tcgroup" length="50" />
        </property>
        <set name="tcExamItemResults" inverse="true" lazy="true" table="tc_exam_item_result" fetch="select">
            <key>
                <column name="BIZDOC_ID" length="30" not-null="true" />
            </key>
            <one-to-many class="com.sae.twms.entity.TcExamItemResult" />
        </set>
        <set name="tcLicItems" inverse="true" lazy="true" table="tc_lic_item" fetch="select">
            <key>
                <column name="BIZDOC_ID" length="30" not-null="true" />
            </key>
            <one-to-many class="com.sae.twms.entity.TcLicItem" />
        </set>
        <set name="tcItemPlantcodes" inverse="true" lazy="true" table="tc_item_plantcode" fetch="select">
            <key>
                <column name="ItemId" length="30" not-null="true" />
            </key>
            <one-to-many class="com.sae.twms.entity.TcItemPlantcode" />
        </set>
        <set name="tcProcessLogs" inverse="true" lazy="true" table="tc_process_log" fetch="select">
            <key>
                <column name="BIZDOC_ID" length="30" not-null="true" />
            </key>
            <one-to-many class="com.sae.twms.entity.TcProcessLog" />
        </set>
        <set name="tcItemAttachs" inverse="true" lazy="true" table="tc_item_attach" fetch="select">
            <key>
                <column name="BIZDOC_ID" length="30" not-null="true" />
            </key>
            <one-to-many class="com.sae.twms.entity.TcItemAttach" />
        </set>
    </class>
</hibernate-mapping>
程序出错:15:36:27,498 INFO  [STDOUT] WinTcExamItemMgmt.onClick$btnQuery() Script error:org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 91 [select distinct d from com.sae.twms.entity.TcExamItem d left outer join TcItemPlantcode e on d.id=e.tcExamItem.id left outer join TcExamItemResult f on d.id=f.tcExamItem.id  where   d.tcItemStatus.statusId='111' and upper(e.partno) like '%PN-101-A%' and upper(f.lawHk) like '%HK%']

Answer 3

你只给出HQL语句 你是用什么执行的 ？session.createQuery么？