Search code examples
javarestspring-bootspring-data-jpajpql

JPQL not working with dates

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...

Solution

  • 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.