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:
Why doesnt Hibernate
translate a null string correctly to "is null" (and rather and wrongly creates "=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"?
You have to use is null and is not null in HQL.
Positional parameters It's use question mark (?) to define a named parameter, and you have to set your parameter according to the position sequence. See example… String hql = "from Stock s where s. stockCode = ? and s.
JPQL is Java Persistence Query Language defined in JPA specification. It is used to create queries against entities to store in a relational database. JPQL is developed based on SQL syntax. But it won't affect the database directly.
The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.
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
.
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();
This is not a Hibernate specific issue (it's just SQL nature), and YES, there IS a solution for both SQL and 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 ;-)
The below code absolutely works and it is great if you keep all your queries in orm.xml
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.
Notes:
The issue may be a specific MySql quirk. I only tested with Oracle.
The above query assumes that there are table rows where c.status is null
The where clause is prioritized so that the parameter is checked first.
The parameter name 'type' may be a reserved word in SQL but it shouldn't matter since it is replaced before the query runs.
If you needed to skip the :status where_clause altogether; you can code like so:
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 ");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With