0

How to write this query in HQL:

select * from Employee where Emp_Code 
NOT IN (select Emp_Code from EmployeeAllocation);

I was unable to find any solution for this on google. I dont know how to write NOT IN clause in HQL The result should must be fetched to a List. Like this:

List<String> lst = query.list();
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
user1228826
  • 9
  • 1
  • 3
  • http://stackoverflow.com/questions/1220901/how-to-achieve-not-in-by-using-restrictions-and-criteria-in-hibernate – Kevin Crowell Feb 23 '12 at 16:32
  • 1
    Have you read the reference manual about HQL? The HQL query would be very similar to the SQL one. You just have to use aliases, entity names instead of table names, and property names instead of column names. HQL works on entities. Since we don't know what your entities are, we can't answer. http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#queryhql – JB Nizet Feb 23 '12 at 17:01
  • Entity Names- Employee: Employee Employee_Allocation: Allocated Properties- Emp_Code in Employee: empCode Emp_Code in Allocated: employeeCode – user1228826 Feb 23 '12 at 18:01

1 Answers1

0

I think you can do it like, As you haven't provided any information about the table structure, otherwise I would have suggested you some better query.

But here in the below shown query I am just trying to tell you about the NOT IN clause in Hibernate or hql.

list = select Emp_Code from EmployeeAllocation

Criteria criteria = DetachedCriteria.forClass(Employee.class);
criteria.add(Restrictions.not(Restrictions.in("Emp_Code", list);

return getHibernateTemplate().findByCriteria(criteria);
Raj
  • 1