问题描述
我正在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标准查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!