表达式中的空导致本机查询异常 [英] Empty in expression cause an exception in native query

问题描述

我使用 Hibernate 和 JpaRepository 来处理我的数据库操作.

I use Hibernate and JpaRepository to handle my DB operations.

我有这个查询(为了简化，因为原始查询很大):

I have this query (to simplify because original query is quite big):

@Query(value="select * from history h where h.project_id in :projects", nativeQuery=true)
List<History> getHistoriesByProjectsIn(@Param("projects")List<Long> projects);

并且当我通过有效且非空的List时它正在工作.但是，当我传递可能发生的空列表并且在我的场景中这并不罕见时，我得到:

and it is working when I pass valid and not empty List<Long>. However when I pass empty list which may happen and it is not unusual in my scenario I get:

org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

谁能告诉我如何摆脱它?

Can anyone give me a hint on how to get rid of it?

推荐答案

postgresql 文档 states:in 谓词不能容纳空列表

The postgresql documentation states: in predicate can not hold empty list

expression IN (value [, ...])

并且由于 hibernate 按原样传递本机查询，可以预见这会导致问题.

and as hibernate passes the native queries as is, it's predictable that this leads to a problem.

但作为解决方法，您可以遵循此建议:用不可能的值初始化列表.

But as workaround you can follow this suggestion: initialize the list with an impossible value.

编辑

实际上，此行为在 hibernate 5.4.10 中已更改.请参阅 提交:

Actually, this behaviour was changed in hibernate 5.4.10. See the commit:

SHA-1: ab9ae431858dc6727023d7f03bd6925f99011c62

* HHH-8901 replace "in ()" SQL with "in (null)" in QueryParameterBindingsImpl

对于您的情况，将生成以下 sql:

And for your case the following sql will be generated:

select * from history h where h.project_id in (null)

任务HHH-8901的描述与这些变化完全无关，这看起来很奇怪.

It looks strange that the task HHH-8901 description completely irrelevant to these changes.

这篇关于表达式中的空导致本机查询异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！