java JPQL 多对多选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12425835/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPQL ManyToMany select
提问by gtgaxiola
I have a Many To Many relationship between two entities called: Car and Dealership.
我有两个实体之间的多对多关系，称为：汽车和经销商。
In native MySQL I have:
在本机 MySQL 中，我有：
car (id and other values)
dealership (id and other values)
car_dealership (car_id and dealership_id)
And the query I want to make in JPQL is:
我想在 JPQL 中进行的查询是：
#Select List of cars in multiple dealerships
SELECT car_id FROM car_dealership WHERE dealership_id IN(1,2,3,56,78,999);
What is the proper way to make the JPQL equivalent?
使 JPQL 等效的正确方法是什么？
My Java method signature is:
我的 Java 方法签名是：
public List<Car> findByDealership(List<Dealership> dealerships);
I have tried
我努力了
//TOTALLY WRONG QUERY CALL!!!
Query query = em.createQuery("SELECT c FROM Car c WHERE :dealer_ids IN c.dealerships");
List<Long> dealerIds = new ArrayList<Long>();
for(Dealership d : dealerships) {
dealerIds.add(d.getId());
}
query.setParameter(":dealer_ids", dealerIds);
List<Dealership> result = (List<Dealership>) query.getResultList();
return result;
}
Here is my JPA Annotations for such relationship in java:
这是我在 Java 中针对这种关系的 JPA 注释：
@Entity
@Table(name = "car")
public class Car implements Serializable {
//Setup of values and whatnot....
@ManyToMany
@JoinTable(name = "car_dealership", joinColumns =
@JoinColumn(name = "car_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name = "dealership_id", referencedColumnName = "id"))
private List<Dealership> dealerships;
... other stuff (getters/setters)
}
@Entity
@Table(name = "property")
public class Dealership implements Serializable {
//Setting of values and whatnot
@ManyToMany(mappedBy = "dealerships")
private List<Car> cars;
.... other stuff(getters/setters)
}
EDIT
编辑
I also have tried:
我也试过：
Query query = em.createQuery("SELECT c FROM Car c INNER JOIN c.dealerships d WHERE d IN (:deals)");
query.setParameter("deals", dealerships);
Which threw the Error:
哪个抛出错误：
org.eclipse.persistence.exceptions.QueryException
Exception Description: Object comparisons can only use the equal() or notEqual() operators.
Other comparisons must be done through query keys or direct attribute level comparisons.
Expression: [
Relation operator [ IN ]
Query Key dealerships
Base stackoverflow.question.Car
Constant [
Parameter deals]]
回答by JB Nizet
select car from Car car
inner join car.dealerships dealership
where dealership in :dealerships
The parameter must be a collection of Dealership instances.
该参数必须是 Dealership 实例的集合。
If you want to use a collection of dealership IDs, use
如果要使用经销商 ID 集合，请使用
select car from Car car
inner join car.dealerships dealership
where dealership.id in :dealershipIds
Remamber that JPQL always use entities, mapped attributes and associations. Never table and column names.
记住 JPQL 总是使用实体、映射属性和关联。从不表名和列名。
