由于各种原因,我正在尝试编写一个部分动态的 HQL 查询,而无需求助于标准 API。我想知道是否有一种简单的方法可以使用 HQL 表达式来短路 where 限制。例如,这是工作正常的原始查询:
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的方法,而不是以任何方式动态构建查询。
解决方案:我使用了额外的查询参数来完成它。我创建了两个帮助程序方法:
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)
我的建议是将所有参数放在一个映射中并动态构建查询,在执行之前构建后,设置查询所需的所有参数,从映射中获取值:
Map<String, Object> pars = new HashMap<String,Object>();
pars.put("statusCodes", statusCodes);
pars.put("orgIds", orgIds);
StringBuilder b = "SELECT customer FROM Customer as customer INNER JOIN customer.profile as profile where 1 = 1";
if (statusCodes != null) {
b.append(" and profile.status in :statusCodes");
}
if (orgIds != null) {
b.append(" and profile.orgId in :statusCodes");
}
...
Query q = session.createQuery(b.toString());
...
for (String p : q.getNamedParameters()) {
q.setParameter(p, pars.get(p));
}
当然需要一些改进,例如在未设置参数时抛出异常,如果复杂度大于几个简单参数,则使用类型化参数等等。
如果绝对必须避免动态查询,则可以以牺牲两个附加参数为代价:
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 代码中,您将执行以下操作:
session.getNamedQuery("your.query.name")
.setParameterList("statusCodes", statusCodes)
.setParameter("statusCodeCount", statusCodes.length)
.setParameterList("orgIds", orgIds)
.setParameter("orgIdCount", orgIds.length);
您需要确保数组的长度为零而不是null
,或者提供额外的 if 检查来处理null
方案。
综上所述,HQL 确实更适合定义良好的(例如静态)查询。您可以处理动态参数,但无法解决动态排序。
您必须动态生成查询:
StringBuilder hql =
new StringBuilder("SELECT customer FROM Customer as customer INNER JOIN customer.profile as profile where 1 = 1")
if (statusCodes != null) {
hql.append(" and profile.status IN :statusCodes");
}
if (orgIds != null) {
hql.append(" and profile.orgId IN :orgIds");
}
当然,您还必须仅在参数不为 null 时才将参数设置为查询。