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:
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.
Based on my book, High-Performance Java Persistence, this workshop teaches you various data access performance optimizations from JDBC, to JPA, Hibernate and jOOQ for the major rational database systems (e.g. Oracle, SQL Server, MySQL and PostgreSQL).
The SQL Master Class for Java Developers training is aimed to level up your SQL skills with techniques such as Window Functions, recursive queries, Pivoting, JSON processing, and many other database querying features supported by Oracle, SQL Server, MySQL, or PostgreSQL.