表达式为空会在本机查询中导致异常 [英] 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< Long> 时，它可以正常工作.但是，当我传递可能会发生的空列表时，这种情况在我的情况中并不罕见，我得到:

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文档

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

expression IN (value [, ...])

而且，随着冬眠原样传递本地查询，可以预见这会导致问题.

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.

编辑

实际上，此行为已在休眠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屋！