多对多关系中的JPA标准查询 [英] JPA criteria query in a many-to-many relationship
问题描述
我正在EclipseLink 2.3.2中使用JPA 2.0，在该产品中，产品及其颜色之间存在多对多的关系.产品可以具有多种颜色，并且颜色可以与多种产品相关联.此关系在数据库中由三个表表示.
I'm using JPA 2.0 in EclipseLink 2.3.2 in which I have a many-to-many relationship between products and their colours. A product can have many colours and a colour can be associated with many products. This relationship is expressed in the database by three tables.
- 产品
- prod_colour(联接表)
- 颜色
prod_colour表在其相关父表product和colour中分别具有两个引用列prod_id和colour_id.
The prod_colour table has two reference columns prod_id and colour_id from its related parent tables product and colour respectively.
很明显，实体类Product具有一组颜色-java.util.Set<Colour>，其命名为colourSet.
As obvious, the entity class Product has a set of colours - java.util.Set<Colour> which is named colourSet.
实体类Colour具有一组产品-java.util.Set<Product>，其名称为productSet.
The entity class Colour has a set of products - java.util.Set<Product> which is named productSet.
我需要根据提供的prodId 从colour表中获取颜色列表，而与表中的颜色不匹配.
I need to fetch a list of colours from the colour table based on prodId supplied which does not match the colours in the prod_colour table.
对应的JPQL如下所示.
The corresponding JPQL would be something like the following.
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语句.
It generates the following SQL statement.
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
由于这将依次是运行时查询，因此最好使用条件查询.在这种复杂的关系中，我没有洞察力来构造条件查询.
Since this would in turn be a run time query, it would be preferable to have a criteria query. I don't have insight to fabricate a criteria query in this complex relationship.
到目前为止，我有以下查询，它与前面的JPQL完全无关.
I have the following query so far which is quite unrelated to the preceding 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相对应的条件查询?
How to write a criteria query that corresponds to the JPQL given?
此条件查询:
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查询.
in turn produces the following SQL query.
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查询?
How to correlate this query to a subquery so that it can produce the following SQL query?
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()一起使用.
The following criteria query along with NOT EXISTS() works.
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查询，如下所示(尽管看起来好像返回了想要的结果集).
It however, produces the SQL query with an unnecessary/extra/redundant join like the following (It returns the desired result set though as it seems to be).
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()的子查询并摆脱这种多余的联接?
Is there a way to have a subquery with the NOT IN() clause instead of NOT EXISTS() and to get rid of this redundant join?
此查询产生的冗余联接已报告为错误.
The redundant join produced by this query was already reported as a bug.
推荐答案
以下是关于NOT IN()的条件查询(但是，我更喜欢NOT EXISTS()而不是NOT IN()).
The following is the criteria query regarding NOT IN() (I however, prefer NOT EXISTS() over NOT IN()).
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.in(root.get(Colour_.colourId)).value(subquery).not());
criteriaQuery.orderBy(criteriaBuilder.desc(root.get(Colour_.colourId)));
TypedQuery<Colour> typedQuery = entityManager.createQuery(criteriaQuery);
List<Colour> list=typedQuery.getResultList();
这将产生以下SQL查询.
This produces the following SQL query.
SELECT t0.colour_id, t0.colour_hex, t0.colour_name
FROM projectdb.colour t0
WHERE NOT
(t0.colour_id IN (
SELECT t0.colour_id
FROM prod_colour t3, projectdb.product t2, projectdb.product t1
WHERE (((t1.prod_id = ?)
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
此查询返回所需的结果集.但是，它会产生冗余连接，但这似乎是错误.
This query returns the desired result set. It however, produces a redundant join as can be seen but this seems to be a bug.
在Hibernate上尝试相同的查询，编写此条件查询的方式看起来不正确.连接和子查询的组合会产生正确的SQL查询.
Trying the same query on Hibernate, the way of writing this criteria query looks incorrect. A combination of join and subquery result in producing the correct SQL query.
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<Colour> subRoot = subquery.from(Colour.class);
subquery.select(subRoot.get(Colour_.colourId));
SetJoin<Colour, Product> join = subRoot.join(Colour_.productSet, JoinType.INNER);
ParameterExpression<Long> parameterExpression=criteriaBuilder.parameter(Long.class);
criteriaQuery.where(criteriaBuilder.in(root.get(Colour_.colourId)).value(subquery).not());
subquery.where(criteriaBuilder.equal(join.get(Product_.prodId), parameterExpression));
criteriaQuery.orderBy(criteriaBuilder.desc(root.get(Colour_.colourId)));
TypedQuery<Colour> typedQuery = entityManager.createQuery(criteriaQuery);
List<Colour> list = typedQuery.setParameter(parameterExpression, 1L).getResultList();
这将产生以下SQL查询，然后将其委托给MySQL.
This produces the following SQL query which would in turn be delegated to MySQL.
SELECT t0.colour_id, t0.colour_name, t0.colour_hex
FROM projectdb.colour t0
WHERE NOT (t0.colour_id IN
(SELECT t1.colour_id
FROM prod_colour t3, projectdb.product t2, projectdb.colour t1
WHERE ((t2.prod_id = ?)
AND ((t3.colour_id = t1.colour_id)
AND (t2.prod_id = t3.prod_id)))))
ORDER BY t0.colour_id DESC
这篇关于多对多关系中的JPA标准查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
