Java Hibernate：如何使用 HQL 设置 NULL 查询参数值？

Hibernate: How to set NULL query-parameter value with HQL?

提问by tim

How can I set a Hibernate Parameter to "null"? Example:

如何将休眠参数设置为“空”？例子：

Query query = getSession().createQuery("from CountryDTO c where c.status = :status  and c.type =:type")
.setParameter("status", status, Hibernate.STRING)
.setParameter("type", type, Hibernate.STRING);

In my case, the status String can be null. I have debugged this and hibernate then generates an SQL string/query like this ....status = null... This however does not Work in MYSQL, as the correct SQL statement must be "status is null" (Mysql does not understand status=null and evaluates this to false so that no records will ever be returned for the query, according to the mysql docs i have read...)

就我而言，状态字符串可以为空。我已经调试了这个，然后休眠然后生成一个像这样的 SQL 字符串/查询 .... status = null... 但是这在 MYSQL 中不起作用，因为正确的 SQL 语句必须是“ status is null”（Mysql 不理解 status=null 并评估这个根据我读过的 mysql 文档，设置为 false 以便查询不会返回任何记录......）

My Questions:

我的问题：

1. Why doesnt Hibernatetranslate a null string correctly to "is null" (and rather and wrongly creates "=null")?

2. What is the best way to rewrite this query so that it is null-safe? With nullsafe I mean that in the case that the "status" String is null than it should create an "is null"?

1. 为什么不Hibernate将空字符串正确转换为“为空”（而是错误地创建“=null”）？

2. 重写此查询以使其为空安全的最佳方法是什么？使用 nullsafe 我的意思是，在“状态”字符串为空的情况下，它应该创建一个“为空”？

采纳答案by Sergey Demin

1. I believe hibernate first translates your HQL query to SQL and only after that it tries to bind your parameters. Which means that it won't be able to rewrite query from param = ?to param is null.

2. Try using Criteria api:

   Criteria c = session.createCriteria(CountryDTO.class);
   c.add(Restrictions.eq("type", type));
   c.add(status == null ? Restrictions.isNull("status") : Restrictions.eq("status", status));
   List result = c.list();
   

1. 我相信 hibernate 首先将您的 HQL 查询转换为 SQL，然后才尝试绑定您的参数。这意味着它将无法从param = ?to重写查询param is null。

2. 尝试使用 Criteria api：

   Criteria c = session.createCriteria(CountryDTO.class);
   c.add(Restrictions.eq("type", type));
   c.add(status == null ? Restrictions.isNull("status") : Restrictions.eq("status", status));
   List result = c.list();
   

回答by skaffman

The javadoc for setParameter(String, Object)is explicit, saying that the Object value must be non-null. It's a shame that it doesn't throw an exception if a null is passed in, though.

该对的javadocsetParameter(String, Object)是明确的，说对象值必须非空。遗憾的是，如果传入 null，它不会引发异常。

An alternative is setParameter(String, Object, Type), which doesallow null values, although I'm not sure what Typeparameter would be most appropriate here.

另一种选择是setParameter(String, Object, Type)，它确实允许空值，但我不确定Type这里最合适的参数是什么。

回答by Peter Lang

I did not try this, but what happens when you use :statustwice to check for NULL?

我没有尝试这个，但是当你使用:status两次来检查时会发生什么NULL？

Query query = getSession().createQuery(
     "from CountryDTO c where ( c.status = :status OR ( c.status IS NULL AND :status IS NULL ) ) and c.type =:type"
)
.setParameter("status", status, Hibernate.STRING)
.setParameter("type", type, Hibernate.STRING);

回答by Eran Medan

It seems you have to use is nullin the HQL, (which can lead to complex permutations if there are more than one parameters with null potential.) but here is a possible solution:

似乎您必须is null在 HQL 中使用，（如果有多个参数可能为空，则可能导致复杂的排列。）但这是一种可能的解决方案：

String statusTerm = status==null ? "is null" : "= :status";
String typeTerm = type==null ? "is null" : "= :type";

Query query = getSession().createQuery("from CountryDTO c where c.status " + statusTerm + "  and c.type " + typeTerm);

if(status!=null){
    query.setParameter("status", status, Hibernate.STRING)
}


if(type!=null){
    query.setParameter("type", type, Hibernate.STRING)
}

回答by Chris S

For an actual HQL query:

