[FIXED] Spring data jpa native query with null parameter (PostgreSQL)

Issue

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'

Solution

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.

Answered By - coladict
Answer Checked By - Robin (JavaFixing Admin)