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

Question

我有两个表，A和B。A与B有@manytoman关系。我的目标是选择所有与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();

Answer 2

我知道了。不确定是否最好，但有效：

SELECT x FROM A x INNER JOIN x.bs y1 WHERE y1 = :bid AND size(x.bs) = 1

现在，如果我有更多的关系，我可以添加另一个连接并增加大小以适应内部连接数

编辑

我认为这是一个更好的解决方案：

em.createQuery("SELECT x FROM A x "
                + "WHERE :listB in elements(x.bs) "
                + "AND length(:listB) = size(x.bs)"
                ).setParameter("listB", listOfB).getResultList();

但如果我在listOfB中添加多个元素，则不起作用。我怎样才能修好它