Criteria equivalent of select * from a where id not in (...)

jamiebarrow · **Joined:** Fri Aug 22, 2008 4:24 am **Posts:** 2

Hi,

I'm using version 3.3.1.ga with Spring JPA Template and am new to Hibernate, and am trying to perform the following:

There is a Users(id, surname) table and an Employee(id, salary) table with a foreign key to the Users table's id.

I want to find all users that aren't employees, so basically the below SQL:

Code:

select * 
from users u 
where 
  id not in (
    select id from employees
  )
and surname like 'smith%';

There's a one to one relationship between Users and Employee (below is simplified version):

Code:

class User {

  @OneToOne(fetch = FetchType.LAZY)
  Employee employee;
}

class Employee {

  @OneToOne((fetch = FetchType.EAGER)
  @JoinColumn(name = "id")
  User user;

}

It's working fine using native sql, but I would like to use Criteria, is this possible? I'm not sure how to do it, this is what I tried:

Code:

    EntityManager entityManager = getJpaTemplate().getEntityManagerFactory().createEntityManager();
    Session session = (Session) entityManager.getDelegate();
    final Criteria criteria = session.createCriteria(getEntityClass());

    if (StringHelper.isNotEmpty(surname)) {
      criteria.add(Expression.ilike("surname", surname + "%"));
    }

    criteria.createAlias("employee", "employee");
    criteria.add(Expression.neProperty("id","employee.id"));

    try {
          return getJpaTemplate().executeFind(new JpaCallback() {
            public Object doInJpa(EntityManager em) throws PersistenceException {
              return criteria.list();
            }
          });
        } catch (RuntimeException re) {
          logger.error("find all failed", re);
          throw re;
        }

Any help would be appreciated, I don't particularly want to use the native SQL way, although it does work for now.

schauder · **Joined:** Thu Jul 05, 2007 9:38 am **Posts:** 287

Have a look at these Criterions

http://www.hibernate.org/hib_docs/v3/ap ... .Criterion)

http://www.hibernate.org/hib_docs/v3/ap ... Collection)

jamiebarrow · **Joined:** Fri Aug 22, 2008 4:24 am **Posts:** 2

Thanks, didn't spot the not Criterion before >.< Solved it by using:

Code:

    Criteria criteriaEmployeeIdList =
            createCriteria(Employee.class).setProjection(Projections.id());
    List employeeIdList = criteriaEmployeeIdList.list();
    criteria.add(Expression.not(Expression.in("id", employeeIdList )));

I also got an HQL Query working and tested, seems the Criteria way is faster too.

Thanks again.