Hibernate：如何使用HQL设置NULL查询参数值？ [英] Hibernate: How to set NULL query-parameter value with HQL?

问题描述

如何将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）; 
  

在我的情况下，状态字符串可以为空。我已经调试了这个，然后hibernate生成一个像这样的SQL字符串/查询.... status = null ...然而这在MYSQL中不起作用，因为正确的SQL语句必须是 status is null （Mysql不理解status = null，并将其计算为false，以便根据mysql不会为查询返回任何记录docs i read ...）

我的问题：

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




2. 重写此查询的最佳方法是使其无效？ nullsafe的意思是说，在status字符串为空的情况下，应该创建一个为空？

非常感谢！
Tim

解决方案

1. 我相信hibernate首先会将您的HQL查询转换为SQL只有在它试图绑定你的参数。这意味着它不能重写来自 param =？到 param为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））; 
   列表结果= c.list（）; 
     

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

My Questions:

1. Why doesnt Hibernate translate 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"?

Thank you very much! Tim

解决方案

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();
   

这篇关于Hibernate：如何使用HQL设置NULL查询参数值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！