JPQL ManyToMany选择 [英] JPQL ManyToMany select

问题描述

上午（EST时间）给您！尊敬的StackOverflow成员：

Top of the Morning (EST Time) to you! Dear StackOverflow members:

我有两个实体之间的多对多关系：汽车和经销商。

I have a Many To Many relationship between two entities called: Car and Dealership.

在本机mysql我有：

In native mysql I have:

car  (id and other values)
dealership  (id and other values)
car_dealership   (car_id and dealership_id)

我想在JPQL中的查询是： p>

And the Query I want to make in JPQL is:

#Select List of cars in multiple dealerships
SELECT car_id FROM car_dealership WHERE dealership_id IN(1,2,3,56,78,999);

使JPQL等效的正确方法是什么？

What is the proper way to make the JPQL equivalent?

我的Java方法签名是：

My Java method signature is:

public List<Car> findByDealership(List<Dealership> dealerships);

我尝试过

    //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;
}

这是我在Java中的这种关系的JPA注释：

Here is my JPA Annotations for such relationship in java:

@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)



}

感谢！

编辑

我也尝试过：

 Query query = em.createQuery("SELECT c FROM Car c INNER JOIN c.dealerships d WHERE d IN (:deals)");
 query.setParameter("deals", dealerships);

发生错误：

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]]

推荐答案

select car from Car car 
inner join car.dealerships dealership
where dealership in :dealerships

参数必须是Dealership实例的集合。

The parameter must be a collection of Dealership instances.

如果要使用经销商ID的集合，请使用

If you want to use a collection of dealership IDs, use

select car from Car car 
inner join car.dealerships dealership
where dealership.id in :dealershipIds

还记得JPQL总是使用实体，映射属性和关联。不要使用表和列名称。

Remamber that JPQL always use entities, mapped attributes and associations. Never table and column names.

这篇关于JPQL ManyToMany选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！