如何使用JPA Criteria API在左联接上指定多个条件?

本文介绍了如何使用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.