JPQL not working with dates

Question

I'm not able to get the data from database using the dates in the query...

I'm working on Web application which is using Spring Data JPA and Oracle Database. I was using @RepositoryRestResource annotation in interface where I was just declaring some query methods with named parameters using @Param and @Query annotations. Today I needed to add a new entity with the dates. In database both columns are type of DATE and it is used in the query. But I also have the other one which is type of TIMESTAMP and maybe I would need to use in a future. And below the Java representation of this two columns only, of course with all setters and getters, but it has more fields so just adding this:

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "INIT_DATE")
private Calendar initDate;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "AGG_DATE")
private Calendar aggDate;

I also created new interface for case, the same way as always:

@RepositoryRestResource(collectionResourceRel = "customer", path = "customer")
public interface ICustomerRepository extends PagingAndSortingRepository<Customer, Long> {

    @Query("SELECT c FROM Customer c where c.initDate <= TO_DATE(:currentDate, 'yyyy-MM-dd') AND c.aggDate >= TO_DATE(:currentDate, 'yyyy-MM-dd')")
    public List<Customer> filterByDate(@Param("currentDate") @DateTimeFormat(pattern = "yyyy-MM-dd") Calendar currentDate);

}

And I'm receiving this error:

org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet
ORA-01858: a non-numeric character was found where a numeric was expected

I'm trying to get this data from database using this http request:

http://localhost/webApp/customer/search/filterByDate?currentDate=2017-07-10

In SQL Developer the query works fine.

I read somewhere that in JPQL there is no date function, but in log I can see the query and parameter which looks like this:

select
    customer0_.customerId as col_0_0_,
    customer0_.customerName as col_0_1_,
    customer0_.aggDate as col_0_2_,
    customer0_.initDate as col_0_3_,
from
    customer customer0_ 
where       
    and customer0_.aggDate>=to_date(?, 'yyyy-MM-dd') 
    and customer0_.initDate<=to_date(?, 'yyyy-MM-dd')
