Consulta de critérios JPA em um relacionamento muitos-para-muitos
Estou usando o JPA 2.0 no EclipseLink 2.3.2, no qual tenho uma relação muitos-para-muitos entre produtos e suas cores. Um produto pode ter muitas cores e uma cor pode ser associada a muitos produtos. Esse relacionamento é expresso no banco de dados por três tabelas.
produtosprod_colour (unir tabela)coroprod_colour
tabela tem duas colunas de referênciaprod_id
ecolour_id
de suas tabelas pai relacionadasproduct
ecolour
respectivamente.
Como é óbvio, a classe de entidadeProduct
tem um conjunto de cores -java.util.Set<Colour>
que é nomeadocolourSet
.
A classe de entidadeColour
tem um conjunto de produtos -java.util.Set<Product>
que é nomeadoproductSet
.
Eu preciso buscar uma lista de cores docolour
mesabaseado emprodId
fornecido o que faznão combinar as cores noprod_colour
mesa.
O JPQL correspondente seria algo como o seguinte.
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
Ele gera a seguinte instrução 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
Como essa seria, por sua vez, uma consulta de tempo de execução, seria preferível ter uma consulta de critérios. Eu não tenho uma visão para fabricar uma consulta de critérios neste relacionamento complexo.
Eu tenho a seguinte consulta até agora que é bastante não relacionada ao JPQL anterior.
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();
Como escrever uma consulta de critérios que corresponde ao JPQL fornecido?
EDITAR:
Esta consulta de critérios:
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();
por sua vez, produz a seguinte consulta 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))))
Como correlacionar essa consulta a uma subconsulta para que ela possa produzir a seguinte consulta 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
EDITAR:
A consulta de critérios a seguir, juntamente comNOT EXISTS()
trabalho.
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();
No entanto, ele produz a consulta SQL com uma associação desnecessária / extra / redundante como a seguinte (ela retorna o conjunto de resultados desejado embora pareça ser).
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
Isso deveria ser simplesmente como
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
Existe uma maneira de ter uma subconsulta com oNOT IN()
cláusula em vez deNOT EXISTS()
e se livrar dessa junção redundante?
A junção redundante produzida por essa consulta já foi relatada comoerro.