问题描述
我想使用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"连接在一起?状况?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！