java如何使用HQL通过所述PK的列表过滤具有复合PK的实体？

Question

考虑：

@Entity
public class Foo {
    @EmbeddedId
    private FooPK pk;

    public FooPK getPk() { return pk; }
    public void setPk(FooPK pk) { this.pk = pk; }
}

及

@Embeddable
public class FooPK {
    @Column(name="ID_A")
    private Long idA;

    @Column(name="ID_B")
    private Long idB;

    public Long getIdA() { return idA; }
    public void setIdA(Long idA) { this.idA = idA; }

    public Long getIdB() { return idB; }
    public void setIdB(Long idB) { this.idB = idB; }
}

我有一个List<FooPK>，我正试图在HQL中用作过滤器：

-- Just an example of what I'm trying to do.
SELECT foo FROM Foo foo
WHERE foo.pk IN (:pkList)

这不起作用，因为pk不是单个值。我只是想列出我拥有pks的所有实体

我可以做一些强有力的事情，比如：

-- Ugly
SELECT foo FROM Foo foo
WHERE (
       (foo.pk.idA = :idA1 AND foo.pk.idB = :idB1)
    OR (foo.pk.idA = :idA2 AND foo.pk.idB = :idB2)
    OR (foo.pk.idA = :idA3 AND foo.pk.idB = :idB3)
    -- ...
)

但我相信你可以看到这是多么丑陋和不可缩放

我正在使用Java6/JPA1/Hibernate3

无论如何，我使用的是Oracle，我希望生成的SQL类似于：

-- This works fine in my database, assuming the schema contains the refered table and columns.
SELECT foo.ID_A, foo.ID_B
FROM Foo foo
WHERE (foo.ID_A, foo.ID_B) in ((?,?), (?,?), (?, ?))

Answer 1

以下内容适用于MySQL：

1. 为@Entity创建一个javax.persistence.NamedQuery

   @NamedQuery(name = "select", query = "SELECT foo FROM Foo foo WHERE foo.pk IN :pks")
   

2. 选择通过

   final TypedQuery<Foo> typedQuery = this.entityManager.createNamedQuery("select", Foo.class);
   final FooPK fooPk = new FooPK(...); // Create example FooPK
   typedQuery.setParameter("pks", Arrays.asList(fooPk, fooPk));
   
   return typedQuery.getResultList();
   

生成的SQL：

select ... from ... where foo0_.idA=? and foo0_.idB=? or foo0_.idA=? and foo0_.idB=?

（注意每个idA和idB的两个检查）

Answer 2

您可以使用CriteriaAPI替换hql

// your list of FooPks
List<FooPk> pkList = ....

//Create a criteria over an entity
Criteria  criteria = session.createCriteria(Foo.class);
// add a restriction
criteria.add(Restrictions.in("pk", pkList));
// Execute query and get result.
List<Foo> foos = criteria.list();