JPA – how to add a criteriabuilder with a custom “on” condition?

I want to use criteriabuilder to query and add 2 tables In mysql, the query I want is as follows:

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

I want to get all orders, if they have a type #1 of project, I want to join this project However, if no item of type #1 is found, I still hope to get the order I don't know how to use criteriabuilder to do this What I know how to do 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));

This query is broken because it produces such results in MySQL:

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

The result contains only orders of type #1 Orders are not included How do I use criteriabuilder to create a query like the one in the first example?

Solution

I think this is the same problem as this one It seems that this is impossible in criteriabuilder This is possible in the hibernate criteria API, but it may not help you

JPA Criteria API: Multiple condition on LEFT JOIN