-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
aturetsky
 Post subject: how do I translate this in to HQL?
PostPosted: Mon Aug 08, 2005 1:06 pm 
Regular
Regular

Joined: Fri Jan 28, 2005 3:11 am
Posts: 81
Here is my SQL query:

Code:
     select count(*) from sdt_program_table s

         inner join idms_part_prog_table p on s.program_id = p.Program_id

         inner join idms_schedule_table i on p.part_id = i.part_id

         where s.model = '777'

         and i.status = 'OPEN'

         and i.supplier_due_date between TO_DATE('01-JAN-2005', 'DD-MON-YYYY') and TO_DATE('31-JAN-2005', 'DD-MON-YYYY');




This returns COUNT(*) = 79



Now I need to rewrite this into HQL. However, when I try to do this, it doesn’t come back with the correct number. I could get the correct results when I split it apart and tested each join one at a time. But when I try to put the query all together I get like back a number like 14440404. Here is my HQL query.



Code:
Query query = session.createQuery(

            "select count(*) " +

            "from com.dao.SdtProgramTable sdtProgramTable, IdmsScheduleTable idmsScheduleTable " +

            "join sdtProgramTable.idmsPartProgTableSet " +

            "join idmsScheduleTable.idmsPartProgTableSet " +

            "where idmsScheduleTable.status = :open " +

            "and sdtProgramTable.model = :model " +

            "and idmsScheduleTable.supplierDueDate between TO_DATE(:startDate, 'DD-MON-YYYY') and TO_DATE(:endDate, 'DD-MON-YYYY') ");



   query.setParameter("model", model);

   query.setParameter("open", "OPEN");

   query.setParameter("startDate", startDate);

   query.setParameter("endDate", endDate);

   List countList = query.list();




Any ideas? I bet my hql is really messed up.

Here are my mapping files:

Code:
<hibernate-mapping package="com.dao">

    <class name="SdtProgramTable" table="SDT_PROGRAM_TABLE">
        <id name="programId" column="PROGRAM_ID" type="java.lang.Integer">
            <generator class="assigned"/>
        </id>

        <property name="model" column="MODEL" type="java.lang.String"  not-null="true" />
        <property name="derivative" column="DERIVATIVE" type="java.lang.String" />

        <set name="idmsPartProgTableSet" inverse="true">
            <key column="PROGRAM_ID"/>
            <one-to-many class="IdmsPartProgTable"/>
        </set>

     
    </class>
   
</hibernate-mapping>

<hibernate-mapping package="com.dao">

    <class name="IdmsPartProgTable" table="IDMS_PART_PROG_TABLE">
        <composite-id name="id" class="IdmsPartProgTableKey">
            <key-many-to-one name="idmsScheduleTable" column="PART_ID" class="IdmsScheduleTable"/>
            <key-many-to-one name="sdtProgramTable" column="PROGRAM_ID" class="SdtProgramTable"/>
        </composite-id>

    </class>
   
</hibernate-mapping>

