Hibernate Criteria查询：获取具有m..n关系的对象的列表，其中子表没有特定的属性 [英] Hibernate Criteria query: getting a list of objects with a m..n relationship where child table does not have a certain property

问题描述

我有以下表格：

pre $ CREATE TABLECOMPANIES（
IDNUMBER NOT NULL，
NAMEVARCHAR2（100）NOT NULL UNIQUE
）
/

CREATE TABLECOMPANIESROLES（
IDNUMBER NOT NULL，
COMPANYIDNUMBER NOT NULL，
ROLENAMEVARCHAR2（30）NOT NULL
）
/

CREATE TABLEROLES（
NAMEVARCHAR2（30）NOT NULL
）
/


这个结构代表许多公司和每个公司允许的角色。对于这些表，有相应的Hibernate对象：

  public class Company实现Serializable {
 
 private长ID; 
私人字符串名称; 
私人设置<角色> companyRoles; 
 
（为便于阅读，省略了getters和setter）
} 
 
公共类角色实现Serializable {
 
私有字符串名称; 
 
（为便于阅读，省略了getters和setter）
} 
  

<使用Hibernate Criteria API找出所有具有特定角色的公司是没有问题的：

  Session session = this。 sessionFactory.getCurrentSession（）; 
 Criteria criteria = session.createCriteria（Company.class）; 
 criterion = Restrictions.eq（companyRoles.name，ADMIN）; 
 criteria.add（标准）; 
清单<公司> companyList = criteria.list（）; 
  

Hibernate将其转换为SQL查询（大约）

  SELECT * 
 FROM公司this_ 
内部联接companyroles cr2_ 
 ON this_.id = cr2_.companyid 
内部联接角色role1_ 
 ON cr2_.rolename = role1_.NAME 
 WHERE role1_.NAME ='ADMIN'
  

现在的问题是：我怎样才能撤消查询，即查找所有没有ADMIN角色映射的公司？如果我只是试图通过设置

  criterion = Restrictions.ne（companyRoles.name，ADMIN）来颠倒标准; 
  

（不等于等于），Hibernate创建一个像这样的查询

  SELECT * 
 FROM companies this_ 
 inner join companyroles cr2_ 
 ON this_.id = cr2_.companyid 
内部联接角色role1_ 
 ON cr2_.rolename = role1_.NAME 
 WHERE role1_.NAME！='ADMIN'
  

显然，这并不会产生所需的输出，因为该列表中仍然包含具有ADMIN角色的公司，只要这些公司至少有一个其他角色。

我想要的是一个没有ADMIN角色的公司列表。作为一个附加的限制，如果可能的话，只要修改Criterion对象就可以实现这一点（这是因为该标准是作为内部框架的一部分自动构建的，并且不可能在那里进行更大的更改）。 当Criteria对象包含其他额外标准时，解决方案也应该可以工作。

这是可行的吗？

解决方案

您需要一个子查询（DetachedCriteria）。

  DetachedCriteria sub = DetachedCriteria.forClass（Company.class）; 
 criterion = Restrictions.eq（companyRoles.name，ADMIN）; 
 sub.add（标准）; 
 sub.setProjection（Projections.property（id））; 
 Criteria criteria = session.createCriteria（Company.class）; 
 criteria.add（Property.forName（id）。notIn（sub））; 
清单<公司> companyList = criteria.list（）; 
  

类似的东西应该可以做到。

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?

解决方案

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.

这篇关于Hibernate Criteria查询：获取具有m..n关系的对象的列表，其中子表没有特定的属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！