Spring boot native query with null parameter

Question

I have a native query in Spring Boot as such:

@Query(value = "SELECT t.* FROM Transaction t WHERE " +
    "t.datetime >= TO_TIMESTAMP(?1,'YYYY-MM-ddTHH:MI') " +
    "AND t.datetime < TO_TIMESTAMP(?2,'YYYY-MM-ddTHH:MI') " +
    "AND (t.location_1 = ?3 or ?3 is null) " +
    "LIMIT ?4",
    nativeQuery = true)
    List<Transaction> findBySearchTerms(@Param("fromDateTime") String fromDateTime,
            String toDateTime,
            Integer location1,
            Integer maxCount
            );

For the location1, it may be null. When i run this query with location1 null, it returns the error message: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = bytea

After which i tried casting on parameter 3:

"AND (?3 is null or t.location_1 = cast(?3 as bigint)) " +

It also results in an error: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to bigint.

I have searched for similar questions on stackoverflow and followed some of the recommendations but it still does not work. Any ideas?

codemozzer · Accepted Answer · 2020-04-20 13:30:27Z

11

I had a same issue and I managed to fix it by casting it to BIGINT but first to TEXT and then BIGINT. I use Spring Boot 2.2 and Kotlin, but same is for Java.

I have following example:

 @Query(
        """
         SELECT
         f.id,
         f.name,
         c.id AS company_id,
         c.name AS company_name
         FROM facility f
         WHERE (:companyId IS NULL OR f.company_id = CAST(CAST(:companyId AS TEXT) AS BIGINT))  
        """
)
@Transactional(readOnly = true)
    fun exampleMethod(@Param("companyId") companyId: Long? = null):List<Result>

If I do not cast it I get error:

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = bytea
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts

and I I cast it just to BIGINT once, then error is:

Caused by: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to bigint

edited Apr 20, 2020 at 13:30

answered Apr 20, 2020 at 9:12

codemozzer

2212 silver badges6 bronze badges

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

1 Comment

user1503636 Over a year ago

See also > When the value for that column is null, hibernate uses the wrong type stackoverflow.com/a/64383530/1503636

Sanjay · Accepted Answer · 2020-02-10 11:43:37Z

1

Just reverse your parameter in query like this.

@Query(value = "SELECT t.* FROM Transaction t WHERE " +
    "t.datetime >= TO_TIMESTAMP(?1,'YYYY-MM-ddTHH:MI') " +
    "AND t.datetime < TO_TIMESTAMP(?2,'YYYY-MM-ddTHH:MI') " +
    "AND ( ?3 is null or t.location_1 = ?3) " +
    "LIMIT ?4",
    nativeQuery = true)
    List<Transaction> findBySearchTerms(@Param("fromDateTime") String fromDateTime,
            String toDateTime,
            Integer location1,
            Integer maxCount
            );

edited Feb 10, 2020 at 11:43

answered May 23, 2019 at 4:10

Sanjay

2,5131 gold badge16 silver badges29 bronze badges

2 Comments

Eugene Over a year ago

Does not work for me, the error message shows the position number being changed. It will still evaluate the second portion t.location_1 = ?3

davey Over a year ago

for string comparsion, don't use t.location_1 = ?3 but use t.location_1 LIKE ?3

Rowi · Accepted Answer · 2019-05-23 03:57:10Z

0

You may have two different query to handle Null and Not Null. Call these two methods based on location is null or not null

@Query(value = "SELECT t.* FROM Transaction t WHERE " +
"t.datetime >= TO_TIMESTAMP(?1,'YYYY-MM-ddTHH:MI') " +
"AND t.datetime < TO_TIMESTAMP(?2,'YYYY-MM-ddTHH:MI') " +
"AND (t.location_1 = ?3 ) " +
"LIMIT ?4", nativeQuery = true)

@Query(value = "SELECT t.* FROM Transaction t WHERE " +
"t.datetime >= TO_TIMESTAMP(?1,'YYYY-MM-ddTHH:MI') " +
"AND t.datetime < TO_TIMESTAMP(?2,'YYYY-MM-ddTHH:MI') " +
"AND (t.location_1 is null) " +
"LIMIT ?4",
nativeQuery = true)

answered May 23, 2019 at 3:57

Rowi

5853 silver badges9 bronze badges

1 Comment

Eugene Over a year ago

Thanks, I guess this is the only way for now. The solution will become messy when I have more than 1 parameter that can be null..

Collectives™ on Stack Overflow

Spring boot native query with null parameter

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related