JPA条件查询Path.get左联接是否可能 [英] JPA Criteria query Path.get left join is it possible
本文介绍了JPA条件查询Path.get左联接是否可能的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我对JPA标准有疑问。
I have a question regarding JPA criteria.
这是我的JPA标准查询:
Here is my JPA criteria query:
CriteriaBuilder criteriaBuilder = getEm().getCriteriaBuilder();
CriteriaQuery<InventoryItemSumReport> query = criteriaBuilder.createQuery(InventoryItemSumReport.class);
Root<InventoryItemDetail> from = query.from(InventoryItemDetail.class);
Join<InventoryItemDetail, InventoryItem> joinItem = from.join(InventoryItemDetail_.inventoryItem);
Predicate where = criteriaBuilder.lessThanOrEqualTo(from.get(InventoryItemDetail_.effectiveDate), date);
query.multiselect(joinItem.get(InventoryItem_.product),joinItem.get(InventoryItem_.facility),joinItem.get(InventoryItem_.customer));
query.groupBy(joinItem.get(InventoryItem_.product),joinItem.get(InventoryItem_.facility),joinItem.get(InventoryItem_.customer));
query.where(where);
TypedQuery<InventoryItemSumReport> createQuery = getEm().createQuery(query);
List<InventoryItemSumReport> resultList = createQuery.getResultList();
这是JPA提供程序生成的结果查询:
Here is the resulting query produced by the JPA provider:
select
inventoryi1_.PRODUCT_ID as col_0_0_,
inventoryi1_.FACILITY_ID as col_1_0_,
inventoryi1_.CUSTOMER_ID as col_2_0_
from
INVENTORY_ITEM_DETAIL inventoryi0_
inner join
INVENTORY_ITEM inventoryi1_
on inventoryi0_.INVENTORY_ITEM_ID=inventoryi1_.ID
inner join
PRODUCT product2_
on inventoryi1_.PRODUCT_ID=product2_.ID
inner join
FACILITY facility3_
on inventoryi1_.FACILITY_ID=facility3_.ID
inner join
CUSTOMER customer4_
on inventoryi1_.CUSTOMER_ID=customer4_.ID
where
inventoryi0_.EFFECTIVE_DATE<= ?
group by
inventoryi1_.PRODUCT_ID ,
inventoryi1_.FACILITY_ID ,
inventoryi1_.CUSTOMER_ID
但我想执行以下查询:
select
inventoryi1_.PRODUCT_ID as col_0_0_,
inventoryi1_.FACILITY_ID as col_1_0_,
inventoryi1_.CUSTOMER_ID as col_2_0_
from
INVENTORY_ITEM_DETAIL inventoryi0_
inner join
INVENTORY_ITEM inventoryi1_
on inventoryi0_.INVENTORY_ITEM_ID=inventoryi1_.ID
inner join
PRODUCT product2_
on inventoryi1_.PRODUCT_ID=product2_.ID
inner join
FACILITY facility3_
on inventoryi1_.FACILITY_ID=facility3_.ID
left join
CUSTOMER customer4_
on inventoryi1_.CUSTOMER_ID=customer4_.ID
where
inventoryi0_.EFFECTIVE_DATE<= ?
group by
inventoryi1_.PRODUCT_ID ,
inventoryi1_.FACILITY_ID ,
inventoryi1_.CUSTOMER_ID
,带有左联接客户,也可以得到 Customers
为空的结果。
客户
,产品
,设施
都是实体,而 InventoryItemSumReport
是一个Value对象或DTO。
with a left join CUSTOMER to get also results where Customers
are null.
Customer
, Product
, Facility
are all entites, while InventoryItemSumReport
is a Value object or DTO.
public class InventoryItemSumReport implements Serializable {
private static final long serialVersionUID = 1L;
private Product product;
private Facility facility;
private Customer customer;
public InventoryItemSumReport(Product product, Facility facility, Customer customer) {
super();
this.product = product;
this.facility = facility;
this.customer = customer;
}
}
推荐答案
I发现如下所示:
CriteriaBuilder criteriaBuilder = getEm().getCriteriaBuilder();
CriteriaQuery<InventoryItemSumReport> query = criteriaBuilder.createQuery(InventoryItemSumReport.class);
Root<InventoryItemDetail> from = query.from(InventoryItemDetail.class);
Join<InventoryItemDetail, InventoryItem> joinItem = from.join(InventoryItemDetail_.inventoryItem);
Predicate where = criteriaBuilder.lessThanOrEqualTo(from.get(InventoryItemDetail_.effectiveDate), date);
Join<InventoryItem, Customer> joinCustomer = joinItem.join(InventoryItem_.customer, JoinType.LEFT);
query.multiselect(joinItem.get(InventoryItem_.product),joinItem.get(InventoryItem_.facility),joinItem.get(InventoryItem_.customer));
query.groupBy(joinItem.get(InventoryItem_.product),joinItem.get(InventoryItem_.facility),joinCustomer);
query.where(where);
TypedQuery<InventoryItemSumReport> createQuery = getEm().createQuery(query);
List<InventoryItemSumReport> resultList = createQuery.getResultList();
这篇关于JPA条件查询Path.get左联接是否可能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文