1

I am trying to write a DAO using Spring's HibernateTemplate that supports deletion from a table based on several conditions. For example, sample SQL:

Delete from Employee where Name='E01' AND Dept='D01' AND Address='ADR01';

I wrote the below code for this query:

session.createQuery("delete from Employee where name=? and dept=? and address=?")
       .setParameter(0, name).setParameter(1, dept).setParameter(2, address)
       .executeUpdate();

it works fine if all columns in where clause have some values. However, if I try to delete records which have "NULL" in their any column, then it does not work.

The generated SQL Query is always of the form:

Delete from Employee where Name=? AND Dept=? AND Address=?;

which of course cannot handle NULL comparison - SQL requires "IS NULL" for checking NULL and "=null" doesn't do the trick here. So when I pass dept as null in Java code, the generated SQL would be of the form:

Delete from Employee where Name='E01' AND Dept=null AND Address='ADR01';

This does not delete the records from DB which have NULL values in Dept column, as the correct condition would be "Dept IS NULL"; and "Dept=null" does not work! Is there anyway to compare NULL values in where clause without using a native query?

NOTE:

  1. I do not want to use deleteAll(Collection) method of HibernateTemplate, as it requires fetching the records first and then deleting them - i.e. more than 1 SQL query. I want to achieve deletion using a single SQL query, without requiring to select first, or requiring native queries.
  2. I am aware that Spring advices using SessionFactory now, but I am stuck with HibernateTemplate in existing code base.
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Pat
  • 2,223
  • 4
  • 19
  • 25
  • As per my understanding and another SO post http://stackoverflow.com/questions/2123438/hibernate-how-to-set-null-query-parameter-value-with-hql , I may be able to get away using Criteria API. However, there is no way to delete records with Criteria API. I can only fetch the records (SQL SELECT) and then delete them. I guess, I may as well do SELECT and then DELETE in this case. I am a bit annoyed that how such common support as deleteByCriteria NOT provided in the framework... :( – Pat Dec 05 '13 at 13:44

0 Answers0