2017-07-25 11:55:22.550 TRACE 12252 --- [ (self-tuning)'] o.h.type.descriptor.sql.BasicBinder      : binding parameter [8] as [TIMESTAMP] - [java.util.GregorianCalendar[time=1499637600000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Berlin,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2017,MONTH=6,WEEK_OF_YEAR=28,WEEK_OF_MONTH=3,DAY_OF_MONTH=10,DAY_OF_YEAR=191,DAY_OF_WEEK=2,DAY_OF_WEEK_IN_MONTH=2,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=3600000,DST_OFFSET=3600000]]
2017-07-25 11:55:22.550 TRACE 12252 --- [ (self-tuning)'] o.h.type.descriptor.sql.BasicBinder      : binding parameter [9] as [TIMESTAMP] - [java.util.GregorianCalendar[time=1499637600000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Berlin,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2017,MONTH=6,WEEK_OF_YEAR=28,WEEK_OF_MONTH=3,DAY_OF_MONTH=10,DAY_OF_YEAR=191,DAY_OF_WEEK=2,DAY_OF_WEEK_IN_MONTH=2,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=3600000,DST_OFFSET=3600000]]

And to be honest, I have no idea what is the problem here... The format date in the database is yy/MM/DD, but it also wasn't working for me... Could you tell me what I'm missing or doing wrong??

EDIT [answer to Gusti Arya]:

I tried two things. Before your update I just changed the type and left the @DateTimeFormat. Then I had the same error as with Calendar type. After removing the @DateTimeFormat, so having the same after your update I get this error:

org.springframework.data.repository.support.QueryMethodParameterConversionException: Failed to convert 2017-07-10 into java.util.Date!
Caused by: org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.String] to type [org.springframework.data.repository.query.Param java.util.Date] for value '2017-07-10'; nested exception is java.lang.IllegalArgumentException

What is more interesting, I have the second query which is almost the same, but without TO_DATE function and now I have the same error as above. Previously I had:

Persistent Entity Must not be null

EDIT 2 [related with the query in the log]:

I just noticed that the query which I posted here is not the same as I see in the log... My entity contains EmbeddedId in which is customerId and customerName. And those two columns appears thrice... So here is the valid log of the query:

select
    customer0_.customerId as col_0_0_,
    customer0_.customerName as col_0_1_,
    customer0_.customerId as col_1_0_,
    customer0_.customerName as col_1_1_,
    customer0_.customerId as customerId1_6_,
    customer0_.customerName as customerName2_6_,
    customer0_.aggDate as aggDate3_6_,
    customer0_.initDate as initDate4_6_,
from
    customer customer0_ 
where       
    and customer0_.aggDate>=to_date(?, 'yyyy-MM-dd') 
    and customer0_.initDate<=to_date(?, 'yyyy-MM-dd')

**EDIT [response to Brian]: **

And also the types in Entity should be Calendar, right? I also put two different Temporal annotation for those two fields. One points to TemporalType.TIMESTAMP and the other to TemporalType.DATE. But then is the problem with the passing calendar value as http parameter. I tried four versions of URL:

1. http://localhost/webApp/customer/search/filterByDate?currentDate=2017-07-10
2. http://localhost/webApp/customer/search/filterByDate?currentDate=2017-07-10 13:08:24.000+0000
3. http://localhost/webApp/customer/search/filterByDate?currentDate=2017-07-10T13:08:24.000+0000
4. http://localhost/webApp/customer/search/filterByDate?currentDate=1499692104

But none of this is not working...

not sure about spring but you can try `hibernate @type` annotation over your timestamp with type date — bananas, Jul 25 '17 at 10:56
and it can be duplicate of (https://stackoverflow.com/questions/2188768/java-type-for-date-time-when-using-oracle-date-with-hibernate) — bananas, Jul 25 '17 at 12:15
It isn't duplicate. I made mistake because those two columns are both dates. I also have third column with TIMESTAMP, but it's not used yet in a query. Maybe in a future. — Lui, Jul 25 '17 at 12:35

score 2 · Answer 1 · answered Jul 25 '17 at 15:33

2

Without further digging into detail, I think you just need to change

@Query("SELECT c FROM Customer c where c.initDate <= TO_DATE(:currentDate, 'yyyy-MM-dd') AND c.aggDate >= TO_DATE(:currentDate, 'yyyy-MM-dd')")

to

@Query("SELECT c FROM Customer c where c.initDate <= :currentDate AND c.aggDate >= :currentDate")

Why? Because the fields initDate and aggDate are of type java.util.Calendar and so is the parameter currentDate. Everything matches and Spring Data as well as any JPA provider binds java.util.Calendar to an SQL timestamp as you can see in your log

...binding parameter [8] as [TIMESTAMP]...

answered Jul 25 '17 at 15:33

Brian

872
8
16

I'm updating my main post, because it's not enough space here. – Lui Jul 25 '17 at 16:09
@Lui, I just created a sample Spring Boot project and it worked out of the box. As I said: Omit the `TO_DATE` function in your query, stick to the URL `.../customer/search/filterByDate?currentDate=2017-07-10` and use the sources of your original question (by which I mean use `java.util.Calendar` and `@Temporal(TemporalType.TIMESTAMP)` for your timestamp fields). Let me know, if you have any trouble. – Brian Jul 25 '17 at 17:25
I tried this: query without `TO_DATE` for two cases - first with `@Param` without `@DateTimeFormat` and second without the `@DateTimeFormat`. In first case, I get error: `"Failed to convert 2017-07-10 into java.util.Calendar!; "`, in second: `"Parameter value [java.util.GregorianCalendar[...] did not match expected type [java.util.Date (n/a)]"`. In the entity I tried both `@Temporal` annotation - with `TIMESTAMP` and `DATE` and also hibernate `@Type`. Rest of the comment in next comment... – Lui Jul 26 '17 at 09:19
Because without this I get this: `"Could not set field value [2016-02-03 11:11:27.138286] value by reflection: [class Customer.aggDate2] setter of Customer.aggDate2"`. This `aggDate2` is the type of `TIMESTAMP` in database. The `initDate` and `aggDate` are type of `DATE`. When I also remove the `@Type` from this `aggDate2` I get this error: `"PersistentEntity must not be null"`. I don't understand it... – Lui Jul 26 '17 at 09:26
Omg! The problem is not with the dates, but with the query and the entity... In my real application I have entity with `@EmbeddedId`I was sure that the query without filter on date works fine... Unfortunately, the problem is this `@EmbeddedId`... Let's say that I have entity like that: [ENTITY](https://pastebin.com/wDKK8MWH). How to see the data from the whole `Entity` - together with `EmbeddedId`?? In log the extracted values are visible, but not in the response body of the REST. Query with `c.customerId, c` is not working and without `c.customerId` returns only `Entity` without `EmbeddedId`. – Lui Jul 26 '17 at 10:44
The ID should be part of the `self` URL in your JSON response. Including the ID in the JSON object would be redundant. Nonetheless you can expose it via config. See [this question and answer](https://stackoverflow.com/questions/30912826/expose-all-ids-when-using-spring-data-rest) for details. – Brian Jul 26 '17 at 10:54
Oh god... I don't want to set up this for each entity which will be having composite key like this... is no other way to do this?? Using maybe some converters or something?? There could be a lot of data returned in the list, so I would like it to be quite efficient and working with good performance. – Lui Jul 26 '17 at 11:04
I don't know of any other way than setting it up for each entity. As I said, it would be redundant to include the ID when it is part of `self` already. I recommend rethinking you approach, if you need to include the ID or search for another solution yourself as it is not part of your original question. – Brian Jul 26 '17 at 11:58

score 1 · Accepted Answer · answered Jul 26 '17 at 13:40

I made huge mistake... The problem is not with the dates, but with the query and the entity... In my real application I have entity with @EmbeddedId and I was sure that the query without filter on date works fine... So I didn't mention about it. Unfortunately, the problem is this @EmbeddedId... I thought that I can use query with c.customerId, c which will return everything, but is not working and without c.customerId returns only Entity without EmbeddedId.

So as @Brian mentioned in his comment:

The ID should be part of the self URL in your JSON response. Including the ID in the JSON object would be redundant. Nonetheless you can expose it via config. See this question and answer for details.

To sum up, query works without TO_DATE function, with @DateTimeFormat annotation for Calendar parameter and @Temporal annotation for the fields with the dates in the entity.

Thank you for all your help and sorry for this mistake.

if you think your query has been resolved, you can accept the answer and close the question — bananas, Jul 28 '17 at 03:51

Gusti Arya · Answer 3 · 2017-07-25T10:38:31.413

0

try changing your code in model class into this :

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "INIT_DATE")
private java.util.Date initDate;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "AGG_DATE")
private java.util.Date aggDate;

and also in JPQL not defining TO_DATE function, try changing your jqpl to this :

@Query("SELECT c FROM Customer c where c.initDate <= :currentDate AND c.aggDate >= :currentDate"
public List<Customer> filterByDate(@Param("currentDate") Date currentDate);

edited Jul 25 '17 at 10:38

answered Jul 25 '17 at 10:17

Gusti Arya

1,281
3
15
32

It didn't work. Error is the same. But the thing is that in project they would like to stay with Calendar type. – Lui Jul 25 '17 at 10:32
check my updated question. I answered there, because here wasn't enough space. – Lui Jul 25 '17 at 10:53
@Lui what data type of INIT_DATE column and AGG_DATE column in your database table? – Gusti Arya Jul 25 '17 at 11:19
as I wrote in the question - In database one column is type of DATE and the other one is type of TIMESTAMP. But I mentioned about this, because I have three columns: 2 with date and one with timestamp,and maybe I would need this in a future. Right now, the query is related only with type DATE. – Lui Jul 25 '17 at 12:27

score 0 · Answer 4 · answered Jul 25 '17 at 12:14

0

There is no TO_DATE function in JPQL. You shall either use native sql query, or wrap native database function into JPQL FUNCTION function :)

I believe it should be FUNCTION(TO_DATE('2000-01-01', 'YYYY-MON-DD'))

answered Jul 25 '17 at 12:14

fg78nc

4,774
3
19
32

If there is no TO_DATE then I should be able to write the query without any function right? I don't need to format the date or something. But for some reasons I can't force it to work properly... – Lui Jul 25 '17 at 12:33
Wrap `TO_DATE` in `FUNCTION()` as stated above in my answer. If you don' need that - then you have to supply correct `@DateTimeFormat ` – fg78nc Jul 25 '17 at 12:34
So, here is the thing. I have this error: " `"Failed to convert 2017-07-10 into java.util.Calendar!"`, when I'm using the query without the `FUNCTION(T_DATE())` or without `@DateTimeFormat`; when is use both then error is: `"could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet; ORA-00904: "FUNCTION": invalid identifier"`. – Lui Jul 26 '17 at 09:03
Please post here your query, I will try on my side. – fg78nc Jul 26 '17 at 13:20
Take a look at my explanation in my answer to my question. – Lui Jul 26 '17 at 13:42
`SELECT c FROM Customer c where c.initDate <= FUNCTION(TO_DATE(:currentDate, 'yyyy-MM-dd')) AND c.aggDate >= FUNCTION(TO_DATE(:currentDate, 'yyyy-MM-dd`))` this is your final query? – fg78nc Jul 26 '17 at 13:44

JPQL not working with dates

4 Answers4