-->
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
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
Rastas
 Post subject: Criteria Query & Date types.
PostPosted: Mon May 07, 2007 1:16 am 
Newbie

Joined: Tue Aug 01, 2006 4:17 am
Posts: 8
I am using the Criteria query to retrieve filtered results. However when I try and filter by date of birth the criteria is never bringing back the correct results. As you can see in the code below I am checking where the date of birth is equal to the entered date of birth. The data type is java.util.Date and the mapping type in hibernate is date.

The only logical reason I can think of is because the type is taking into account time as well as date, but if that is the case it should rather be a timestamp or a date_time data type?

I'm sure this is a stupid problem but it has caused many hours of frustration and any help would be greatly appreciated.

thanks

Code:

if (searchCriteria.getDateOfBirth() != null) {
                    crit.add(Restrictions.eq("dateOfBirth", searchCriteria.getDateOfBirth()));
}



Hibernate version:3.1.3

Mapping documents:

Code:

    <class name="za.co.generic.newbusiness.persistence.application.ApplicationSummaryHBM"
        proxy="za.co.generic.newbusiness.domain.application.ApplicationSummary" lazy="false">
        <subselect>
            select a.PROXY_ENTITY as PROXY_ENTITY, a.WORKING_ENTITY as WORKING_ENTITY, a.EMPLOYER_ENTITY as
            EMPLOYER_ENTITY, a.APPL_ID as APPLICATION_ID, p.COMP_ID as POLICY_ID, ae.FIRST_NAME || ' ' || ae.LAST_NAME
            as FULL_NAME, ae.DATE_OF_BIRTH as DATE_OF_BIRTH, c.EFFECTIVE_FROM as QUOTE_DATE, c.EFFECTIVE_TO as
            QUOTE_EXPIRY_DATE, a.STAT_CD as STAT_CD from "NB_APPLICATION" a, "NB_APP_ENTITY" ae, "NB_STATUS_CODE" sc,
            "NB_POLICY_GRP" pg, "NB_GROUP_COMP_CONN" p, "NB_COMPONENT" c where a.APPL_ID = ae.APPL_ID and a.STAT_CD =
            sc.STAT_CD and ae.DEFAULT_ROLE = 'PP' and a.APPL_ID = pg.APPL_ID and p.GROUP_ID = pg.GROUP_ID and p.COMP_ID
            = c.COMPONENT_ID
        </subselect>
        <synchronize table="NB_APPLICATION" />
        <synchronize table="NB_APP_ENTITY" />
        <synchronize table="NB_STATUS_CODE" />

        <id name="policyId" type="long" column="POLICY_ID" />
        <property name="applicationId" type="long" column="APPLICATION_ID" />
        <property name="proxyEntityNo" type="long" column="PROXY_ENTITY" />
        <property name="workingEntityNo" type="long" column="WORKING_ENTITY" />
        <property name="employerEntityNo" type="long" column="EMPLOYER_ENTITY" />
        <property name="fullName" type="string" column="FULL_NAME" />
        <property name="dateOfBirth" type="date" column="DATE_OF_BIRTH" />
        <property name="quoteDate" type="date" column="QUOTE_DATE" />
        <property name="quoteExpiryDate" type="date" column="QUOTE_EXPIRY_DATE" />
        <many-to-one name="statusCode" class="za.co.generic.newbusiness.persistence.application.StatusCodeHBM" lazy="false" fetch="join">
            <column name="STAT_CD" precision="10" scale="0" />
        </many-to-one>
    </class>


Name and version of the database you are using: HsqlDB 1.8
Top
 Profile  
 
Ananasi
 Post subject:
PostPosted: Mon May 07, 2007 8:31 am 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
It's kind of hard to say exactly. Here's a quick suggestion: turn on debug logging for the org.hibernate.sql and org.hibernate.type packages. That will show you the SQL statement and the parameters that are injected into the statement. I bet that will give you a lot more insight into exactly what Hibernate is doing when it generates the SQL statment. It makes for big log files, but it is very complete.
Top
 Profile  
 
