Oracle DATE and Hibernate/JPA mapping

Question

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.

Are you sure the format you are using is correct to parse the string to Date? Can you try DateFormat df = new SimpleDateFormat("yyyy-MM-dd");? — Bhesh Gurung
– Bhesh Gurung, Commented Apr 28, 2012 at 18:10
@βнɛƨн Ǥʋяʋиɢ -- Nope, I had actually tried that earlier too, but does not work. Strangely, Hibernate does not report any errors, only no results. — SRK
– SRK, Commented Apr 28, 2012 at 18:25
Can you give us the query (NamedQuery or Critera query or whatever it is you use.) I think the error/problem is in the query or in the method findByReportDate — esej
– esej, Commented Apr 28, 2012 at 18:35
try a date range from >= 2012-02-01 to < 2012-02-02 in your query and see if that brings any results. sometimes with dates, you think you're working with a date and its really storing it as a date-time — Bruce Lowe
– Bruce Lowe, Commented Apr 28, 2012 at 19:40
I think like @BruceLowe, Oracle always stores dates with time (even if you don't show it). If you can query the DB with pure sql (not with hibernate) see what you get for this query: select to_char(REPORTING_DATE, 'dd/mm/yyyy hh24:mi:ss') from IR_REPORTING_PERIOD — A.B.Cade
– A.B.Cade, Commented Apr 29, 2012 at 5:47

psaraj12 · Accepted Answer · 2012-04-29 08:38:46Z

3

I think there is some explicit date format conversion while obtaining the result in reportingPeriodRepository.findByMaxReportingPeriod();

Hence we can check whether we get the data using the same format as database format

Change

DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); 
ReportingPeriod period3 = reportingPeriodRepository.findByReportDate(df.parse("2012-02-01"))

to

DateFormat df = new SimpleDateFormat("dd-MMM-yy"); 
ReportingPeriod period3 = reportingPeriodRepository.findByReportDate(df.parse("01-FEB-12"))

edited Apr 29, 2012 at 8:38

answered Apr 29, 2012 at 8:09

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle DATE and Hibernate/JPA mapping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related