CODEWITHSUNDEEP
javasqlhibernate
simon
simon

Reputation: 12902

Hibernate Criteria query: getting a list of objects with a m..n relationship where child table does not have a certain property

I have the following tables

CREATE TABLE "COMPANIES" (
    "ID" NUMBER NOT NULL ,
    "NAME" VARCHAR2 (100) NOT NULL  UNIQUE
) 
/

CREATE TABLE "COMPANIESROLES" (
    "ID" NUMBER NOT NULL ,
    "COMPANYID" NUMBER NOT NULL ,
    "ROLENAME" VARCHAR2 (30) NOT NULL
) 
/

CREATE TABLE "ROLES" (
    "NAME" VARCHAR2 (30) NOT NULL
) 
/

This structure represents a number of companies and the roles allowed for each company. For these tables, there are the corresponding Hibernate objects:

public class Company implements Serializable {

    private Long id;
    private String name;
    private Set<Role> companyRoles;

    //(getters and setters omitted for readability)
}

public class Role implements Serializable {

        private String name;

        //(getters and setters omitted for readability)
}

Finding out all companies, which have a specific role using the Hibernate Criteria API is no problem:

Session session = this.sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(Company.class);
criterion = Restrictions.eq("companyRoles.name", "ADMIN");
criteria.add(criterion);
List<Company> companyList = criteria.list();

Hibernate translates this to an SQL query (approximately)

SELECT *
FROM   companies this_
      inner join companyroles cr2_
         ON this_.id = cr2_.companyid
       inner join roles role1_
         ON cr2_.rolename = role1_.NAME
WHERE role1_.NAME = 'ADMIN'

And now the problem: how can I reverse the query, i.e. find out all companies, which do not have a mapping for the role "ADMIN"? If I simply try to reverse the criterion by setting

criterion = Restrictions.ne("companyRoles.name", "ADMIN");

(not equals instead of equals), Hibernate creates a query like this

SELECT *
FROM   companies this_
      inner join companyroles cr2_
         ON this_.id = cr2_.companyid
       inner join roles role1_
         ON cr2_.rolename = role1_.NAME
WHERE role1_.NAME != 'ADMIN'

Obviously, this does not produce the desired output, as the list still contains companies having the role "ADMIN", as long as the companies have at least one other role.

What I want to have is a list of companies, which do not have the role "ADMIN". As an additional restriction, this should be doable by just modifying the Criterion object, if possible (this is because the criterion is built automatically as part of an internal framework, and it is not possible to make larger changes there). The solution should also work, when the Criteria object contains other, additional criterions.

How is this doable, or is it?

Upvotes: 2

Views: 8398

Answers (1)

Stijn Geukens
Stijn Geukens

Reputation: 15628

You need a subquery (DetachedCriteria).

DetachedCriteria sub = DetachedCriteria.forClass(Company.class);
criterion = Restrictions.eq("companyRoles.name", "ADMIN");
sub.add(criterion);
sub.setProjection(Projections.property("id"));
Criteria criteria = session.createCriteria(Company.class);
criteria.add(Property.forName("id").notIn(sub));
List<Company> companyList = criteria.list();

Something like that should do it.

Upvotes: 3

Related Questions