在将空集作为 SQL IN 参数设置时发生 SQLGrammarException 异常

Question

当我将一个空集合设置为 SQL IN 参数时，我的代码会引发 SQLGrammarException。

Query query = this.entMngr.createNativeQuery("SELECT foo_id, first, last FROM foo WHERE bar IN :barSet");
//barSet is a Set<Integer>
query.setParameter("barSet", barSet);
//this throws exception
List<Object> nativeList =  query.getResultList();

当集合不为空时，一切都能正常工作。如何使其不依赖于集合（或任何提交的集合）是否有数据？

- amphibient

---

从逻辑上讲，使用空集合进行查询的结果应该是一个空的结果集。因此，我会特殊处理 barSet.empty() 并返回 Collections.emptyList()。 - Dathan

@Dathan 这样做可以让代码运行速度更快，但会增加代码的复杂性。在这里，我更喜欢可维护性更强的代码（使用 barSet.add(null);）而不是更快的代码（检查特殊情况）。 - dened

当然，我明白你的想法。但是当我发表评论时，并不关心效率；我认为插入不明显的代码以适应 SQL 边缘情况比使用早期返回的显式检查更加“复杂”，而且肯定不如后者易读。前者需要注释进行文档说明，而后者是非常常见和被理解的习语。 - Dathan

@Dathan 显式检查还需要注释说明为什么不应该删除这个额外的检查...如果查询中有多个IN子句，你会怎么做？.. - dened

@dened 我认为if语句不需要自己的评论--我觉得它非常直观明了。但是多个IN子句的情况是一个非常好的观点--在这种情况下，使用null技巧比我能想到的其他任何方法都更清晰地处理该情况。 - Dathan

@Dathan 实际上这里的问题不是多个 IN 子句，而是可能会出现一些查询即使有一个“空”的 IN 子句也返回非空结果的情况。另外一个问题是，如果你更改了一个查询，导致它开始返回即使有“空”IN子句也返回非空结果，那么你还应该对显式检查做点什么，如果你忘记了这样做，你就会遇到麻烦... 如果使用NULL技巧，则不会出现这些问题。 - dened

Answer 1

这里的问题在于SQL语法不允许空的IN子句。因此，在您的情况下，barSet不应为空。但是，您可以在将其传递给查询之前，只需向集合中添加null：

barSet.add(null);
query.setParameter("barSet", barSet);

您可以在这里了解此技巧：SQL In语句带零到多个参数。

- dened

Answer 2

Hibernate中的In子句可以通过Query.setParameterList()https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/Query.html#setParameterList(java.lang.String, java.util.Collection)方法实现。

您需要使用此方法并将Collection或Array作为参数传递。

Query query = this.entMngr.createNativeQuery("SELECT foo_id, first, last FROM foo WHERE bar IN :barSet");
//barSet is a Set<Integer>
query.setParameterList("barSet", barSet); // as barSet is a Collection.
//this throws exception
List<Object> nativeList =  query.getResultList();

- ares