在Hibernate中使用命名查询时如何使用可选参数？

Question

在使用Hibernate时，是否有办法指定可选参数（例如当搜索参数来自表单且不是所有参数都是必需的时）在命名查询中呢？我正在使用本地SQL查询，但这个问题可能也适用于命名HQL查询。

我很确定答案是“否”，但我还没有在文档中找到明确的答案。

- Ickster

Answer 1

如之前所提到的不同的答案以及所引用的问题,下面的HQL语句构建对我有效:

select o from Product o WHERE :value is null or o.category = :value

如果传入 null，则返回所有产品。请参见可选或空参数。请注意，由于此错误，这在某些版本的Sybase中不起作用，因此以下是另一种选择：

select o from Product o WHERE isnull(:value, 1) = 1 or o.category = :value

- Ian Jones

---

当使用':value is null'时，我收到错误消息'Illegal use of keyword NULL'。当使用'isnull(:value, 1) = 1'时，我收到错误消息'The left expression is not a valid expression'。 - Atul Kumbhar

你可以使用 .. WHERE isnull(:value, o.category) = o.category ..。 - Siamand

我们曾经使用这个解决方案一段时间，直到我们进行了负载测试并意识到对于那些未使用可选参数的情况，该解决方案出现了显著的查询性能下降。（大表、几百万条记录、DB2数据库，约四五个可选参数）。 - Bernhard

@Ian Jones，“可选或空参数”链接是404。 - Archmede

Answer 2

据我所知，这种东西并不存在，因此您需要编写一个动态查询。也许可以查看一下这个之前的答案，展示了如何在HQL中进行此操作（您可以将其转换为SQL），并且还展示了Criteria API如何使其更简单，因此我认为它更适合这项工作。

更新：（回答OP的评论）使用Hibernate处理遗留数据库确实会很棘手。也许您可以使用动态本地查询并返回非托管实体。但从长远来看，情况可能会变得更糟（我无法为您确定）。也许Hibernate不是您的最佳选择，类似iBATIS的东西会给您带来所需的灵活性。

- Pascal Thivent

---

谢谢。这基本上是我想到的。我熟悉你在另一个答案中提到的两种方法，但是我被一个噩梦般的遗留数据库模式所困扰，它并不适合我的特定情况使用Hibernate方法。到目前为止，我已经让它工作了，但是有一些新的要求，我似乎无法使用纯Hibernate映射来满足。我可能会放弃并在这个特定情况下使用iBATIS。添加另一种技术作为应急措施并不是很令人兴奋，但这就是生活吧。谢谢。 - Ickster

谢谢你的更新。我也考虑过那种方法，但考虑到我要处理的参数数量，我决定还是使用iBATIS更加简洁明了。感谢你的建议。 - Ickster

Answer 3

很不幸，“Optional or Null Parameters”下的解决方案不能用于IN列表。

我不得不按照以下方式更改查询...

命名查询定义：

select ls from KiCOHeader co
...
join lu.handlingType ht
where (:inHandlingTypesX = 1 OR ht.name in (:inHandlingTypes))

代码：

Set<KiHandlingTypeEnum> inHandlingTypes = ...

Query query = persistence.getEm().createNamedQuery("NAMED_QUERY");
query.setParameter("inHandlingTypesX", (inHandlingTypes == null) ? 1 : 0);
query.setParameter("inHandlingTypes", inHandlingTypes);

List<KiLogicalStock> stocks = query.getResultList();

工作非常有趣。

- Kosima

Answer 4

另一种处理可选列表参数的解决方案是使用COALESCE函数检查null。COALESCE被Hibernate支持，它返回列表中第一个非空参数，允许您在列表上检查null而不会在列表中有多个项时破坏语法。

带有可选参数和列表参数的HQL示例：

select obj from MyEntity obj
where ( COALESCE( null, :listParameter ) is null or obj.field1 in (:listParameter) )
  and ( :parameter is null or obj.field2 = :parameter )

这对我来说在使用SQL Server方言时有效。

- Dubs

---

这对我不起作用，Hibernate 在未定义:listParameter时抱怨COALESCE(null, )。我的解决方案是传递一个额外的参数，并以这种方式编写where条件：(:listParamterSize = 0 或 obj.field1 在 (:listParameter) 中) - jeremija

它可以工作，但是:listParameter必须被定义，至少为null。这种解决方案的优点是您可以将参数传递为null，而不需要额外的参数。 - Daniel Olszewski

Answer 5

对于那些遇到 NULL 值问题的人，另一个选项是使用替代值。在我的情况下，我仅使用正值作为我的类别字段，这使我可以使用 -1 作为我的替代值。

因此，在执行查询之前，您可以进行小型验证：

if(value==null) {
   value = -1;
}
....
....
select p from Product p WHERE :value = -1 or p.category = :value

- georgeos