Java Hibernate 中带有命名查询的可选参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2444603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Optional parameters with named query in Hibernate?
提问by Ickster
Is there any way to specify optional parameters (such as when search parameters are provided from a form and not all parameters are required) in a named query when using Hibernate? I'm using a native SQLquery, but the question is probably applicable to named HQLqueries as well.
使用Hibernate时，是否有任何方法可以在命名查询中指定可选参数（例如，当从表单提供搜索参数且并非所有参数都需要时）？我正在使用本机SQL查询，但该问题可能也适用于命名HQL查询。
I'm pretty sure the answer to this is 'no', but I haven't found the definitive answer in the documentation yet.
我很确定这个答案是否定的，但我还没有在文档中找到明确的答案。
采纳答案by Pascal Thivent
AFAIK, there is no such thing so you'll have to write a dynamic query for this. Maybe have a look at this previous answershowing how to do this in HQL (that you can transpose to SQL) and also showing how the Criteria API makes it simpler and is thus better suited for this job in my opinion.
AFAIK，没有这样的事情，所以你必须为此编写一个动态查询。也许看看这个以前的答案，展示了如何在 HQL 中执行此操作（您可以将其转换为 SQL），并展示 Criteria API 如何使其更简单，从而在我看来更适合这项工作。
Update:(answering a comment from the OP) Working with a legacy database can be indeed tricky with Hibernate. Maybe you can use a dynamic native query and return non-managed entitiesthough. But on the long run, things might get worse (I can't tell that for you). Maybe Hibernate is not the best choice in your case and something like iBATIS would give you the flexibility you need.
更新：（回答来自 OP 的评论）使用 Hibernate 处理遗留数据库确实很棘手。也许您可以使用动态本机查询并返回非托管实体。但从长远来看，事情可能会变得更糟（我不能告诉你）。也许 Hibernate 不是您的最佳选择，像 iBATIS 这样的东西会给您所需的灵活性。
回答by Ian Jones
As mentioned in a different answerto the questionreferenced previously, the following HQL construct works for me:
正如前面提到的问题的不同答案中提到的，以下 HQL 构造对我有用：
select o from Product o WHERE :value is null or o.category = :value
if :valueis passed in as null, all Products are returned.
如果:value传入 as null，则返回所有产品。
See also Optional or Null Parameters
另请参阅可选或空参数
Note that this won't work in some versions of Sybase due to this bug, so the following is an alternative:
请注意，由于此错误，这在某些版本的 Sybase 中不起作用，因此以下是替代方法：
select o from Product o WHERE isnull(:value, 1) = 1 or o.category = :value
回答by Kosima
unfortunately the solution under "Optional or Null Parameters" does not work for IN lists. I had to changed the query as followed ...
不幸的是，“可选或空参数”下的解决方案不适用于 IN 列表。我不得不按如下方式更改查询...
Named query definition:
命名查询定义：
select ls from KiCOHeader co
...
join lu.handlingType ht
where (:inHandlingTypesX = 1 OR ht.name in (:inHandlingTypes))
Code:
代码：
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();
Much fun working.
工作很有趣。
回答by Jon Williams
Another solution for handling optional list parameters is by checking for null using the COALESCEfunction. COALESCE is supported by Hibernatereturns the first non-null parameter from a list, allowing you to check for null on a list without breaking the syntax when there are multiple items in the list.
处理可选列表参数的另一种解决方案是使用COALESCE函数检查空值。Hibernate 支持COALESCE从列表中返回第一个非空参数，当列表中有多个项目时，允许您检查列表上的空值而不会破坏语法。
HQL example with optional parameter and list parameter:
带有可选参数和列表参数的 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 )
This worked for me with a SQL Server dialect.
这对我使用 SQL Server 方言有效。
回答by georgeos
For those having issues with NULL values, another option is to use an alternate value. In my case, I used only positive values for my category field, that allows me to use as my alternate value = -1.
对于那些对 NULL 值有问题的人，另一种选择是使用替代值。就我而言，我的类别字段仅使用正值，这允许我用作备用值 = -1。
Therefore, before to execute the query, you can make a small validation:
因此，在执行查询之前，您可以进行一个小的验证：
if(value==null) {
value = -1;
}
....
....
select p from Product p WHERE :value = -1 or p.category = :value
