Hibernate:如何使用HQL设置空查询参数值?

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

如何将Hibernate参数设置为“null”?例子:

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...)

在我的例子中，状态字符串可以为空。我已经调试这个然后hibernate生成一个SQL字符串/查询这样....状态=零……但是，这在MYSQL中不起作用，因为正确的SQL语句必须是“status为null”(MYSQL不理解status=null，并将其计算为false，因此，根据我所读的MYSQL文档，没有任何记录会返回到查询中)。

My Questions:

我的问题:

Why doesnt Hibernate translate a null string correctly to "is null" (and rather and wrongly creates "=null")?

为什么Hibernate不正确地将一个空字符串转换为“null”(并且错误地创建“=null”)?
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"?

重写这个查询的最好方法是什么?对于nullsafe，我的意思是，在“状态”字符串为空的情况下，它应该创建一个“null”?

Thank you very much! Tim

非常感谢!蒂姆

10 个解决方案

#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.

我相信hibernate首先将您的HQL查询转换为SQL，然后它尝试绑定您的参数。这意味着它将无法重写来自param =的查询。参数是null。

Try using Criteria api:

尝试使用标准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();

#2

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查询。我猜您只是需要一个新的clean run来获取查询更改;-)

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

从CountryDTO c到where(:状态为null和c)。状态为空)或c。状态=:状态)和c。类型=:类型

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.

如果参数字符串为null，那么查询将检查该行的状态是否为空。否则，它将会与等号进行比较。

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。状态为空

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

从CountryDTO c到where (:status为null或c)。状态=:状态)和c。类型=:类型

and it is equivalent to:

它等于:

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

#3

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.

setParameter(字符串、对象)的javadoc是显式的，表示对象值必须是非空的。遗憾的是，如果null被传入，它不会抛出异常。

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

另一种方法是setParameter(String, Object, Type)，它允许null值，不过我不确定在这里哪种类型参数最合适。

#4

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

我没有尝试，但是当您使用:状态两次检查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);

#5

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

在HQL中，似乎必须使用null(如果有多个参数，则可能导致复杂的排列)，但这里有一个可能的解决方案:

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)
}

#6

For an actual HQL query:

对于实际的HQL查询:

FROM Users WHERE Name IS NULL

#7

HQL supports coalesce, allowing for ugly workarounds like:

HQL支持合并，允许出现难看的工作区:

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

#8

You can use

您可以使用

Restrictions.eqOrIsNull("status", status)

insted of

本月的

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

#9

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上找到的解决方案。我必须将一个参数传递给我的查询，它有时会有值为空。所以我通过了:

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.

如您所见，我正在使用查询。setParameter(String, Object, Type)方法，我不能使用Hibernate。我在文档中找到了(可能是旧版本)。对于完整的类型参数选项，请检查org.hibernate.type的实现类列表。接口类型。

Hope this helps!

希望这可以帮助!

#10

-1

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.

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

#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.

我相信hibernate首先将您的HQL查询转换为SQL，然后它尝试绑定您的参数。这意味着它将无法重写来自param =的查询。参数是null。