java - Hibernate/SQL 多重连接查询精确匹配关系

Question

我有两个表，A 和 B。A 与 B 具有 @ManyToMany 关系。我的目标是选择与 B 具有确切某种关系的所有“A”。这是一个简单的示例:

@Entity
class A implements java.io.Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Integer id;

    @ManyToMany
    public java.util.Set<B> bs = new java.util.HashSet<>();

}

@Entity
public class B implements java.io.Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Integer id;
}


A a_only1 = new A();
A a_only2 = new A();
A a_both = new A();

B b1 = new B();
persist(b1);
a_only1.bs.add(b1);
a_both.bs.add(b1);

B b2 = new B();
persist(b2);
a_only2.bs.add(b2);
a_both.bs.add(b2);

persist(a_only1);
persist(a_only2);
persist(a_both);

// trying to query "a_only1"
query("SELECT x FROM A x "
    + "INNER JOIN x.bs y1 "
    + "WHERE y1 = "+b1.id);
    // returns "a_only1" and "a_both"

// trying to query "a_only2"
query("SELECT x FROM A x "
    + "INNER JOIN x.bs y1 "
    + "WHERE y1 = "+b2.id);
    // returns "a_only2" and "a_both"

// trying to query "a_both"
query("SELECT x FROM A x "
    + "INNER JOIN x.bs y1 "
    + "INNER JOIN x.bs y2 "
    + "WHERE y1 = "+b1.id+" "
    + "AND y2 = "+b2.id);
    // returns "a_both" (OK)

我能找到的关于它的最好的主题是这个:MySQL query to retrieve items that have an exact match in a many-to-many relationship我认为这与我想要的不完全一样，我无法重新安排在 HQL 中工作。

主要问题是我的查询找到了正确的关系，但忽略了其余的关系。我需要 100% 匹配的关系。

我也尝试过直接使用 SQL，但没有成功。

谢谢!

Answer 1

我认为您对 [NOT] MEMBER OF HQL feature 感兴趣:

试试这个:

List result = session.createQuery(
    "select distinct x from A x, B y where y.id=:xid and y MEMBER OF a.bs"
)
.setParameter("xid", b1.id)
.list();

或

List result = session.createQuery(
    "select distinct x from A x left join a.bs with bs.id = :xid"
)
.setParameter("xid", b1.id)
.list();