localdate value don't return result

Question

I use Java 11, spring boot 2.3.4 with jpa (hibernate implementation)

I have an entity

public class Prba{
    Long prId;
    Long baId;
    LocalDate startDate;
}

My jpql query

select 
  prba from Prba prba 
where 
  prba.prId != :prId
and
  prba.baId = :baId
and
  prba.startDate = :date

baId, prId are long (number(18,0) in oracle) date is localdate (date in oracle)

query generated

select * from prba
where 
  prba.pr_id != ?
and
  prba.ba_id = ?
and
  prba.start_date = ?

I get no result

Without start_date condition I get a row, start_value is 15-08-2020, it's the java value i pass, but i don't get any result

Edit

if i use

to_date(prba.start_date, 'DD-MM-YYYY) = TO_DATE ('15-08-20', 'DD-MM-YYYY')

that work

Is there any method to use in jpa when I use localdate

Edit 2

create table prba ( pr_id number(18,0), ba_id number(18,0), startDate date )

Issue it's in orale a time is saved with a date, if you pass by a ui tool to insert data, 00:00:00 for the time will not be used and if you compare you will get wrong date

where is localdate defined? This does not make sense. you are specifying a date mask of YYYYY but your example shows a year of 20, not 2020. Please edit the question and provide all relevant detail included table definition, and example data — OldProgrammer, Sep 23 '20 at 18:53
LocalDate is a datatype in Java, is in my entity. make not sense... but it work — robert trudel, Sep 23 '20 at 19:00

EdStevens · Answer 1 · 2020-09-23T21:00:12.490

You need to allow for the fact that in oracle a DATE data type includes the time down to the second. So if the date in your oracle table has, effectively '23-Sep-2020 15:24:32', and the date you are comparing it to is just a date with no time, then they will not match. Everything you've presented - especially the use of the to_date to correct it, suggests that is your issue. BTW,

Here's a demo of what I just said, with an example of the normal adjustment to deal with DATE and ignore the time component:

SQL> -- create and populate test table
SQL> create table my_test (dob date);

Table created.

SQL> insert into my_test values (sysdate);

1 row created.

SQL> -- unconditional select to prove what's there
SQL> select dob,
  2         to_char(dob,'dd-mon-yyyy hh24:mi:ss') date_time
  3  from my_test;

DOB       DATE_TIME
--------- --------------------
23-SEP-20 23-sep-2020 15:07:50

1 row selected.

SQL> -- use  where clause that does not account for the time component, it returns nothing
SQL> select dob,
  2         to_char(dob,'dd-mon-yyyy hh24:mi:ss') date_time
  3  from my_test
  4  where dob = to_date('23-09-2020','dd-mm-yyyy')
  5  ;

no rows selected

SQL> -- use trun() to eliminate the time component
SQL> select dob,
  2         to_char(dob,'dd-mon-yyyy hh24:mi:ss') date_time
  3  from my_test
  4  where trunc(dob) = to_date('23-09-2020','dd-mm-yyyy')
  5  ;

DOB       DATE_TIME
--------- --------------------
23-SEP-20 23-sep-2020 15:07:50

1 row selected.

SQL> -- clean up the test
SQL> drop table my_test purge;

Table dropped.

SQL> spo off

Some other observations:

your use of "to_date(prba.start_date, 'DD-MM-YYYY)" ... the to_date function take a string as its input. So if prba.start_date is of data type DATE, you force oracle to first do an implied to_char to make it the string required by to_date. And it is only a matter of time before that comes back to bite you, due to conflicting NLS_ settings. And if prba.start_date is NOT of data type DATE, then that is in itself a design failure.
I'd suggest you give some thought to your naming conventions.

select prba from Prba prba

you have a table name PRBA, in that table you have a column also named PRBA. Not a good idea. Spend a little time researching column and table naming conventions.

localdate value don't return result

1 Answers1