Spring data jpa native query with null parameter (PostgreSQL)

Question

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'

The error seems to be related to extraction of the result set, what are the fields of the custom projection? — Thomas
– Thomas, Commented Nov 24, 2020 at 20:02
String - eventType, Long - institutionId, Long - studentIdentifierId, String - studentIdentifierName. And when I pass identifierId as e.g. 0L it doesn't break, so I'm not sure if it's related with projection. — degath
– degath, Commented Nov 24, 2020 at 20:12

coladict · Accepted Answer · 2020-11-24 22:51:18Z

3

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 Nov 24, 2020 at 22:51

coladict

5,1151 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christian Beikov · Accepted Answer · 2020-11-25 08:25:32Z

1

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

answered Nov 25, 2020 at 8:25

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Comments

Banyenzachi · Accepted Answer · 2020-11-24 23:24:29Z

0

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

answered Nov 24, 2020 at 23:24

Banyenzachi

111 bronze badge

Comments

SternK · Accepted Answer · 2020-11-25 20:39:28Z

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.

Paul Snow · Accepted Answer · 2021-12-16 23:29:49Z

0

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.

answered Dec 16, 2021 at 23:29

Paul Snow

911 silver badge7 bronze badges

Comments

Onur Akan · Accepted Answer · 2022-09-14 15:25:58Z

0

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

answered Sep 14, 2022 at 15:25

Onur Akan

5110 bronze badges

1 Comment

coladict Over a year ago

You know, I really thought this was only a problem when using PostgreSQL, but this year I encountered it with MSSQL, and now you're confirming it for another database? I do hope the new type system in Hibernate 6 fixed that.

Collectives™ on Stack Overflow

Spring data jpa native query with null parameter (PostgreSQL)

6 Answers 6

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related