问题描述
从MySQL切换到 PostgreSQL 后，我发现我的SQL查询(spring数据存储库界面中的@Query)不再起作用.该问题是由于空值作为 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 有关，当value为false时，null值将按预期工作.因此，问题是，即使启用了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.
这篇关于Spring数据存储库将空值作为Bytea发送到PostgreSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！