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

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Criteria query returning single duplicate row
PostPosted: Fri Oct 06, 2006 10:31 am 
Newbie

Joined: Fri Oct 06, 2006 10:15 am
Posts: 3
I am using criteria to build a dynamic sql statement dependent on the presence of incoming parameters. I run the query and it returns the correct data except for a single duplication of the very first record returned. Here's the particulars. Also, I noticed that if you do not createCriteria on dependant objects, they will not be autopopulated by the query. Any help would be greatly appreciated.

Hibernate version: 3.1.3
Mapping documents:
Code:
<class name="CustomerTransaction">
      <id name="customerTransactionId" type="string" unsaved-value="null" >
         <column name="customerTransactionId" sql-type="char(32)" not-null="true"/>
         <generator class="uuid.hex"/>
      </id>
        <timestamp name="updateTimestamp"/>
        <many-to-one name="salon" class="Salon" column="salonId"/>
        <property name="date"/>
        <many-to-one name="customer" class="Customer" column="customerId"/>
        <set name="lineItems">
             <key column="customerTransactionId"/>
           <one-to-many class="CustomerTransactionLineItem"/>
       </set>
       <set name="payments">
             <key column="customerTransactionId"/>
           <one-to-many class="CustomerTransactionPayment"/>
       </set>
   </class>


Code between sessionFactory.openSession() and session.close():
Code:
public Collection<CustomerTransaction> findBySalonIdDateRangeEmployeeIdCustomerId(long salonId, Date startDate, Date endDate, long employeeId, String customerId){
      
      Criteria crit = getSession().createCriteria(CustomerTransaction.class);
      crit.add(Restrictions.between("date", startDate, endDate));
      crit.createCriteria("salon").add(Restrictions.eq("salonId", salonId)).createCriteria("salonHours");
      if (employeeId > 0){
         crit.createCriteria("lineItems").createCriteria("employee").add(Restrictions.eq("employeeId", employeeId));
      }
      if (customerId != null && !customerId.equals("")){
         crit.createCriteria("customer").add(Restrictions.eq("customerId", customerId));
      }
      crit.addOrder(Order.asc("date"));
      return (Collection<CustomerTransaction>)crit.list();
      
   }


Name and version of the database you are using: MySQL 5.0

The generated SQL (show_sql=true):
Code:
Hibernate: select this_.customerTransactionId as customer1_2_4_, this_.updateTimestamp as updateTi2_2_4_, this_.salonId as salonId2_4_, this_.date as date2_4_, this_.customerId as customerId2_4_, salon1_.salonId as salonId14_0_, salon1_.updateTimestamp as updateTi2_14_0_, salon1_.name as name14_0_, salon1_.description as descript4_14_0_, salon1_.address as address14_0_, salon1_.city as city14_0_, salon1_.state as state14_0_, salon1_.zip as zip14_0_, salon1_.phone as phone14_0_, salon1_.fax as fax14_0_, salon1_.email as email14_0_, salon1_.url as url14_0_, salon1_.salesTaxRate as salesTa13_14_0_, salonhours2_.salonId as salonId15_1_, salonhours2_.sundayOpen as sundayOpen15_1_, salonhours2_.sundayClose as sundayCl3_15_1_, salonhours2_.mondayOpen as mondayOpen15_1_, salonhours2_.mondayClose as mondayCl5_15_1_, salonhours2_.tuesdayOpen as tuesdayO6_15_1_, salonhours2_.tuesdayClose as tuesdayC7_15_1_, salonhours2_.wednesdayOpen as wednesda8_15_1_, salonhours2_.wednesdayClose as wednesda9_15_1_, salonhours2_.thursdayOpen as thursda10_15_1_, salonhours2_.thursdayClose as thursda11_15_1_, salonhours2_.fridayOpen as fridayOpen15_1_, salonhours2_.fridayClose as fridayC13_15_1_, salonhours2_.saturdayOpen as saturda14_15_1_, salonhours2_.saturdayClose as saturda15_15_1_, salonhours2_.timezone as timezone15_1_, customertr3_.customerTransactionLineItemId as customer1_3_2_, customertr3_.updateTimestamp as updateTi2_3_2_, customertr3_.customerTransactionId as customer3_3_2_, customertr3_.employeeId as employeeId3_2_, customertr3_.saleItemId as saleItemId3_2_, customertr3_.quantity as quantity3_2_, customertr3_.actualPrice as actualPr7_3_2_, employee4_.employeeId as employeeId5_3_, employee4_.updateTimestamp as updateTi2_5_3_, employee4_.employeeNumber as employee3_5_3_, employee4_.employeeTypeId as employee4_5_3_, employee4_.employeeStatusId as employee5_5_3_, employee4_.salonId as salonId5_3_, employee4_.firstName as firstName5_3_, employee4_.middleName as middleName5_3_, employee4_.lastName as lastName5_3_, employee4_.address as address5_3_, employee4_.city as city5_3_, employee4_.state as state5_3_, employee4_.zip as zip5_3_, employee4_.homePhone as homePhone5_3_, employee4_.cellPhone as cellPhone5_3_, employee4_.email as email5_3_, employee4_.password as password5_3_ from CustomerTransaction this_ inner join Salon salon1_ on this_.salonId=salon1_.salonId inner join SalonHours salonhours2_ on salon1_.salonId=salonhours2_.salonId inner join CustomerTransactionLineItem customertr3_ on this_.customerTransactionId=customertr3_.customerTransactionId inner join Employee employee4_ on customertr3_.employeeId=employee4_.employeeId where this_.date between ? and ? and salon1_.salonId=? and employee4_.employeeId=? order by this_.date asc
[/code]


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 11, 2006 7:04 am 
Newbie

Joined: Wed Oct 11, 2006 7:03 am
Posts: 1
I am experiencing the same issue with a timestamp column.

did you ever find the cause?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 11, 2006 10:01 am 
Newbie

Joined: Fri Oct 06, 2006 10:15 am
Posts: 3
spennine wrote:
I am experiencing the same issue with a timestamp column.

did you ever find the cause?


Nope. Still waiting for an answer.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Nov 05, 2006 12:43 pm 
Newbie

Joined: Fri Oct 06, 2006 10:15 am
Posts: 3
bump


Top
 Profile  
 
 Post subject:
PostPosted: Sun Nov 05, 2006 1:14 pm 
Senior
Senior

Joined: Tue Mar 09, 2004 2:38 pm
Posts: 141
Location: Lowell, MA USA
Have you tried executing the generated SQL to see if your getting back the same results outside of Hibernate?

Ryan-

_________________
Ryan J. McDonough
http://damnhandy.com

Please remember to rate!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

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.