如何使CriteriaBuilder与自定义"on"连接在一起?状况? [英] How to make a CriteriaBuilder join with a custom "on" condition?

问题描述

我想使用CriteriaBuilder在其中连接2个表的地方进行查询.在MySQL中，我要进行的查询如下所示:

I want make a query where I join 2 tables, using the CriteriaBuilder. In MySQL the query I'm trying to make would look like this:

SELECT * FROM order
LEFT JOIN item
ON order.id = item.order_id
AND item.type_id = 1

我想获得所有订单，如果他们有一个类型为#1的商品，我想加入该商品.但是，如果没有找到类型为#1的项目，我仍然想获得订单.我不知道如何使用CriteriaBuilder做到这一点.我所能做的就是:

I want to get all orders and if they have an item of type #1, I want to join with this item. However, if no item of type #1 is found, I still want to get the order. I can't figure out how to make this with the CriteriaBuilder. All I know how to make is:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Order> cq = cb.createQuery(Order.class);
Root<Order> order = cq.from(Order.class);
Join<Order, Item> item = order.join(Order_.itemList, JoinType.LEFT);
Join<Item, Type> type = order.join(Item_.type, JoinType.LEFT);
cq.select(order);
cq.where(cb.equal(type.get(Type_.id), 1));

此查询已中断，因为它在MySQL中导致如下所示:

This query is broke, since it results in something like this in MySQL:

SELECT * FROM order
LEFT JOIN item
ON order.id = item.order_id
WHERE item.type_id = 1

结果将仅包含类型为#1的订单.不含的订单不包括在内.像第一个示例一样，如何使用CriteriaBuilder创建查询?

The result will only contain orders with items of type #1. Orders without are excluded. How can I use the CriteriaBuilder to create a query like in the first example?

推荐答案

可以使用on方法Join<Z, X> on(Predicate... restrictions);

方法如下:

Root<Order> order = cq.from(Order.class);
Join<Order, Item> item = order.join(Order_.itemList, JoinType.LEFT);
item.on(cb.equal(item.get(Item_.type), 1));

这篇关于如何使CriteriaBuilder与自定义"on"连接在一起?状况?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！