JPA标准使用IN运算符以多对多关系进行查询 [英] JPA criteria query in a many-to-many relationship using IN operator
问题描述
我有以下两个实体:
个人资料,类别
在配置文件实体中,我有一个配置文件包含的类别列表
在类别实体中,我有一个配置文件列表,该类别包含
生成的表称为 profiles_has_categories ,它包含以下列:
$ b profile_id,category_id strong>
我想查找属于任何类别的所有配置文件,其中包含以下id 1,2,3,4 4
我会用普通的sql做到这一点:
SELECT p。* FROM profiles p,profiles_has_categories phc
WHERE p.id = phc.profile_id
AND phc.category_id IN(1,2,3,4)
但是我无法弄清楚如何使用CriteriaBuilder在JPA中构建查询:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<个人资料> criteria = cb.createQuery(Profile.class);
Root< Profile> root = criteria.from(Profile.class);
// Profile_.categories是类别对象的集合
//类别是我想要在
中搜索的Long(类别ids)的集合//但它不起作用
criteria.where(root.get(Profile_Categories).in(categories))
List< Profile> results = em.createQuery(criteria).getResultList();
你不想要 / em>,你想要一个加入:
criteria.where(root.join(Profile_。类别).in(类别))
I have the following two entities:
Profile, Category
In the Profile entity i have a List of categories that the profile has
In the Category entity i have a List of profiles that the category has
The generated table is called profiles_has_categories and has the following columns:
profile_id, category_id
I want to find all profiles that belong to any of the categories with the following ids 1, 2, 3, 4
I would do that in plain sql with:
SELECT p.* FROM profiles p, profiles_has_categories phc
WHERE p.id = phc.profile_id
AND phc.category_id IN (1, 2, 3, 4)
But i can't figure out how to do that using the CriteriaBuilder to construct a query in JPA:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Profile> criteria = cb.createQuery(Profile.class);
Root<Profile> root = criteria.from(Profile.class);
// Profile_.categories is a collection of Category objects
// categories is collection of Long (category ids) that i want to search in
// but is not working
criteria.where(root.get(Profile_.categories).in(categories))
List<Profile> results = em.createQuery(criteria).getResultList();
You don't want a get, you want a join:
criteria.where(root.join(Profile_.categories).in(categories))
这篇关于JPA标准使用IN运算符以多对多关系进行查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!