使用hql表达式而非Criteria进行动态HQL查询？

问题描述

我正在尝试编写一个部分动态的HQL查询，因为各种原因无需诉诸Criteria API。我想知道是否有一种简单的方法来使用HQL表达式来限制其中的限制。例如，以下是可以正常工作的原始查询：

  SELECT customer 
 FROM Customer as customer 
 INNER JOIN customer.profile as profile 
 WHERE profile.status IN：statusCodes 
 AND profile.orgId IN：orgIds 
  

StatusCodes是一个字符串列表，orgIds是一个整数列表。但是，任何一个都是可选的，如果传递null而不是集合，则不应限制。我试图完成这样的事情：

  SELECT customer 
 FROM Customer as customer 
 INNER JOIN customer.profile as profile 
 WHERE（：statusCodes IS NULL OR profile.status IN：statusCodes）
 AND（：orgIds IS NULL or profile.orgId IN：orgIds）
  

这不起作用，但是有没有其他方法可以使用，或者使用不同的表达式或传递默认值？编辑：只是要清楚我正在寻找一种方法来使用NamedQuery，而不是以任何方式动态构建查询。

解决方案：我使用额外的查询参数来完成它。我创建了两个帮助器方法：

pre $ private $ {code> private void setRequiredParameter（TypedQuery<> query，String name，Object value）{
query.setParameter（name，value）;
}

private void setOptionalParameter（TypedQuery<> query，String name，Object value）{
query.setParameter（name，value）;
query.setParameter（name +Optional，value == null？1：0）;
}

查询就像这样：

  SELECT customer 
 FROM Customer as customer 
 INNER JOIN customer.profile as profile 
 WHERE（：statusCodesOptional = 1 OR profile.status IN ：statusCodes）
 AND（：orgIdsOptional = 1 OR profile.orgId IN：orgIds）
  

解决方案

如果您绝对必须避免动态查询，您可以牺牲另外两个参数来做到这一点：

  SELECT customer 
 FROM Customer AS customer 
 JOIN customer.profile AS profile 
 WHERE（profile.status IN：statusCodes OR：statusCodeCount = 0）
 AND（profile。 orgId IN：orgIds OR：orgIdCount = 0）
  

在您的Java代码中，您可以执行如下操作：
$ b

  session.getNamedQuery（your.query.name）
 .setParameterList（statusCodes，statusCodes）
 .setPar ameter（statusCodeCount，statusCodes.length）
 .setParameterList（orgIds，orgIds）
 .setParameter（orgIdCount，orgIds.length）; 
  

您需要确保数组是零长度而不是 null 或者提供额外的if检查来处理 null 情况。

所有这些都说HQL是真的更适合定义明确的（如静态）查询。您可以解决动态参数问题，您将无法解决动态排序问题。

I am trying to write a partially dynamic HQL query without resorting to the Criteria API for various reasons. I wanted to know if there is an easy way to short circuit a where restriction using HQLs expressions. For example, here is the original query which works fine:

SELECT customer
FROM Customer as customer
INNER JOIN customer.profile as profile
WHERE profile.status IN :statusCodes
AND   profile.orgId IN :orgIds

StatusCodes is a list of Strings and orgIds is a list of Integers. However, either one is optional and shouldn't restrict if null is passed instead of a collection. I've tried to accomplish this like so:

SELECT customer
FROM Customer as customer
INNER JOIN customer.profile as profile
WHERE (:statusCodes IS NULL OR profile.status IN :statusCodes)
AND   (:orgIds IS NULL OR profile.orgId IN :orgIds)

This didn't work unfortunately, but is there any other approach that might work, either with using different expressions or passing in default values?

EDIT: Just to be clear I'm looking for a way to use a NamedQuery, not dynamically building the query in any way.

SOLUTION: I used the extra query parameters to accomplish it. I created two helper methods:

private void setRequiredParameter(TypedQuery<?> query, String name, Object value) {
    query.setParameter(name, value);
}

private void setOptionalParameter(TypedQuery<?> query, String name, Object value) {
    query.setParameter(name, value);
    query.setParameter(name + "Optional", value == null ? 1 : 0);
}

And the query like so:

SELECT customer
        FROM Customer as customer
        INNER JOIN  customer.profile as profile
        WHERE (:statusCodesOptional = 1 OR profile.status IN :statusCodes)
        AND (:orgIdsOptional = 1 OR profile.orgId  IN :orgIds)

解决方案

If you absolutely must avoid dynamic queries, you can do so at the expense of two additional parameters:

SELECT customer
  FROM Customer AS customer
  JOIN customer.profile AS profile
 WHERE (profile.status IN :statusCodes OR :statusCodeCount = 0)
   AND (profile.orgId IN :orgIds OR :orgIdCount = 0)

In your Java code you would then do something like:

session.getNamedQuery("your.query.name")
       .setParameterList("statusCodes", statusCodes)
       .setParameter("statusCodeCount", statusCodes.length)
       .setParameterList("orgIds", orgIds)
       .setParameter("orgIdCount", orgIds.length);

You'll need to ensure arrays are zero-length rather than null or supply additional if checks to handle null scenario.

All that said, HQL is really better suited for well-defined (e.g. static) queries. You can work around dynamic parameters, you won't be able to work around dynamic sorting.

这篇关于使用hql表达式而非Criteria进行动态HQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！