The moose likes Object Relational Mapping and the fly likes Problem in Date comparision in JPQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Problem in Date comparision in JPQL " Watch "Problem in Date comparision in JPQL " New topic

Problem in Date comparision in JPQL

jitun hi

Joined: Jan 04, 2010
Posts: 3
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:
<orm:named-query name="valuesByKeys">
<orm:query>SELECT NEW studentValueDTO(x) FROM Student x WHERE x.admissionDate = :admissionDate</orm:query>

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

"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:
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();
return result;

I've written an ejb client (Out of container) to invoke the above stateless bean:
java.util.Calendar cal = Calendar.getInstance();

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

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 hi

Joined: Jan 04, 2010
Posts: 3
Could anyone please help here?
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 17410

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.

An IDE is no substitute for an Intelligent Developer.
I agree. Here's the link:
subject: Problem in Date comparision in JPQL
It's not a secret anymore!