-->
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
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
romanonj
 Post subject: Hibernate returns different result set from SQL - any ideas?
PostPosted: Tue Apr 19, 2005 3:04 pm 
Newbie

Joined: Mon Jul 26, 2004 11:35 am
Posts: 13
Read the rules before posting!
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hi,
Hopefully you can help me with this nightmare!!!!
SQL Below does not return any records when hibernate executes, however if I execute sql in Toad it returns 4 Rows. Very strange. Please help, I need this fixed for a production reporting system.
Thank you.

Code:
HQL Code ->
DatabaseUtil.beginSession();
      StringBuffer hql = new StringBuffer();
      hql.append("select item.literature.model.category.title,sum(item.quantity) ");
      hql.append("from DealerOrderItem as item ");
      hql.append("where item.order.status='I' ");
      if(!StringUtil.isEmpty(startDate))
        hql.append("and item.updated >= :startDate ");
      if(!StringUtil.isEmpty(endDate))
        hql.append("and item.updated <= :endDate ");
                   
      //add group by and order by clause
      hql.append("group by item.literature.model.category.title ");
      hql.append("order by item.literature.model.category.title asc");
     
      //create query
      Query q= DatabaseUtil.createQuery(hql.toString());
     
      //set query parameters
      if(!StringUtil.isEmpty(startDate) && !StringUtil.isEmpty(endDate))
      {
        q.setString("startDate",startDate);
        q.setString("endDate",endDate);
      }
      else
      {
        if(!StringUtil.isEmpty(startDate))
          q.setString("startDate",startDate);
        else if(!StringUtil.isEmpty(endDate))
          q.setString("endDate",endDate);
      }
     
      //list result
      results = q.list();


Hibernate version 2.1.8:

Oracle 10g

Code:
select category4_.title as x0_0_, sum(dealerorde0_.QUANITY) as x1_0_ from DLR_ORDER_ITEM dealerorde0_, DLR_ORDER dealerorde1_, LITERATURE_MASTER literature2_, ITEM_MASTER model3_, ITEM_CATEGORY category4_ where dealerorde0_.LIT_ID=literature2_.LIT_ID and literature2_.MODEL=model3_.MODEL and model3_.CATEGORY=category4_.CATEGORY and ((dealerorde1_.STATUS='I'  and dealerorde0_.ORDER_ID=dealerorde1_.ORDER_ID)and(dealerorde0_.UPDATED<=? )) group by  category4_.title order by  category4_.title asc


Code:
19 Apr 2005 14:52:49,878 DEBUG SessionImpl.find:1537 - find: select item.literature.model.category.title,sum(item.quantity) from DealerOrderItem as item where item.order.status='I' and item.updated <= :endDate group by item.literature.model.category.title order by item.literature.model.category.title asc
19 Apr 2005 14:52:49,880 DEBUG QueryParameters.traceParameters:122 - named parameters: {endDate=21-APR-2005}
19 Apr 2005 14:52:49,881 DEBUG SessionImpl.flushEverything:2267 - flushing session
19 Apr 2005 14:52:49,883 DEBUG SessionImpl.flushEntities:2467 - Flushing entities and processing referenced collections
19 Apr 2005 14:52:49,884 DEBUG SessionImpl.flushCollections:2808 - Processing unreferenced collections
19 Apr 2005 14:52:49,886 DEBUG SessionImpl.flushCollections:2822 - Scheduling collection removes/(re)creates/updates
19 Apr 2005 14:52:49,888 DEBUG SessionImpl.flushEverything:2291 - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
19 Apr 2005 14:52:49,889 DEBUG SessionImpl.flushEverything:2296 - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
19 Apr 2005 14:52:49,891 DEBUG SessionImpl.autoFlushIfRequired:1828 - Dont need to execute flush
19 Apr 2005 14:52:49,892 DEBUG QueryTranslator.logQuery:207 - HQL: select item.literature.model.category.title,sum(item.quantity) from com.crestec.factory.beans.DealerOrderItem as item where item.order.status='I' and item.updated <= :endDate group by item.literature.model.category.title order by item.literature.model.category.title asc
19 Apr 2005 14:52:49,894 DEBUG QueryTranslator.logQuery:208 - SQL: select category4_.title as x0_0_, sum(dealerorde0_.QUANITY) as x1_0_ from DLR_ORDER_ITEM dealerorde0_, DLR_ORDER dealerorde1_, LITERATURE_MASTER literature2_, ITEM_MASTER model3_, ITEM_CATEGORY category4_ where dealerorde0_.LIT_ID=literature2_.LIT_ID and literature2_.MODEL=model3_.MODEL and model3_.CATEGORY=category4_.CATEGORY and ((dealerorde1_.STATUS='I'  and dealerorde0_.ORDER_ID=dealerorde1_.ORDER_ID)and(dealerorde0_.UPDATED<=? )) group by  category4_.title order by  category4_.title asc
19 Apr 2005 14:52:49,895 DEBUG BatcherImpl.logOpenPreparedStatement:204 - about to open: 0 open PreparedStatements, 0 open ResultSets
19 Apr 2005 14:52:49,897 DEBUG BatcherImpl.getPreparedStatement:253 - preparing statement
19 Apr 2005 14:52:49,903 DEBUG Loader.doQuery:281 - processing result set
19 Apr 2005 14:52:49,904 DEBUG Loader.doQuery:298 - done processing result set (0 rows)
19 Apr 2005 14:52:49,906 DEBUG BatcherImpl.logClosePreparedStatement:211 - done closing: 0 open PreparedStatements, 0 open ResultSets
19 Apr 2005 14:52:49,908 DEBUG BatcherImpl.closePreparedStatement:275 - closing statement:
Top
 Profile  
 
