Zapytanie o kryteria JPA w relacji wiele do wielu
Używam JPA 2.0 w EclipseLink 2.3.2, w którym mam relację wiele do wielu między produktami i ich kolorami. Produkt może mieć wiele kolorów, a kolor może być powiązany z wieloma produktami. Ta relacja jest wyrażona w bazie danych przez trzy tabele.
produktprod_colour (dołącz tabelę)kolorTheprod_colour
tabela ma dwie kolumny odniesieniaprod_id
icolour_id
z powiązanych tabel nadrzędnychproduct
icolour
odpowiednio.
Tak oczywiste, klasa encjiProduct
ma zestaw kolorów -java.util.Set<Colour>
który jest nazwanycolourSet
.
Klasa encjiColour
ma zestaw produktów -java.util.Set<Product>
który jest nazwanyproductSet
.
Muszę pobrać listę kolorów zcolour
stółoparte naprodId
dostarczone co robinie dopasuj kolory wprod_colour
stół.
Odpowiedni JPQL byłby podobny do następującego.
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
Generuje następującą instrukcję 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
Ponieważ byłoby to z kolei zapytanie o czas wykonania, lepiej byłoby mieć zapytanie o kryteria. Nie mam wglądu w sformułowanie zapytania o kryteria w tej złożonej relacji.
Mam do tej pory następujące zapytanie, które nie jest związane z poprzednim 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();
Jak napisać zapytanie o kryteria odpowiadające podanemu JPQL?
EDYTOWAĆ:
To zapytanie o kryteria:
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();
z kolei wytwarza następujące zapytanie 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))))
Jak skorelować to zapytanie z podzapytaniem, aby mogło wygenerować następujące zapytanie 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
EDYTOWAĆ:
Następujące zapytanie o kryteria wraz zNOT EXISTS()
Prace.
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();
Tworzy jednak zapytanie SQL z niepotrzebnym / dodatkowym / nadmiarowym złączeniem, jak poniżej (Zwraca pożądany zestaw wyników, jak się wydaje).
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
To powinno być po prostu,
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
Czy istnieje sposób na podzapytanie zNOT IN()
klauzula zamiastNOT EXISTS()
i pozbyć się tego zbędnego przyłączenia?
Redundantne połączenie utworzone przez to zapytanie zostało już zgłoszone jakopluskwa.