问题描述
我需要帮助以在条件查询"中转换此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:
对于条件查询也是如此.为什么不只查询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
这篇关于条件查询选择联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!