Java/Hibernate odata filter by datetime is not working

[former_member435554]

Hi Experts!

In my organization we've developed a java application that exposes odata services, some technologies that we've used are:

• Spring Boot
• Apache Olingo
• JPA/Hibernate

Everything seems to work fine, we can consume the odatas and do our requirements, but I've found a problem when performing a filter by date.

Query:

WorkOrderHeaders?$filter=(StartDate ge datetime'2018-01-16T05:00:00')

Result:

QueryException: unexpected char: '{' [SELECT E1 FROM WorkOrderHeader E1 WHERE (E1.startDate >= {ts '2018-01-16 05:00:00.000'})]

I thought it was a problem of Olingo, so I did a research and found this article: https://issues.apache.org/jira/browse/OLINGO-240?jql=text%20~%20%22datetime%20filter%22

It basically said: "This issue is specific to hibernate and currently there is no specific handling in Olingo JPA processor for hibernate."

Googling about hibernate I've found this: https://hibernate.atlassian.net/browse/HHH-8653

It mention: "The root of the problem seems to be that the Hibernate generated grammar does not include support for temporal literals"

So, apparently hibernate don't provide support for date literals.

• Any ideas how to solve this issue?.
• Do I have to change JPA implementation?

ps. I've builded a small project using the same technologies but considering eclipselink instead of hibernate and it works fine with date filters.

[Ivan-Mirisola]

Hi Ivan

Considering that you've made it work with EclipseLink, are there any reasons why you should be using hibernate instead?

BTW: SAP has delivers the library "SDK for Services" which is basically the same "logic" behind the new cloud foundry "application programming model". With both technologies you are able to create your own OData services based on a DB entities with automation and simplification of the development process without sacrificing flexibility. I'd suggest looking into it as well. There os a nice blog on how to create samples using this technologies.

Hope that helps.

Regards,
Ivan

[former_member435554]

Hi Ivan.

Thanks for you answer, currently I'm testing the application looking for negative impacts because of the migration from Hibernate to EclipseLink.

Since our team has more experience working with hibernate, is there a way to fix this problem?

[Ivan-Mirisola]

Hi Ivan,

Please check the following tutorial on how to setup Hibernate for HANA in Eclipse. I think it might help you checking your project setup.

More specifically, check the following attribute on your hibernate persistence config:

hibernate.dialect=org.hibernate.dialect.HANAColumnStoreDialect

The "unexpected char" tells me that hibernate is generating invalid SQL commands to your HANA DB (I am assuming you are trying to run your JPA project against a HABA DB. Am I correct?).

While checking the SQL command from the provided error I've noticed the usage of function 'ts'. This shows that hibernate isn't generating HANA compatible SQL code, as there is no such data type conversion function on HANA. The closest SQL function that is perhaps equivalent to a time stamp conversion function taking as input a formatted string would be the TO_TIMESTAMP.

I think that by setting the correct dialect, the generated SQL syntax will be correct.

I would also try running the same SQL commands seen on your Hibernate application against your DB via SQL Console. That way you will be able to figure out which part of the SQL command is wrong.

Best regards,
Ivan

[former_member435554]

Hi Ivan.

I've checked my project jpa configuration and it's ok. Yes I'm running with HANA DB.

In this case "SELECT E1 FROM WorkOrderHeader E1 WHERE (E1.startDate >= {ts '2018-01-16 05:00:00.000'})" isn't SQL query, it's actually JPA query (JPQL).

The following odata filter "WorkOrderHeaders?$filter=(StartDate ge datetime'2018-01-16T05:00:00')" With olingo produces: "SELECT E1 FROM WorkOrderHeader E1 WHERE (E1.startDate >= {ts '2018-01-16 05:00:00.000'})"

The problem is that Hibernate do not support standard date literals, check this: https://stackoverflow.com/questions/8386109/jpa-date-literal?noredirect=1&lq=1

It seems that the only option I have is to migrate to EclipseLink.

Regards.

[Ivan-Mirisola]

Hi Ivan,

OK, now I understand what you are trying to do and the Hibernate limitations. However, the support for date literals is missing on version 4.2.5.

Could you check you are using a version of spring boot that carries a more recent version of hibernate-core?

Even for spring boot 1.5.16 you would be getting hibernate 5.0.12 - which may already have the fix for this issue you are having. Since you would be checking for a fix, I would go directly yo version 2.1.0 that contains hibernate-core 5.3.5.

Regards,
Ivan