However, when running the query above, Hibernate throws the following exception:
java.lang.IllegalArgumentException: Unknown parameter name : datetime
That’s because of the :: type cast operator conflicts with the JPA : named parameter syntax.
So, how can we fix it?
There are two ways we can fix this issue. We can either escape the : character using a \ or use the CAST function.
Escaping the : character
If we escape each of the : character using \, so that :: becomes \:\:, then everything will work just fine:
I'm running an online workshopk on the 14th of May about The Best Way to Fetch Data with Java Persistence and Hibernate.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Native SQL queries are very powerful, and that’s why you should use them. However, few database-specific operators, like the :: type conversion operator might interfere with the JPA-specific named parameter syntax.
Fixing this issue is actually very easy, as you can replace the operator in question with its equivalent SQL function call.