romanonj
 Post subject:
PostPosted: Tue Apr 19, 2005 3:37 pm 
Newbie

Joined: Mon Jul 26, 2004 11:35 am
Posts: 13
Posting entire or-mapping.hbm.xml file.
Thank you in advance
Sincerely Roman

Code:
<hibernate-mapping package="com.crestec.factory.beans">
 
        <class name="Dealer" table="dlr_dealers">
                <id name="dealerId" column="dealer_id" type="string" unsaved-value="id_value">
                  <generator class="assigned"/>
                </id>
                <property name="name" type="string"/>
                <property name="address1" type="string"/>
                <property name="address2" type="string"/>
                <property name="city" type="string"/>
                <property name="state" type="string"/>
                <property name="zip" type="string"/>
                <property name="country" type="string"/>
                <property name="phone" type="string"/>
                <property name="fax" type="string"/>
                <!-- took out shipping reference to allow one to many relationship-->
                <many-to-one name="ship"
                             column="SHIP_CODE"
                             class="DealerShipping"
                             cascade="save-update"
                             outer-join="false"
                             update="true"
                             property-ref="shipCode"
                             unique="true"/>
                <property name="creditHold" column="CREDIT_HOLD" type="character"/>
                <property name="status" type="character"/>
                <property name="cancelDate" column="DATE_CANCELLED" type="string"/>
                <set name="orders" lazy="true" inverse="true" order-by="UPDATED ASC" cascade="none">
                    <key column="dealer_id"/>
                    <one-to-many class="DealerOrder"/>
                </set>
        </class> 

        <class name="DealerShipping" table="dlr_shipping">
                <id name="shipId" column="ship_id" type="int" unsaved-value="0">
                  <generator class="sequence">
                    <param name="sequence">dlr_shipping_seq</param>
                  </generator>
                </id>
                <property name="shipCode" column="ship_code" type="string"/>
                <property name="description" type="string"/>
                <property name="active" type="yes_no"/>
               
                <!-- list of dealers this applies to -->
                <set name="dealers" table="DLR_SHIPPING_DEALERS">
                    <key column="ship_id"/>
                    <element column="dealer_id" type="string"/>
                </set>
               
                <!-- list of states this applies to -->
                <set name="states" table="DLR_SHIPPING_STATES">
                    <key column="ship_id"/>
                    <element column="state" type="string"/>
                </set>
      </class>

      <class name="Category" table="ITEM_CATEGORY">
                <id name="code" column="CATEGORY" type="string">
                  <generator class="assigned"/>
                </id>
                <property name="title" type="string"/>
                <property name="image" type="string"/>
                <property name="notes" type="string"/>
      </class>

      <class name="LiteratureInfo" table="INVENTORY">
                <id name="number" column="LIT_NUMBER" type="string">
                  <generator class="assigned"/>
                </id>
                <property name="type" column="MANUAL_TYPE" type="string"/>
                <property name="retailPrice" column="PRICE" type="double"/>
                <property name="wholesalePrice" column="WHOLESALE_PRICE" type="double"/>
                <property name="quantity" type="int"/>
                <property name="dealerOnly" column="DEALER_ONLY" type="yes_no"/>
                <property name="active" column="ACTIVE_FLAG" type="char"/>
                <property name="created" column="CREATED_DATE" type="string"/>
                <property name="updated" column="LAST_UPDATE_DATE" type="string"/>
      </class>

      <class name="Model" table="ITEM_MASTER">
                <id name="name" column="MODEL" type="string">
                  <generator class="assigned"/>
                </id>
                <property name="family" column="MODEL_NAME" type="string"/>
                <many-to-one name="category"
                             column="CATEGORY"
                             class="Category"
                             cascade="save-update"
                             outer-join="false"
                             update="true"
                             unique="true"/>
                <property name="created" column="CREATED_DATE" type="string"/>
      </class>

      <class name="Literature" table="LITERATURE_MASTER">
                <id name="id" column="LIT_ID" type="long" unsaved-value="0">
                  <generator class="sequence">
                    <param name="sequence">LITERATURE_MASTER_SEQ</param>
                  </generator>
                </id>
                <many-to-one name="litInfo"
                             column="LIT_NUMBER"
                             class="LiteratureInfo"
                             cascade="none"
                             outer-join="false"
                             update="false"
                             unique="true"/>
                <many-to-one name="model"
                             column="MODEL"
                             class="Model"
                             cascade="none"
                             outer-join="false"
                             update="false"
                             unique="true"/>
              <property name="year" type="string"/>
              <property name="created" column="CREATED_DATE" type="string"/>
             <property name="discontinued" type="string" update="false" insert="false"/>
      </class>

      <!-- shopping cart object -->
      <class name="DealerOrder" table="DLR_ORDER">
                <id name="orderId" column="ORDER_ID" type="long" unsaved-value="0">
                  <generator class="sequence">
                    <param name="sequence">dlr_order_seq</param>
                  </generator>
                </id>
                <property name="orderNumber" column="ORDER_NUMBER" type="string"/>
                <many-to-one name="dealer"
                             column="DEALER_ID"
                             class="Dealer"
                             cascade="none"
                             outer-join="true"
                             update="false"
                             insert="false"
                             unique="true"/>
                <property name="attention" column="ATTENTION" type="string"/>
                <property name="poNumber" column="PO_NUMBER" type="string"/>
                <property name="status" column="STATUS" type="char"/>
                <many-to-one name="dealerTransaction"
                             column="ORDER_NUMBER"
                             class="DealerTransaction"
                             cascade="none"
                             outer-join="true"
                             update="false"
                             insert="false"
                             unique="true"/>
                <property name="updated" column="UPDATED" type="string" update="false" insert="false"/>
                <property name="orderSent" column="ORDER_SENT" type="string" update="false" insert="false"/>
               
                <set name="orderItems" lazy="false" inverse="true">
                    <key column="ORDER_ID"/>
                    <one-to-many class="DealerOrderItem"/>
                </set>
      </class>

      <class name="DealerOrderItem" table="DLR_ORDER_ITEM">
                <id name="orderItemId" column="ORDER_ITEM_ID" type="long" unsaved-value="0">
                  <generator class="sequence">
                    <param name="sequence">dlr_order_item_seq</param>
                  </generator>
                </id>
                <many-to-one name="order"
                             column="ORDER_ID"
                             class="DealerOrder"
                             cascade="none"
                             outer-join="true"
                             update="true"
                             insert="true"
                             unique="true"/>
                <many-to-one name="literature"
                             column="LIT_ID"
                             class="Literature"
                             cascade="none"
                             outer-join="false"
                             update="true"
                             insert="true"
                             unique="true"/>
                <property name="purchasePrice" column="PURCHASE_PRICE" type="double"/>
                <property name="quantity" column="QUANITY" type="int"/>
                <property name="updated" column="UPDATED" type="string" update="false" insert="false"/>
      </class>

      <class name="DealerTransaction" table="dlr_transaction">
                <id name="orderNumber" column="order_number" type="string" unsaved-value="id_value">
                  <generator class="assigned"/>
                </id>
                <property name="orderTotal" column="order_total" type="double"/>
                <property name="email" type="string"/>
                <property name="description" type="string"/>
                <property name="name" type="string"/>
                <property name="address1" type="string"/>
                <property name="address2" type="string"/>
                <property name="city" type="string"/>
                <property name="state" type="string"/>
                <property name="zip" type="string"/>
                <property name="country" type="string"/>
                <property name="phone" type="string"/>
                <property name="fax" type="string"/>
                <many-to-one name="shipping"
                             column="ship_code"
                             class="DealerShipping"
                             cascade="save-update"
                             outer-join="false"
                             update="true"
                             property-ref="shipCode"
                             unique="true"/>
                <property name="updated" column="UPDATED" type="string" update="false" insert="false"/>
      </class>
     
</hibernate-mapping>
[/code]
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
  [ 2 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.