对于实际的 HQL 查询：

FROM Users WHERE Name IS NULL

回答by onexdrk

You can use

您可以使用

Restrictions.eqOrIsNull("status", status)

insted of

插入的

status == null ? Restrictions.isNull("status") : Restrictions.eq("status", status)

回答by Yannis Sermetziadis

Here is the solution I found on Hibernate 4.1.9. I had to pass a parameter to my query that can have value NULL sometimes. So I passed the using:

这是我在 Hibernate 4.1.9 上找到的解决方案。我不得不将一个参数传递给我的查询，有时它的值可能为 NULL。所以我通过了使用：

setParameter("orderItemId", orderItemId, new LongType())

After that, I use the following where clause in my query:

之后，我在查询中使用以下 where 子句：

where ((:orderItemId is null) OR (orderItem.id != :orderItemId))

As you can see, I am using the Query.setParameter(String, Object, Type) method, where I couldn't use the Hibernate.LONG that I found in the documentation (probably that was on older versions). For a full set of options of type parameter, check the list of implementation class of org.hibernate.type.Type interface.

如您所见，我使用的是 Query.setParameter(String, Object, Type) 方法，我无法使用我在文档中找到的 Hibernate.LONG（可能是旧版本）。类型参数的全套选项，查看org.hibernate.type.Type接口的实现类列表。

Hope this helps!

希望这可以帮助！

回答by egallardo

This is not a Hibernate specific issue (it's just SQL nature), and YES, there IS a solution for both SQL and HQL:

这不是特定于 Hibernate 的问题（这只是 SQL 性质），是的，SQL 和 HQL 都有解决方案：

@Peter Lang had the right idea, and you had the correct HQL query. I guess you just needed a new clean run to pick up the query changes ;-)

@Peter Lang 有正确的想法，您有正确的 HQL 查询。我猜你只需要一个新的干净运行来获取查询更改;-)

The below code absolutely works and it is great if you keep all your queries in orm.xml

下面的代码绝对有效，如果您将所有查询保存在 orm.xml 中，那就太好了

from CountryDTO c where ((:status is null and c.status is null) or c.status = :status) and c.type =:type

from CountryDTO c where ((:status is null and c.status is null) or c.status = :status) and c.type =:type

If your parameter String is null then the query will check if the row's status is null as well. Otherwise it will resort to compare with the equals sign.

如果您的参数 String 为空，则查询将检查行的状态是否也为空。否则它将求助于与等号进行比较。

Notes:

笔记：

The issue may be a specific MySql quirk. I only tested with Oracle.

问题可能是特定的 MySql 怪癖。我只用 Oracle 测试过。

The above query assumes that there are table rows where c.status is null

上述查询假设存在 c.status 为 null 的表行

The where clause is prioritized so that the parameter is checked first.

where 子句具有优先级，以便首先检查参数。

The parameter name 'type' may be a reserved word in SQL but it shouldn't matter since it is replaced before the query runs.

参数名称 'type' 可能是 SQL 中的保留字，但它应该无关紧要，因为它在查询运行之前已被替换。

If you needed to skip the :status where_clause altogether; you can code like so:

如果您需要完全跳过 :status where_clause; 你可以这样编码：

from CountryDTO c where (:status is null or c.status = :status) and c.type =:type

from CountryDTO c where (:status is null or c.status = :status) and c.type =:type

and it is equivalent to:

它相当于：

sql.append(" where ");
if(status != null){
  sql.append(" c.status = :status and ");
}
sql.append(" c.type =:type ");

回答by Arjan

HQL supports coalesce, allowing for ugly workarounds like:

HQL 支持coalesce，允许丑陋的解决方法，例如：

where coalesce(c.status, 'no-status') = coalesce(:status, 'no-status')

回答by Wim Berchmans

this seems to work as wel ->

这似乎也有效->

@Override
public List<SomeObject> findAllForThisSpecificThing(String thing) {
    final Query query = entityManager.createQuery(
            "from " + getDomain().getSimpleName() + " t  where t.thing = " + ((thing == null) ? " null" : " :thing"));
    if (thing != null) {
        query.setParameter("thing", thing);
    }
    return query.getResultList();
}

Btw, I'm pretty new at this, so if for any reason this isn't a good idea, let me know. Thanks.

顺便说一句，我对此很陌生，所以如果出于任何原因这不是一个好主意，请告诉我。谢谢。