Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem in Date comparision in JPQL

 
jitun hi
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
Could anyone please help here?
 
Tim Holloway
Saloon Keeper
Posts: 18302
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic