JPA中的外部条件加入ON条件 [英] Outer joins with ON conditions in JPA

问题描述

$ b

  SELECT w.weight_id， w.weight，zc.charge 
从体重w 
左外部加入zone_charge zc ON w.weight_id = zc.weight_id 
 AND zc.zone_id =？ < ------- 
 ORDER BY w.weight ASC 
  

相应的JPQL查询就像是，

  SELECT w.weightId，w.weight，zc.charge 
 FROM权重w 
 LEFT JOIN w.zoneChargeSet zc 
 WITH zc.zone.zoneId =：id< ------- 
 ORDER BY w.weight 
  

我无法用标准重现相同的结果，特别是 WITH zc.zone.zoneId =： id 。

---

以下标准查询使用 code>子句。



  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder（）; 
 CriteriaQuery< Tuple> criteriaQuery = criteriaBuilder.createTupleQuery（）; 
 Root<重量> root = criteriaQuery.from（entityManager.getMetamodel（）。entity（Weight.class））; 
 SetJoin< Weight，ZoneCharge> join = root.join（Weight_.zoneChargeSet，JoinType.LEFT）; 
 
 ParameterExpression< Long> parameterExpression = criteriaBuilder.parameter（Long.class）; 
 criteriaQuery.where（criteriaBuilder.equal（join.get（ZoneCharge_.zoneTable）.get（ZoneTable_.zoneId），parameterExpression））; 
 criteriaQuery.multiselect（root.get（Weight_.weightId），root.get（Weight_.weight），join.get（ZoneCharge_.charge））; 
 criteriaQuery.orderBy（criteriaBuilder.asc（root.get（Weight_.weight）））; 
 TypedQuery< Tuple> typedQuery = entityManager.createQuery（criteriaQuery）.setParameter（parameterExpression，1L）; 
 List< Tuple> list = typedQuery.getResultList（）; 
  

如何修改它以使其对应于 ... LEFT OUTER JOIN zone_charge zc ON w.weight_id = zc.weight_id
AND zc.zone_id =？

这将生成以下SQL查询。
$ b

  SELECT weight0_.weight_id AS col_0_0_，
 weight0_.weight AS col_1_0_，
 zonecharge1_.charge AS col_2_0_ 
 FROM social_networking.weight weight0_ 
 LEFT OUTER JOIN social_networking.zone_charge zonecharge1_ 
 ON weight0_.weight_id = zonecharge1_.weight_id 
 WHERE zonecharge1_.zone_id =？ 
 ORDER BY weight0_.weight ASC 
  

解决方案

与运算符是JPQL的hibernate特定扩展。您在标准API中找不到它的支持。

I need a criteria query for the following SQL query.

SELECT w.weight_id, w.weight, zc.charge
FROM weight w
LEFT OUTER JOIN zone_charge zc ON w.weight_id=zc.weight_id
AND zc.zone_id=?             <-------
ORDER BY w.weight ASC

The corresponding JPQL query would be like,

SELECT w.weightId, w.weight, zc.charge 
FROM Weight w 
LEFT JOIN w.zoneChargeSet zc 
WITH zc.zone.zoneId=:id      <-------
ORDER BY w.weight

I can't reproduce the same with criteria especially WITH zc.zone.zoneId=:id.

---

The following criteria query uses the where clause.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple>criteriaQuery=criteriaBuilder.createTupleQuery();
Root<Weight> root = criteriaQuery.from(entityManager.getMetamodel().entity(Weight.class));
SetJoin<Weight, ZoneCharge> join = root.join(Weight_.zoneChargeSet, JoinType.LEFT);

ParameterExpression<Long>parameterExpression=criteriaBuilder.parameter(Long.class);
criteriaQuery.where(criteriaBuilder.equal(join.get(ZoneCharge_.zoneTable).get(ZoneTable_.zoneId), parameterExpression));
criteriaQuery.multiselect(root.get(Weight_.weightId), root.get(Weight_.weight), join.get(ZoneCharge_.charge));
criteriaQuery.orderBy(criteriaBuilder.asc(root.get(Weight_.weight)));
TypedQuery<Tuple> typedQuery = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, 1L);
List<Tuple> list = typedQuery.getResultList();

How could it be modified so that it corresponds to ...LEFT OUTER JOIN zone_charge zc ON w.weight_id=zc.weight_id AND zc.zone_id=?

This generates the following SQL query.

SELECT weight0_.weight_id  AS col_0_0_, 
       weight0_.weight     AS col_1_0_, 
       zonecharge1_.charge AS col_2_0_ 
FROM   social_networking.weight weight0_ 
       LEFT OUTER JOIN social_networking.zone_charge zonecharge1_ 
                    ON weight0_.weight_id = zonecharge1_.weight_id 
WHERE  zonecharge1_.zone_id =? 
ORDER  BY weight0_.weight ASC 

解决方案

The with operator is a hibernate-specific extension to JPQL. You won't find support for it in the criteria API.

这篇关于JPA中的外部条件加入ON条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！