java 如何使用 JPA Criteria API 在左连接上指定多个条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3280381/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to specify multiple conditions on left join using JPA Criteria API?
提问by Jonathan
I'd like to convert the following SQL query:
我想转换以下 SQL 查询：
select * from region_tree country left outer join region_tree region
on country.REG_CODE_PAR=region.REG_CODE
and region.LFT < country.LFT
and region.RGT > country.RGT
and region.REG_CODE_PAR = 'ALL'
and COUNTRY.STATUS_CODE = 'A'
and REGION.STATUS_CODE = 'A
into JPA Crtieria based query.
进入基于 JPA 标准的查询。
I created an entity to represent the self join:
我创建了一个实体来表示自联接：
@Entity
@Table(name = "REGION_TREE")
public class RegionTree implements Serializable {
... some other attributes
@ManyToOne
@JoinColumn(name = "REG_CODE_PAR")
private RegionTree region;
... getters and setters
}
I used the following code to create the JPA query
我使用以下代码创建 JPA 查询
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<RegionTree> cq = cb.createQuery(RegionTree.class);
Root<RegionTree> e = cq.from(RegionTree.class);
Join<RegionTree, RegionTree> r = e.join("region", JoinType.LEFT);
Predicate p1 = cb.greaterThan(e.get("lft").as(Integer.class), r.get("lft").as(Integer.class));
Predicate p2 = cb.lessThan(e.get("rgt").as(Integer.class), r.get("rgt").as(Integer.class));
Predicate p3 = cb.equal(e.get("statusCode"), "A");
Predicate p4 = cb.equal(r.get("statusCode"), "A");
Predicate p5 = cb.equal(r.get("regCodePar"), "ALL");
cq.where(p1,p2,p3,p4,p5);
TypedQuery<RegionTree> tq = em.createQuery(cq);
l = tq.getResultList();`
This is the query automatically generated by Hibernate when I run this piece of code.
这是我运行这段代码时Hibernate自动生成的查询。
select
regiontree0_.REG_CODE as REG1_7_,
regiontree0_.LFT as LFT7_,
regiontree0_.NAME as NAME7_,
regiontree0_.REG_CODE_PAR as REG4_7_,
regiontree0_.RGT as RGT7_,
regiontree0_.STATUS_CODE as STATUS6_7_
from
REGION_TREE regiontree0_
left outer join
REGION_TREE regiontree1_
on regiontree0_.REG_CODE_PAR=regiontree1_.REG_CODE
where
cast(regiontree0_.LFT as integer)>cast(regiontree1_.LFT as integer)
and cast(regiontree0_.RGT as integer)<cast(regiontree1_.RGT as integer)
and regiontree0_.STATUS_CODE=?
and regiontree1_.STATUS_CODE=?
and regiontree1_.REG_CODE_PAR=?
I've tried a number of ways including removing the cq.whereline of code but the generated query can't match my original one. Have I configured anything wrong?
我尝试了多种方法，包括删除cq.where代码行，但生成的查询无法匹配我原来的查询。我配置错了吗？
回答by Nick
Try invoking cq.select(r);after creating a join. Without cq.select()the result of the last cq.from()call is used as a selection root.
cq.select(r);创建连接后尝试调用。没有cq.select()最后一次cq.from()调用的结果被用作选择根。
