• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem in Date comparision in JPQL

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Everyone,

Is there any restrictions in JPQL for date equality comparision?

My requirement is to get all the student's information based on the admission date.
viz. If admissionDate is date-16.02.2010 time-15:03:08, then it should give me all the student's information who has admitted on date-"16.02.2010" time-"15:03:08"

In my orm.xml mapping file, i've the following JPQL query defined:
-----------clip---------
<orm:named-query name="valuesByKeys">
<orm:query>SELECT NEW studentValueDTO(x) FROM Student x WHERE x.admissionDate = :admissionDate</orm:query>
</orm:named-query>
-----------clap---------

"Student" is an ejb3.0 entity class which has a "admissionDate" field of type java.util.Date.
-----------clip---------
@Column(name = "ADMISSION_DATE")
@Temporal(TemporalType.DATE)
private java.util.Date admissionDate;
-----------clap---------

"StudentValueDTO" is a data tranferable object having just getters & setters methods.

I've following ejb 3.0 stateless session bean, which uses the orm.xml mapping file for making DB query:
----------------clip--------------
@PersistenceContext
private javax.persistence.EntityManager manager;

public List<StudentValueDTO> getStudentInfo (java.util.Date admissionDate){

javax.persistence.Query query = manager.createNamedQuery("valuesByKeys");
query.setParameter("admissionDate", admissionDate);
List<StudentValueDTO> result=(List<StudentValueDTO)query.getResultList();
manager.clear();
return result;
}
----------------clap--------------

I've written an ejb client (Out of container) to invoke the above stateless bean:
----------clip----------
java.util.Calendar cal = Calendar.getInstance();
cal.set(2010,01,16,15,03,8);

List><StudentValueDTO> studentList = remoteObject.getStudentInfo(cal.getTime());
----------clap----------

But when i print the contents of studentList, its always empty.
Also, i confirmed with the logs of hibernate, the query seems to be formed properly & binding too. But there are no result sets returned for this query.
I checked in my oracle DB, there are lots of data which matches this criteria i.e. having admissionDate as provided.

Require your kind help in this regard.
Thanks in advance.

_Jitun
 
jitun hi
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Could anyone please help here?
 
Saloon Keeper
Posts: 27762
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dates are like floating-point numbers when it comes to databases. Only worse. This isn't just a JPQL problem, but I'll admit that ORM technologies tend to make the problem more apparent.

The stock Java date classes suffer from granularity issues. java.util.Date is supposed to be precise to the day, java.sql.Date is supposed to be precise to the millisecond (IIRC). Oracle native dates have a granularity of one second, so neither of those datatypes are a good fit. Just to make things worse, the actual internal representation of both java.util.Data and java.sql.Date are precise to the millisecond, with no enforcement of the fundamental limitations of the datatype.

The problem comes when you store Date items. Oracle will truncate them to the second, so any attempt to retrieve a date by equality will usually fail. The only sure-fire way around this is to enforce the proper granularity externally and to ensure that the underlying database object type is sufficiently precise for the application's data that truncation will not occur.
 
reply
    Bookmark Topic Watch Topic
  • New Topic