Help with HQL Query please !!!!

knoxor · **Joined:** Fri Oct 06, 2006 7:11 am **Posts:** 32

Hi,

I'm looking for some help on how to write a HQL query on my database. I have the SQL needed to get my results but don't know how I should write this in HQL.

I don't think I need to supply the hbm.xml files but if you think it may be necessary then I will do that.

Any Help would be greatly appreciated.

I have the following tables

TABLE DPCS_STATEMENT_TITLE
(
STATEMENT_TITLE_ID NUMBER(19)
STATEMENT_TITLE VARCHAR2(255)
LANGUAGE_ID NUMBER(19)
VET_CERT NUMBER(1)
SELECTABLE VARCHAR2(255)
)

TABLE DPCS_STATEMENT
(
STATEMENT_ID NUMBER(19)
STATEMENT VARCHAR2(255)
STATEMENT_CODE VARCHAR2(255)
LANGUAGE_ID NUMBER(19)
)

And the table that links them together is:

TABLE DPCS_STATEMEMT_TITLE_LINKS
(
STATEMENT_TITLE_ID NUMBER(19)
STATEMENT_ID NUMBER(19)
)

I want to run this SQL as HQL

select t1.STATEMENT_ID from dpcs_statement t1, DPCS_STATEMEMT_TITLE_LINKS t2
where t2.STATEMENT_ID = t1.STATEMENT_ID
and t2.statement_title_id = ? and t1.LANGUAGE_ID = ?

Can anyone help ?

thanks

MikePloed · **Posted:** Thu Nov 09, 2006 7:27 am

Since HQL is object oriented, you need to post your mappings please

knoxor · **Joined:** Fri Oct 06, 2006 7:11 am **Posts:** 32

Hi,

Mapping files are below

thanks

StatementTitle.hbm.xml
=================================

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="ie.gov.agriculture.dpcs.cert.StatementTitle" lazy="false" table="DPCS_STATEMENT_TITLE">
       <id name="statementTitleId" column="STATEMENT_TITLE_ID" unsaved-value="0" type="long">
           <generator class="native"/>
       </id>
        <property name="title" column="STATEMENT_TITLE" type="string"/>
        <property name="selectable" column="SELECTABLE" type="string"/>

      <many-to-one name="certLanguage"
                column="LANGUAGE_ID"/>
                
         <set name="statements" table="DPCS_STATEMEMT_TITLE_LINKS" cascade="all">
          <key column="STATEMENT_TITLE_ID"/>
          <many-to-many column="STATEMENT_ID" 
                        class="ie.gov.agriculture.dpcs.cert.Statement"/>
      </set>

    </class>
   <query name="statement.title.list">
      from StatementTitle StatementTitle
   </query>
</hibernate-mapping>

Statement.hbm.xml
==================================

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="ie.gov.agriculture.dpcs.cert.Statement" lazy="false" table="DPCS_STATEMENT">
       <id name="statementId" column="STATEMENT_ID" unsaved-value="0" type="long">
           <generator class="native"/>
       </id>
        <property name="statement" column="STATEMENT" type="string"/>
        <property name="statementCode" column="STATEMENT_CODE" type="string"/>
            
      <many-to-one name="certLanguage"
               column="LANGUAGE_ID"/>
    </class>
   <query name="statement.list">
      from Statement Statement
   </query>
   
</hibernate-mapping>