Spring data jpa native query with null parameter (PostgreSQL)

user_6701496 0
Viewed 3844

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'
spring-data-jpa postgresql hibernate spring java
6 Answers

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.

user_38411610

Modify query by adding space between roles.identifier_id=:identifierId should be likeroles.identifier_id= :identifierId

user_95521800

You will have to use the following instead: (cast(:identifierId as bigint) IS NULL OR roles.identifier_id=:identifierId)

user_4124460

As it was mentioned in the @coladict's answer it looks like, unfortunately, you can not do that you want with clean Spring Data JPA solution. So, you can rewrite you query using EntityManager and hibernate specific setParameter that allows explicitly specify binding parameter type:

import org.hibernate.type.LongType;

// ...

List<CustomProjection> results = entityManager.createNamedQuery("find_by_custom_condition" )
.unwrap(org.hibernate.query.Query.class)
.setParameter("identifierId", identifierId, LongType.INSTANCE)
.getResultList();

Then you can use @NamedNativeQuery with @SqlResultSetMapping to map native query results to the CustomProjection. See examples in the hibernate documentation.

See also this article about setParameter usage in JPA and Hibernate query.

user_62771040

So this is not the exact answer to the question above, but I was facing a similar issue, I figured I would add it here, for those that come across this question.

I was using a native query where the in clause of the query might be null, ie:

WHERE (cm.first_name in (:firstNames) OR :firstNames is NULL)

I was getting the bytea error, in the end I was able to send an empty list.

(null == entity.getFirstName()? Collections.emptyList() : entity.getFirstName())

In this case, sending the empty list to the resolver worked, where as null did not.

hope this saves some time for people who come across this question looking for how to pass null to a list parameter of a native query.

user_104055090

Following code also worked for me when booleanParameterValue is null. My database is Oracle and, application is using jpql and hibernate.

Query query = getEntityManager().createNamedQuery("findBankAccountInfos");
query.setParameter("booleanParameter", Boolean.FALSE);
query.setParameter("booleanParameter", booleanParameterValue);

Otherwise application is throwing following exception:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
user_40389690
Related