问题描述

从 MySQL 切换到 PostgreSQL 后，我发现我的 SQL 查询(spring 数据存储库接口中的 @Query)不再起作用.问题是由 null 值作为 bytea 发送的，我收到以下异常:

After switching from MySQL to PostgreSQL I found out that my SQL query (@Query in spring data repository interface) does not work anymore. The issue is caused by null value being sent as bytea and I'm getting following exception:

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.

带有@Query 的存储库:

Repository with @Query:

public interface WineRepository extends PagingAndSortingRepository<Wine, Long> {
    @Query(value = "SELECT * FROM WINE w WHERE (?1 IS NULL OR w.id = ?1)", nativeQuery = true)
    Wine simpleTest(Long id);
}

简单测试:

LOGGER.warn("test1: {}", wineRepository.simpleTest(1L));    //ok
LOGGER.warn("test2: {}", wineRepository.simpleTest(null));  //PSQLException

在实际情况下，我有多个可以为 null 的参数，我不想在 java 代码中检查它们，而是将它们发送到 sql 查询.我在 stackoverflow 上检查了这里的问题，但没有找到一个很好的答案，尤其是对于 spring 数据存储库 @query 注释.

In the real case I have multiple parameters which can be null and I would prefer not checking them in java code but sending them to sql query. I have checked questions here on stackoverflow but found none with a good answer especially for spring data repository @query annotation.

使用 PostgreSQL 处理空值的正确方法是什么?或者您有任何提示如何解决我的方法?谢谢！

更新:问题似乎与 nativeQuery = true 有关，当值为 false 时，空值按预期工作.所以问题是即使启用了 nativeQuery 是否也可以使其正常工作.

Update: Issue seems to be related to nativeQuery = true, when value is false, null values work as expected. So the question is whether it is possible to make it function even with nativeQuery enabled.

推荐答案

试试这个.

SELECT *
FROM WINE w
WHERE ?1 IS NULL OR w.id = CAST(CAST(?1 AS TEXT) AS BIGINT)

它满足类型检查器并且应该具有与原始查询相同的属性.CAST 如果发生在常量值而不是数据库行中的值上，则不会对性能造成很大影响.

It satisfies the type checker and should have the same properties as the original query. CAST is not a big performance hit if it happens on a constant value rather than a value from a database row.