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]