Я использую JPA 2.0 в EclipseLink 2.3.2, в которой у меня есть отношение многие ко многим между продуктами и их цветами. Продукт может иметь много цветов, а цвет может ассоциироваться со многими продуктами. Эта связь выражается в базе данных тремя таблицами.

продуктprod_colour (присоединиться к таблице)цвет

prod_colour таблица имеет два справочных столбцаprod_id а такжеcolour_id из связанных с ним родительских таблицproduct а такжеcolour соответственно.

Как видно, класс сущностиProduct имеет набор цветов -java.util.Set<Colour> который названcolourSet.

Класс сущностиColour имеет набор продуктов -java.util.Set<Product> который названproductSet.

Мне нужно получить список цветов изcolour Таблицаоснованный наprodId поставляется который делаетне сопоставьте цвета вprod_colour Таблица.

Соответствующий JPQL будет выглядеть примерно так:

FROM Colour colour 
WHERE colour.colourId 
NOT IN(
SELECT colours.colourId 
     FROM Product product 
     INNER JOIN product.colourSet colours 
     WHERE product.prodId=:id) 
ORDER BY colour.colourId DESC

Он генерирует следующий оператор SQL.

SELECT t0.colour_id, t0.colour_hex, t0.colour_name 
FROM projectdb.colour t0 
WHERE t0.colour_id 
NOT IN (
       SELECT DISTINCT t1.colour_id 
       FROM prod_colour t3, projectdb.product t2, projectdb.colour t1 
       WHERE ((t2.prod_id = ?)
       AND ((t3.prod_id = t2.prod_id) 
       AND (t1.colour_id = t3.colour_id)))) 
ORDER BY t0.colour_id DESC

Поскольку это, в свою очередь, запрос времени выполнения, было бы предпочтительным иметь запрос критерия. У меня нет понимания, чтобы сфабриковать запрос критериев в этих сложных отношениях.

У меня есть следующий запрос, который совершенно не связан с предыдущим JPQL.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Colour>criteriaQuery=criteriaBuilder.createQuery(Colour.class);
Metamodel metamodel = entityManager.getMetamodel();
EntityType<Colour> entityType = metamodel.entity(Colour.class);
Root<Colour> root = criteriaQuery.from(entityType);
SetJoin<Colour, Product> join = root.join(Colour_.productSet, JoinType.INNER);
ParameterExpression<Long> parameterExpression=criteriaBuilder.parameter(Long.class);
criteriaQuery.where(criteriaBuilder.equal(join.get(Product_.prodId), parameterExpression));  

TypedQuery<Colour> typedQuery = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, prodId);
List<Colour> list=typedQuery.getResultList();

Как написать запрос критерия, соответствующий заданному JPQL?

РЕДАКТИРОВАТЬ:

Этот критерий запроса:

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple>criteriaQuery=criteriaBuilder.createQuery(Tuple.class);
Metamodel metamodel = entityManager.getMetamodel();
EntityType<Colour> entityType = metamodel.entity(Colour.class);
Root<Colour> root = criteriaQuery.from(entityType);
criteriaQuery.multiselect(root.get(Colour_.colourId));
SetJoin<Colour, Product> join = root.join(Colour_.productSet, JoinType.INNER);
ParameterExpression<Long> parameterExpression=criteriaBuilder.parameter(Long.class);
criteriaQuery.where(criteriaBuilder.equal(join.get(Product_.prodId), parameterExpression));

TypedQuery<Tuple> typedQuery = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, prodId);
List<Tuple> list = typedQuery.getResultList();

в свою очередь производит следующий запрос SQL.

SELECT t0.colour_id 
FROM projectdb.colour t0, prod_colour t2, projectdb.product t1 
WHERE ((t1.prod_id = 1) 
AND ((t2.colour_id = t0.colour_id) 
AND (t1.prod_id = t2.prod_id))))

Как соотнести этот запрос с подзапросом, чтобы он мог произвести следующий запрос SQL?

SELECT t0.colour_id, t0.colour_hex, t0.colour_name 
FROM projectdb.colour t0 
WHERE t0.colour_id 
NOT IN (
       SELECT t0.colour_id 
       FROM projectdb.colour t0, prod_colour t2, projectdb.product t1 
       WHERE ((t1.prod_id = 1) 
       AND ((t2.colour_id = t0.colour_id) 
       AND (t1.prod_id = t2.prod_id))))
ORDER BY t0.colour_id DESC   

РЕДАКТИРОВАТЬ:

Следующий критерий запроса вместе сNOT EXISTS() работает.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Colour>criteriaQuery=criteriaBuilder.createQuery(Colour.class);
Metamodel metamodel = entityManager.getMetamodel();
EntityType<Colour> entityType = metamodel.entity(Colour.class);
Root<Colour> root = criteriaQuery.from(entityType);
criteriaQuery.select(root);
Subquery<Long>subquery=criteriaQuery.subquery(Long.class);
Root<Product> subRoot = subquery.from(Product.class);
subquery.select(root.get(Colour_.colourId));
Predicate paramPredicate = criteriaBuilder.equal(subRoot.get(Product_.prodId), prodId);
Predicate correlatePredicate = criteriaBuilder.equal(root.get(Colour_.productSet), subRoot);
subquery.where(criteriaBuilder.and(paramPredicate, correlatePredicate));
criteriaQuery.where(criteriaBuilder.exists(subquery).not());
criteriaQuery.orderBy(criteriaBuilder.desc(root.get(Colour_.colourId)));

TypedQuery<Colour> typedQuery = entityManager.createQuery(criteriaQuery);
List<Colour>list= typedQuery.getResultList();

Тем не менее, он создает запрос SQL с ненужным / дополнительным / избыточным объединением, как показано ниже (он возвращает желаемый набор результатов, хотя, как кажется).

SELECT t0.colour_id, t0.colour_hex, t0.colour_name 
FROM projectdb.colour t0 
WHERE 
NOT (EXISTS (
       SELECT t0.colour_id 
       FROM prod_colour t3, projectdb.product t2, projectdb.product t1 
       WHERE (((t1.prod_id = 1) 
       AND (t1.prod_id = t2.prod_id)) 
       AND ((t3.colour_id = t0.colour_id) 
       AND (t2.prod_id = t3.prod_id)))))
ORDER BY t0.colour_id DESC

Это должно быть просто как,

SELECT t0.colour_id, t0.colour_hex, t0.colour_name 
FROM projectdb.colour t0 
WHERE 
NOT (EXISTS (
       SELECT t0.colour_id 
       FROM prod_colour t3, projectdb.product t2
       WHERE (((t2.prod_id = 1)) 
       AND ((t3.colour_id = t0.colour_id) 
       AND (t2.prod_id = t3.prod_id)))))
ORDER BY t0.colour_id DESC

Есть ли способ получить подзапрос сNOT IN() пункт вместоNOT EXISTS() и избавиться от этого лишнего соединения?

Резервное соединение, созданное этим запросом, уже было сообщено какошибка.