<hibernate-mapping package="com.dao">

    <class name="IdmsScheduleTable" table="IDMS_SCHEDULE_TABLE">
        <id name="partId" column="PART_ID" type="java.lang.Integer">
            <generator class="assigned"/>
        </id>

        <property name="sdrlId" column="SDRL_ID" type="java.lang.Integer"  not-null="true" />
        <property name="scheduleId" column="SCHEDULE_ID" type="java.lang.Integer"  not-null="true" />
        <property name="openDate" column="OPEN_DATE" type="java.util.Date" />
        <property name="closeDate" column="CLOSE_DATE" type="java.util.Date" />
        <property name="status" column="STATUS" type="java.lang.String" />
        <property name="supplierEstDueDate" column="SUPPLIER_EST_DUE_DATE" type="java.util.Date" />
        <property name="supplierDueDate" column="SUPPLIER_DUE_DATE" type="java.util.Date" />
        <property name="supplierSlipDateCnt" column="SUPPLIER_SLIP_DATE_CNT" type="java.lang.Integer" />
        <property name="supplierCompleteDate" column="SUPPLIER_COMPLETE_DATE" type="java.util.Date" />
        <property name="supplierSig" column="SUPPLIER_SIG" type="java.lang.String" />
        <property name="supplierOrgCode" column="SUPPLIER_ORG_CODE" type="java.lang.String" />
        <property name="supplierStatus" column="SUPPLIER_STATUS" type="java.lang.String" />
        <property name="dataMgmtEstDueDate" column="DATA_MGMT_EST_DUE_DATE" type="java.util.Date" />
        <property name="dataMgmtDueDate" column="DATA_MGMT_DUE_DATE" type="java.util.Date" />
        <property name="dataMgmtSlipDateCnt" column="DATA_MGMT_SLIP_DATE_CNT" type="java.lang.Integer" />
        <property name="dataMgmtCompleteDate" column="DATA_MGMT_COMPLETE_DATE" type="java.util.Date" />
        <property name="dataMgmtSig" column="DATA_MGMT_SIG" type="java.lang.String" />
        <property name="dataMgmtStatus" column="DATA_MGMT_STATUS" type="java.lang.String" />
        <property name="primaryEstDueDate" column="PRIMARY_EST_DUE_DATE" type="java.util.Date" />
        <property name="primaryDueDate" column="PRIMARY_DUE_DATE" type="java.util.Date" />
        <property name="primarySlipDateCnt" column="PRIMARY_SLIP_DATE_CNT" type="java.lang.Integer" />
        <property name="primaryCompleteDate" column="PRIMARY_COMPLETE_DATE" type="java.util.Date" />
        <property name="primarySig" column="PRIMARY_SIG" type="java.lang.String" />
        <property name="primaryStatus" column="PRIMARY_STATUS" type="java.lang.String" />
        <property name="buyerEstDueDate" column="BUYER_EST_DUE_DATE" type="java.util.Date" />
        <property name="buyerDueDate" column="BUYER_DUE_DATE" type="java.util.Date" />
        <property name="buyerSlipDateCnt" column="BUYER_SLIP_DATE_CNT" type="java.lang.Integer" />
        <property name="buyerCompleteDate" column="BUYER_COMPLETE_DATE" type="java.util.Date" />
        <property name="buyerSig" column="BUYER_SIG" type="java.lang.String" />
        <property name="buyerStatus" column="BUYER_STATUS" type="java.lang.String" />
        <property name="boeingEstDueDate" column="BOEING_EST_DUE_DATE" type="java.util.Date" />
        <property name="boeingDueDate" column="BOEING_DUE_DATE" type="java.util.Date" />
        <property name="boeingCompleteDate" column="BOEING_COMPLETE_DATE" type="java.util.Date" />
        <property name="boeingStatus" column="BOEING_STATUS" type="java.lang.String" />
        <property name="resubmitDate" column="RESUBMIT_DATE" type="java.util.Date" />
        <property name="resubmitScheduleId" column="RESUBMIT_SCHEDULE_ID" type="java.lang.Integer" />
        <property name="dataMgmtEstDueDate2" column="DATA_MGMT_EST_DUE_DATE2" type="java.util.Date" />
        <property name="dataMgmtDueDate2" column="DATA_MGMT_DUE_DATE2" type="java.util.Date" />
        <property name="dataMgmtSlipDateCnt2" column="DATA_MGMT_SLIP_DATE_CNT2" type="java.lang.Integer" />
        <property name="dataMgmtCompleteDate2" column="DATA_MGMT_COMPLETE_DATE2" type="java.util.Date" />
        <property name="dataMgmtSig2" column="DATA_MGMT_SIG2" type="java.lang.String" />
        <property name="dataMgmtStatus2" column="DATA_MGMT_STATUS2" type="java.lang.String" />
        <property name="createHow" column="CREATE_HOW" type="java.lang.String" />
        <property name="createdBy" column="CREATED_BY" type="java.lang.String" />
        <property name="archiveFlag" column="ARCHIVE_FLAG" type="java.lang.String" />
        <property name="milestoneId" column="MILESTONE_ID" type="java.lang.Integer" />
        <property name="eicoSig" column="EICO_SIG" type="java.lang.String" />
        <property name="eicoDueDate" column="EICO_DUE_DATE" type="java.util.Date" />
        <property name="eicoEstDueDate" column="EICO_EST_DUE_DATE" type="java.util.Date" />
        <property name="eicoSlipDateCnt" column="EICO_SLIP_DATE_CNT" type="java.lang.Integer" />
        <property name="eicoStatus" column="EICO_STATUS" type="java.lang.String" />
        <property name="eicoCompleteDate" column="EICO_COMPLETE_DATE" type="java.util.Date" />
        <property name="supplierEcdDate" column="SUPPLIER_ECD_DATE" type="java.util.Date" />
        <property name="supplierEcdComment" column="SUPPLIER_ECD_COMMENT" type="java.lang.String" />
        <property name="supplierEcdCnt" column="SUPPLIER_ECD_CNT" type="java.lang.Integer" />
        <property name="dataMgmtEcdDate" column="DATA_MGMT_ECD_DATE" type="java.util.Date" />
        <property name="dataMgmtEcdComment" column="DATA_MGMT_ECD_COMMENT" type="java.lang.String" />
        <property name="dataMgmtEcdCnt" column="DATA_MGMT_ECD_CNT" type="java.lang.Integer" />
        <property name="primaryEcdDate" column="PRIMARY_ECD_DATE" type="java.util.Date" />
        <property name="primaryEcdComment" column="PRIMARY_ECD_COMMENT" type="java.lang.String" />
        <property name="primaryEcdCnt" column="PRIMARY_ECD_CNT" type="java.lang.Integer" />
        <property name="verifierEcdDate" column="VERIFIER_ECD_DATE" type="java.util.Date" />
        <property name="verifierEcdComment" column="VERIFIER_ECD_COMMENT" type="java.lang.String" />
        <property name="verifierEcdCnt" column="VERIFIER_ECD_CNT" type="java.lang.Integer" />
        <property name="buyerEcdDate" column="BUYER_ECD_DATE" type="java.util.Date" />
        <property name="buyerEcdComment" column="BUYER_ECD_COMMENT" type="java.lang.String" />
        <property name="buyerEcdCnt" column="BUYER_ECD_CNT" type="java.lang.Integer" />

           <set name="idmsPartProgTableSet" inverse="true">
            <key column="PART_ID"/>
            <one-to-many class="IdmsPartProgTable"/>
        </set>
       
    </class>
   
</hibernate-mapping>
Top
 Profile  
 
kgignatyev
 Post subject: why not SQL?
PostPosted: Mon Aug 08, 2005 1:43 pm 
Expert
Expert

Joined: Fri Jul 22, 2005 2:42 pm
Posts: 670
Location: Seattle, WA
Well, you could use your SQL query like this

SQLQuery sqlQ = s.createSQLQuery( "select count(c.id) as cnt from ...... " +
"where .... and i.supplier_due_date between :fromD and :endD ");
sqlQ.addScalar("cnt", Hibernate.LONG);
sqlQ.setDate("fromD", fromDate);
sqlQ.setDate("endD", toDate);

Long o = (Long) sqlQ.uniqueResult();

see also http://forum.hibernate.org/viewtopic.php?t=946046
Top
 Profile  
 
aturetsky
 Post subject:
PostPosted: Mon Aug 08, 2005 1:52 pm 
Regular
Regular

Joined: Fri Jan 28, 2005 3:11 am
Posts: 81
why would you want this:
sqlQ.addScalar("cnt", Hibernate.LONG);

why not leave it an Integer?
Top
 Profile  
 
kgignatyev
 Post subject: int
PostPosted: Mon Aug 08, 2005 2:10 pm 
Expert
Expert

Joined: Fri Jul 22, 2005 2:42 pm
Posts: 670
Location: Seattle, WA
It sure can be integer.
Top
 Profile  
 
aturetsky
 Post subject:
PostPosted: Mon Aug 08, 2005 2:26 pm 
Regular
Regular

Joined: Fri Jan 28, 2005 3:11 am
Posts: 81
anyhow, I didn't want to use SQL, but HQL, so I ended up solving it this way:

Integer count = (Integer) session.createQuery("select count(*) from idmsPartProgTable part where part.sdtProgramTable.id.model = '777' and part.idmsScheduleTable.id.status = 'OPEN' and part.idmsScheduleTable.id.supplierDueDate between :fromDate and :endDate").setDate("fromDate", fromDate).setDate("endDate", endDate).uniqueResult();

This worked.

Thanks for the input, anyway.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.