Rastas
 Post subject:
PostPosted: Mon May 07, 2007 9:20 am 
Newbie

Joined: Tue Aug 01, 2006 4:17 am
Posts: 8
Hey man, thanks for the response, but I am already doing that, and that is the reason for my post. This is what hibernate logs are spewing out.....

Code:
Hibernate: select this_.POLICY_ID as POLICY1_10_1_, this_.APPLICATION_ID as APPLICAT2_10_1_, this_.PROXY_ENTITY as PROXY3_10_1_, this_.WORKING_ENTITY as WORKING4_10_1_, this_.EMPLOYER_ENTITY as EMPLOYER5_10_1_, this_.FULL_NAME as FULL6_10_1_, this_.DATE_OF_BIRTH as DATE7_10_1_, this_.QUOTE_DATE as QUOTE8_10_1_, this_.QUOTE_EXPIRY_DATE as QUOTE9_10_1_, this_.STAT_CD as STAT10_10_1_, statuscode2_.STAT_CD as STAT1_8_0_, statuscode2_.DESCR as DESCR8_0_ from (
            select a.PROXY_ENTITY as PROXY_ENTITY, a.WORKING_ENTITY as WORKING_ENTITY, a.EMPLOYER_ENTITY as
            EMPLOYER_ENTITY, a.APPL_ID as APPLICATION_ID, p.COMP_ID as POLICY_ID, ae.FIRST_NAME || ' ' || ae.LAST_NAME
            as FULL_NAME, ae.DATE_OF_BIRTH as DATE_OF_BIRTH, c.EFFECTIVE_FROM as QUOTE_DATE, c.EFFECTIVE_TO as
            QUOTE_EXPIRY_DATE, a.STAT_CD as STAT_CD from "NB_APPLICATION" a, "NB_APP_ENTITY" ae, "NB_STATUS_CODE" sc,
            "NB_POLICY_GRP" pg, "NB_GROUP_COMP_CONN" p, "NB_COMPONENT" c where a.APPL_ID = ae.APPL_ID and a.STAT_CD =
            sc.STAT_CD and ae.DEFAULT_ROLE = 'PP' and a.APPL_ID = pg.APPL_ID and p.GROUP_ID = pg.GROUP_ID and p.COMP_ID
            = c.COMPONENT_ID
         ) this_ left outer join NB_STATUS_CODE statuscode2_ on this_.STAT_CD=statuscode2_.STAT_CD where this_.DATE_OF_BIRTH=?
DEBUG - NullableType.nullSafeSet(80) | binding '27 April 1977' to parameter: 1
--


as you can see at the end, it reckons it's binding to parameter value '27 April 1977' .


Substitute this directly in the query in a DB ( just to say I did it )

Code:
select ..........
         ) this_ left outer join NB_STATUS_CODE statuscode2_ on this_.STAT_CD=statuscode2_.STAT_CD where this_.DATE_OF_BIRTH='27 April 1977'




And as you would expect I get "wrong data type" error message, hovever put this in...

Code:
select ..........
         ) this_ left outer join NB_STATUS_CODE statuscode2_ on this_.STAT_CD=statuscode2_.STAT_CD where this_.DATE_OF_BIRTH='1977-04-27'


and hey presto it works like a charm....

So I really don't understand why it's such a headache... hibernate is supposed to make my life easier...aaaargh!
Top
 Profile  
 
Ananasi
 Post subject:
PostPosted: Mon May 07, 2007 9:43 am 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
We can try to get this nailed down. Post your hibernate.cfg.xml file, let's see what dialect you are using, along with the database vendor and version. Also, the associated POJO might give us some insight. Maybe even the DDL for the table could help. Additionally, the code where you set the query parameter could assist.

1) Java date implementations are not the cleanest in the world.
2) Every database vendor just has to do dates in their own special way, pain in the rear.
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
  [ 4 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.