JPA select query with timestamp and date fields fail to retrieve results

Question

I am trying to fetch records using JPA Query which has DATE and TIMESTAMP columns in WHERE clause. But somehow date and timestamp columns fail to retrieve anything from database.

Code Segment:

String sql = "Select F.* from FIN_TABLE F where F.COL1_NUM = :COL1 and F.COL2_TIMESTAMP =:COL2 and F.COL3_DATE =:COL3";
Query query = JPAentityManager.createNativeQuery(sql);
query.setParameter("COL1", 123);
//java.sql.Timestamp:2014-10-29 12:00:00.0
query.setParameter("COL2", new java.sql.Timestamp(new java.sql.Date(new SimpleDateFormat("MMM dd yyyy HH:mm:sssa").parse("OCT 29 2014 12:00:000AM").getTime()).getTime()));
//java.sql.Date:2014-10-29
query.setParameter("COL3", new java.sql.Date(new SimpleDateFormat("MMM dd yyyy HH:mm:sssa").parse("OCT 29 2014 12:00:000AM").getTime()));

List<FinTable> result =  (List<FinTable>)query.getResultList();

And Data in Oracle is:

COL1_NUM    COL2_TIMESTAMP                      COL3_DATE
123         29-OCT-14 12.00.00.000000000 AM     26-Nov-14
456         29-OCT-14 12.00.00.000000000 AM     26-Nov-14

I am originally trying to retrieve results using EntityManager.find(Class, Object), but it was also failing so i tried with createNativeQuery(), which also fails to succeed. My FinTable entity has these columns as Timestamp and Date.

Please enlighten the right way. :) Thanks !!

Answer 1

Date and Calendar parameter values require special methods in order to specify what they represent, such as a pure date, a pure time or a combination of date and time, as explained in detail in the Date and Time (Temporal) Types section.

For example, the following invocation passes a Date object as a pure date (no time):

query.setParameter("date", new java.util.Date(), TemporalType.DATE);

and timestamp

query.setParameter("date", new java.util.Date(), TemporalType.TIMESTAMP);