Criteria - between dates

iamvraj · **Joined:** Mon May 09, 2005 5:50 am **Posts:** 6

Hi,

I am using hibernate with postgres.
I have a POJO as follows;

Code:

  public class UserAccount {
    ....
    private java.util.Date createdTime;
    ....
  }

The hibenrate mapping for this field is as follows;

Code:

  <property name="createdTime" type="timestamp">
     <column name="createdTime" not-null="true" />
  </property>

the data type of this field in the database is TimeStamp.

I want to find the useraccounts between two given dates. I am building criteria as follows;

Code:

Criteria criteria = session.createCriteria(UserAccount.class);
criteria.add(Expression.between("createdTime", fromDate, toDate));
List userAccountList = criteria.list();

where fromDate and toDate are objects of java.util.Date.

But this is always returning empty list for me. Can anyone tell me where am I wrong? The between expression is working for me with all other data types.

Thanks in Advance..!!

Cheers,
Vraj

ramnnath_82 · **Posted:** Thu May 26, 2005 12:46 am

Hi

Look at
<property name="createdTime" type="timestamp">

instead it should be

<property name="createdTime" type="java.util.Date">

The type in the property is w.r.t to the Persistent Class.

I have used it and it worked well for me.

iamvraj · **Joined:** Mon May 09, 2005 5:50 am **Posts:** 6

Hi Ram,

I changed the mapping as per your suggestion. But still the list is empty.

From the log, I saw the query as follows. When I gave the same query in the db client, I got 5 records. Probably hibernate is setting the dates in a wrong way. But hibernate is showing only the query in the log but not the values. Is there a way to make hibernate log the values of PreparedStatement?

If it doesnt trouble you, can you send me your example?

Query from My Log

Code:

select this.userAccountId as userAcco1_3_, this.ownerPersonId as ownerPer2_3_, this.smartCode as smartCode3_, this.password as password3_, this.createdTime as createdT5_3_, this.createdPersonId as createdP6_3_, this.residenceCountry as residenc7_3_, this.bankCode as bankCode3_, this.secretQuestion as secretQu9_3_, this.secretAnswer as secretA10_3_, this.mobileCountryCode as mobileC11_3_, this.mobileNumber as mobileN12_3_, this.mobilePin as mobilePin3_, this.pinStatus as pinStatus3_, this.activateSecurityScreen as activat15_3_, this.lastModifiedTime as lastMod16_3_, this.lastModifiedPersonId as lastMod17_3_, this.previousLoginTime as previou18_3_, this.lastLoginTime as lastLog19_3_, this.passwordTries as passwor20_3_, this.closeDate as closeDate3_, this.closeRemarks as closeRe22_3_, this.loginStatus as loginSt23_3_, person1_.personId as personId0_, person1_.fullName as fullName0_, person1_.dateOfBirth as dateOfBi3_0_, person1_.residenceCountry as residenc4_0_, person1_.gender as gender0_, person1_.personalTitle as personal6_0_, person1_.createdTime as createdT7_0_, person1_.createdPersonId as createdP8_0_, person1_.lastModifiedTime as lastModi9_0_, person1_.lastModifiedPersonId as lastMod10_0_, person1_.remarks as remarks0_, person1_.personStatus as personS12_0_, person2_.personId as personId1_, person2_.fullName as fullName1_, person2_.dateOfBirth as dateOfBi3_1_, person2_.residenceCountry as residenc4_1_, person2_.gender as gender1_, person2_.personalTitle as personal6_1_, person2_.createdTime as createdT7_1_, person2_.createdPersonId as createdP8_1_, person2_.lastModifiedTime as lastModi9_1_, person2_.lastModifiedPersonId as lastMod10_1_, person2_.remarks as remarks1_, person2_.personStatus as personS12_1_, person3_.personId as personId2_, person3_.fullName as fullName2_, person3_.dateOfBirth as dateOfBi3_2_, person3_.residenceCountry as residenc4_2_, person3_.gender as gender2_, person3_.personalTitle as personal6_2_, person3_.createdTime as createdT7_2_, person3_.createdPersonId as createdP8_2_, person3_.lastModifiedTime as lastModi9_2_, person3_.lastModifiedPersonId as lastMod10_2_, person3_.remarks as remarks2_, person3_.personStatus as personS12_2_ from UserAccounts this left outer join Persons person1_ on this.ownerPersonId=person1_.personId left outer join Persons person2_ on this.createdPersonId=person2_.personId left outer join Persons person3_ on this.lastModifiedPersonId=person3_.personId where this.createdTime between ? and ?

ramnnath_82 · **Posted:** Thu May 26, 2005 5:21 am

Hi

Create and another Date for To
public class UserAccount {
....
private java.util.Date createdTime;
private java.util.Date createdTimeTo; // This is for toDate
....
}

Dont make any entries for this "createdTimeTo" in the hbm file.

Now in the java class
Criteria criteria = session.createCriteria(UserAccount.class);
criteria.add(Expression.between("createdTime", info.getCreateTime(), info.getCreateTimeTo()));
List userAccountList = criteria.list();

Now check it out you will see the results.

Regards
Ramnath