条件查询选择联接 [英] Criteria Query select join

问题描述

我需要帮助以在条件查询"中转换此JPQL查询:

I need help to convert this JPQL query in Criteria Query:

SELECT u.documentList FROM Unit u WHERE u.id = :id

这是我尝试过的:

CriteriaQuery<Document> query = builder.createQuery(Document.class);
Root<Unit> root = query.from(Unit.class);
Join<Unit, Document> join = root.join(Unit_.documentList);
query.select(join);
query.where(builder.equal(root.get(AbstractEntity_.id), entity.getId()));

执行此查询将导致一个复杂的SQL查询，该查询返回一个空列表.

executing this query result in a complex SQL query that returns an empty list.

IMO这应该工作，也许是错误?

IMO this should work, maybe it's a bug?

我正在使用EclipseLink 2.5.2作为JPA提供程序，并使用MySQL 5.6作为数据库.

i'm using EclipseLink 2.5.2 as JPA provider and MySQL 5.6 as db.

此处生成的SQL:

SELECT ... 
FROM UNIT t3 
    LEFT OUTER JOIN (DOCUMENT_RELATION t4 
    JOIN UNIT t0 ON (t0.ID = t4.CHILD_ID) 
    JOIN DELIVERABLE t1 ON (t1.ID = t0.ID) 
    JOIN DOCUMENT t2 ON (t2.ID = t0.ID)) ON (t4.PARENT_ID = t3.ID) 
WHERE (t3.ID = 58)

SELECT ... 
FROM DOCUMENT_RELATION t6, 
    DOCUMENT t5, 
    DELIVERABLE t4, 
    UNIT t3, 
    DOCUMENT t2, 
    DELIVERABLE t1, 
    UNIT t0 
WHERE (((t3.ID = 58) AND (((t5.ID = t3.ID) AND ((t4.ID = t3.ID) AND (t4.ID = t3.ID))) AND (t3.DTYPE = 'Document'))) AND ((((t5.ID = t3.ID) AND ((t4.ID = t3.ID) AND (t4.ID = t3.ID))) AND (t3.DTYPE = 'Document')) AND (((t6.PARENT_ID = t3.ID) AND (t0.ID = t6.CHILD_ID)) AND (((t2.ID = t0.ID) AND ((t1.ID = t0.ID) AND (t1.ID = t0.ID))) AND (t0.DTYPE = 'Document'))))) LIMIT 0, 10

以下是映射:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Unit extends NamedEntity
{
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "DOCUMENT_RELATION", joinColumns = @JoinColumn(name = "PARENT_ID"), inverseJoinColumns = @JoinColumn(name = "CHILD_ID"))
    protected List<Document> documentList = new ArrayList<>();
}

这是类层次结构:

AbstractEntity (abstract @MappedSuperClass)
    NamedEntity (abstract @MappedSuperClass)
        Unit (abstract @Entity joined-inheritance)
            Deliverable (abstract @Entity)
                Document (concrete @Entity)

---

找到解决方案:

---

found solution:

CriteriaQuery<Document> query = builder.createQuery(Document.class);

Root<Unit> root = query.from(Unit.class);
root.alias("root");

Root<Document> relation = query.from(Document.class);
relation.alias("relation");

query.select(relation);

query.where
(
    builder.equal(root.get(AbstractEntity_.id), item.getId()),
    builder.isMember(relation, root.get(Unit_.actionList))
);

推荐答案

JPA规范禁止您尝试实现的目标. 单值路径表达式在select子句中有效，但 集合值路径表达式无效.请参阅规范的第4.8章.我引用了 Pro JPA 书:

What you are trying to achieve is forbidden by JPA spec. single-valued path expressions are valid in select clause but collection-valued path expressions are not valid. See chapter 4.8 of the spec. I cite Pro JPA book:

以下查询是非法的: SELECT d.员工 来自部门d

The following query is illegal: SELECT d.employees FROM Department d

对于条件查询也是如此.为什么不只查询Unit并调用getDocumentList()?

The same is applies for Criteria queries. Why not just query for Unit and call getDocumentList()?

您也可以尝试撤消查询:

You can also try to reverse the query:

SELECT d FROM Document d WHERE d.unit.id=:id

这篇关于条件查询选择联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！