Encounter the same problem in 5.3.7 which are described at :
Refer to the test-case at https://github.com/hibernate/hibernate-orm/commit/c354b5e8b9e157d46c08d3070de5d654e4e6cf11. Changing to use the native SQL
Then set the parameter to null
It will then thrown :
Change to use Hibernate specific API does not have such problem.
This is my JpaRepository method that is still failing with hibernate-core 5.3.9 Postgre JDBC driver 42.2.5:
The generated SQL query is
It seems there is a problem with mapping of the java Enum (4th parameter)
The code JpaRepository works just fine with hibernate-core 5.2.18.
You may be able to workaround this issue by using Postgresql encode function to cast the first usage of :name from bytea to varchar. I'm not familiar enough with this function to know for sure.
I see that when HQL/JPQL is used, Hibernate recognizes that :name should be treated as a StringType for both instances where that parameter is used.
When a native query is used though, Hibernate determines that the first time :name is used, it should be treated as a SerializableType, and the second time :name should be treated as StringType.
An improvement would be for Hibernate to determine that both usages of the same parameter should be treated as the same type, in this case StringType.
I have dug deep into this before, and a real solution lies only in PostgreSQL, not in Hibernate.
There is however a workaround that should be made more commonly known.
For whatever your query is:
You can first call setParameter("param",SOME_CONST) with a non-null value of the same class to invoke the type identification, then with your real potentially-null parameter.
Setting the parameter a second time without having called the query is not an error.
I discovered something that works for me, see below, I changed the JPQL query to use named parameters instead of ?1, ?2, ?3, ?4, hope this helps someone else:
This works fine with the version Spring Data JPA 2.1.7.RELEASE (it is a Spring Boot 2.1.7.RELEASE app), same Postgre version (9.6.12).
I suppose the problem is that on JDBC level, the parameter values are passed with the `setObject(int index, Object o)` method. The PostgreSQL JDBC driver can't handle NULL values that way though so this is the problem you are having. When passing a NULL value for a parameter, this fails. One way around that is to cast the parameter to the desired type i.e. use ... where col = cast(aram as varchar)