如何使用JPA Criteria API在左联接上指定多个条件? [英] How to specify multiple conditions on left join using JPA Criteria API?
问题描述
我想转换以下SQL查询:
I'd like to convert the following SQL query:
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
进入基于JPA Crtieria的查询.
into JPA Crtieria based query.
我创建了一个实体来表示自我联接:
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
}
我使用以下代码创建了JPA查询
I used the following code to create the JPA query
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();`
这是我运行这段代码时由Hibernate自动生成的查询.
This is the query automatically generated by Hibernate when I run this piece of code.
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=?
我尝试了多种方法,包括删除代码的cq.where
行,但是生成的查询与我的原始查询不匹配.我配置错了吗?
I've tried a number of ways including removing the cq.where
line of code but the generated query can't match my original one. Have I configured anything wrong?
推荐答案
在创建联接后尝试调用cq.select(r);
.如果没有cq.select()
,则最后一个cq.from()
调用的结果将用作选择根.
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.
这篇关于如何使用JPA Criteria API在左联接上指定多个条件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!