One of the Oracle tables looks like this, I dont have the option of changing this:
REPORTING_PERIOD | REPORTING_DATE (Oracle DATE type)
-------------------------------
1140 01-FEB-12
1139 01-JAN-12
The JPA entity (with Hibernate as the provider) which looks like this :
@Column(name="REPORTING_PERIOD")
private long reportingPeriod;
@Temporal( TemporalType.DATE)
@Column(name="REPORT_DATE")
private Date reportDate; //java.util.Date
Now, let us go through my unit tests: (I am using Spring Data JPA for repositories) The below line queries the DB by REPORTING_PERIOD column
ReportingPeriod period1 = reportingPeriodRepository.findByMaxReportingPeriod();
assertNotNull(period1); // works fine and entity is fetched
System.out.println(period1.getReportDate());
The out put of SOP is 2012-02-01 - Notice the automatic conversion from value in db 01-FEB-12
Now, If I query directly by date using '01-FEB-12', as I am doing below, I dont get any results:
ReportingPeriod period2 = reportingPeriodRepository.findByReportDate(period1.getReportDate());
assertNotNull(period2);
Notice that, I am using date field from the same entity which I could successfully fetch in the previous statement.
Nor this works :
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
ReportingPeriod period3 = reportingPeriodRepository.findByReportDate(df.parse("2012-02-01"));
assertNotNull(period3);
Any help on how can I query ( with HQL will also be ok) by REPORTING_DATE as the param when the value in db is 01-FEB-12 is greatly appreciated.
Date
? Can you tryDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
? – Bhesh Gurung Apr 28 '12 at 18:10NamedQuery
or Critera query or whatever it is you use.) I think the error/problem is in the query or in the method findByReportDate – esej Apr 28 '12 at 18:35select to_char(REPORTING_DATE, 'dd/mm/yyyy hh24:mi:ss') from IR_REPORTING_PERIOD
– A.B.Cade Apr 29 '12 at 5:47