在JPA条件API中使用NOT EXISTS构建查询 [英] Building a query using NOT EXISTS in jpa criteria api
问题描述
我有两个名为table1,table2的表.两个表都没有相同的字段.这两个表之间没有关系.我的要求是我想要table1中所有不在table2中的记录.因此,我已经使用Criteria API编写了查询.但这并没有给出正确的结果.由于我是这个JPA和标准API的新手,任何人都可以指出我做错的地方.下面是我用来执行此操作的代码.
I have two tables named as table1 ,table2.Both the tables are having same no of fields.There is no relation between these two tables.My requirement is I want all the records in table1 which are not there in table2. So I have written a query using Criteria API. But it is not giving the correct result. As I am new to this JPA and criteria API, can any one point me where I am doing wrong.The below code I am using to do this.
CriteriaBuilder cb = mediationEntityManager.getCriteriaBuilder();
CriteriaQuery<Table1> cq = cb.createQuery(Table1.class);
Root<Table1> table1 = cq.from(Table1.class);
cq.select(table1)
Subquery<Table2> subquery = cq.subquery(Table2.class)
Root table2 = subquery.from(Table2.class)
subquery.select(table2)
cq.where(cb.not(cb.exists(subquery)))
TypedQuery<Table1> typedQuery = mediationEntityManager.createQuery(cq);
List<Table1> resultList = typedQuery.getResultList();
MySQL查询:
SELECT table1
FROM table1 table1
WHERE NOT EXISTS (SELECT table2
FROM table2 table2
WHERE table2.name = table1.name
AND table2.education = table1.education
AND table2.age = table1.age)
AND table1.name = 'san'
AND table1.age = '10';
对于上述MySQL查询,我需要JPA标准API查询.
I need the JPA criteria API query for the above mentioned MySQL query.
推荐答案
您可以使用Criteria API尝试以下代码.我没有尝试过,但是您可以尝试相应地修改代码.
You can try the below code with Criteria API. I haven't tried, but you can try modifying the code accordingly.
CriteriaBuilder cb = mediationEntityManager.getCriteriaBuilder();
CriteriaQuery<Table1> query = cb.createQuery(Table1.class);
Root<Table1> table1 = query.from(Table1.class);
query.select(table1);
//--
Subquery<Table2> subquery = query.subquery(Table2.class);
Root<Table2> table2 = subquery.from(Table2.class);
subquery.select(table2);
//--
List<Predicate> subQueryPredicates = new ArrayList<Predicate>();
subQueryPredicates.add(cb.equal(table1.get(Table1_.name), table2.get(Table2_.name)));
subQueryPredicates.add(cb.equal(table1.get(Table1_.age), table2.get(Table2_.age)));
subQueryPredicates.add(cb.equal(table1.get(Table1_.education), table2.get(Table2_.education)));
subquery.where(subQueryPredicates.toArray(new Predicate[]{}));
//--
List<Predicate> mainQueryPredicates = new ArrayList<Predicate>();
mainQueryPredicates.add(cb.equal(table1.get(Table1_.name), "san");
mainQueryPredicates.add(cb.equal(table1.get(Table1_.age), "10");
mainQueryPredicates.add(cb.not(cb.exists(subquery)));
//--
query.where(mainQueryPredicates.toArray(new Predicate[]{}));
TypedQuery<Table1> typedQuery = mediationEntityManager.createQuery(query);
List<Table1> resultList = typedQuery.getResultList();
此外,您还可以尝试下面的JPQL查询,该查询更易于理解,更改和修改.调试.
Also, you can try below JPQL query, which is easier to understand, alter & debug.
SELECT t1
FROM table1 t1,
table2 t2
WHERE t1.name = 'san'
AND t1.age = '10'
AND (t2.name <> t1.name
AND t2.education <> t1.education
AND t2.age <> t1.age);
这篇关于在JPA条件API中使用NOT EXISTS构建查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!