The question is how to make my native query accept a null parameter?
Here is my query:
@Query(value = "SELECT " +
" summaries.event_type as \"eventType\", " +
" summaries.institution_id as \"institutionId\", " +
" identifiers.id as \"studentIdentifierId\", " +
" identifiers.name as \"studentIdentifierName\" " +
"FROM summaries " +
"LEFT JOIN roles ON summaries.student_id=roles.id " +
"LEFT JOIN identifiers ON roles.identifier_id=identifiers.id " +
"WHERE " +
" summaries.student_id IS NOT NULL AND " +
" summaries.event_type IN (:eventTypes) AND " +
" (:identifierId IS NULL OR roles.identifier_id=:identifierId) " +
"GROUP BY " +
" summaries.institution_id, " +
" summaries.student_id, " +
" identifiers.id, " +
" identifiers.name, " +
" summaries.event_type",
nativeQuery = true)
List<CustomProjection> findByCustomCondition(
@Param("identifierId") Long identifierId,
@Param("eventTypes") List<String> eventTypes);
And now when I pass identifierId as null, I'm receiving the error:
InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet.
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = bytea
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
It seems like the line (:identifierId IS NULL OR roles.identifier_id=:identifierId) causes a problem here, but can't really find a good solution, as from my understanding it should work normally (when I use this query directly on pgAdmin it works as it should, so I'm quite sure it's an issue related with mapping).
I tried to cast the identifier (cast(:identifierId as bigint)), but it didn't helped out.
PS. it's kinda legacy project, so these are versions that I use:
compile group: 'org.springframework.data', name: 'spring-data-jpa', version: '1.11.6.RELEASE'
compile group: 'org.postgresql', name: 'postgresql', version: '42.2.2'
compile group: 'org.hibernate', name: 'hibernate-core', version: '5.2.17.Final'
This is a common problem with Hibernate + PostgreSQL, and the solution is to implement that method yourself, instead of having Spring do it for you. Within the implementation you have to do something like this
List<CustomProjection> findByCustomCondition(
@Param("identifierId") Long identifierId,
@Param("eventTypes") List<String> eventTypes) {
// entityManager is acquired with the @PersistenceContext annotation as an injectable
Query q = entityManager.createNativeQuery(..., CustomProjection.class);
// the important part:
q.setParameter("identifierId", 0L);
q.setParameter("identifierId", identifierId);
...
First call to setParameter ensures Hibenate uses the correct type setter, second call overrides the first value without having executed the query, and skips type detection.
Modify query by adding space between roles.identifier_id=:identifierId should be likeroles.identifier_id= :identifierId
You will have to use the following instead: (cast(:identifierId as bigint) IS NULL OR roles.identifier_id=:identifierId)