Spring Data JPA - problem with null parameter for NUMBER column type

Question

I want to make dynamic query in which if particular parameter is sent, the Native query should filter the result based on it. In case it's null, it should not reflect the result.

I am using Spring Data JPA with Native query mechanism + Oracle DB

For String parameters this approach works fine

:email is null or s.email = :email

but for Integer parameters when they have value, the Query works but if the parameter is null the query fails with the error

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

I am using the exactly the same approach for for Integer instead of String

I am wondering whether the problem is on my side or it's some kind of bug?

Answer 1

In Oracle DB it's not worked this way. A workaround is using JPQL like

SELECT s FROM Entity s WHERE :id is null OR s.id = COALESCE(:id, -1)

Or for native query use TO_NUMBER function of oracle

SELECT s FROM Entity s WHERE :id is null OR s.id = TO_NUMBER(:id)