英文:
JPA Criteria based query that INTERSECTS three queries at once
问题
我想查找所有具有 "b" = 500 和 "b" = 501 以及 "c" = 2 的 "a"。
在 SQL
中,我会这样做:
select a from table1 where id in (select info_id from table2 where b = '500')
intersect
select a from table1 where id in (select info_id from table2 where b = '501')
intersect
select a from table1 where id in (select info_id from table2 where id in (select ecu_id from table3 where c = '2'));
所以我会创建三个 SQL
查询,然后取这三者的交集。
但是现在我必须使用基于 JPA criteria
的查询,不能使用原生的 SQL
或 JPQL
。
我们可以执行三个不同的基于 JPA criteria
的查询:
一个返回 "b" = 500 的 "a" 列表,
一个返回 "b" = 501 的 "a" 列表,
一个返回 "c" = 2 的 "a" 列表。
然后过滤出出现在这三个返回列表中的所有 "a"。但是这会花费太长时间,因为我们的数据库包含了成千上万个 "a" 条目...
英文:
I want to find all "a" that have "b" = 500 and "b" = 501 and "c" = 2 installed.
In SQL
I would do it like this:
select a from table1 where id in (select info_id from table2 where b = '500')
intersect
select a from table1 where id in (select info_id from table2 where b = '501')
intersect
select a from table1 where id in (select info_id from table2 where id in (select ecu_id from table3 where c = '2'));
So I would create three SQL
queries and then take the intersection of those 3.
But I now have to use JPA criteria
based queries, no native SQL
or JPQL
.
We could do three distinct JPA criteria
based queries:
one that returns a list of "a" where "b" = 500,
one that returns a list of "a" where "b" = 501 and
one that returns a list of "a" where "c" = 2.
And then filter for all "a" that appear in all three returned lists. But that would take too long, our database contains many million "a" entries...
答案1
得分: 0
以下是翻译好的内容:
这是一个在SQL中的解决方案:
http://tpcg.io/mV3rZ2Of
这作为一个JPA标准查询很长,所以我只展示了我如何翻译最后一个Exists
条件,这部分是最难的:
Subquery<Table3> subQuery = searchQuery.subquery(Table3.class);
Root<Table3> fromTable3SubQuery = subQuery.from(Table3.class);
List<Predicate> subRestrictions = new ArrayList<>();
Subquery<Table2> subQueryForInExpression = searchQuery.subquery(Table2.class);
Root<Table2> fromTable2SubQuery = subQueryForInExpression.from(Table2.class);
subQueryForInExpression.select(fromTable2SubQuery.get(ID)).where(criteriaBuilder.equal(fromTable2SubQuery.get(info_id), root.get(ID)));
subRestrictions.add(criteriaBuilder.equal(fromTable3SubQuery.get(c), '2'));
subRestrictions.add(criteriaBuilder.in(fromTable3SubQuery.get(ecu_id)).value(subQueryForInExpression));
restrictions.add(criteriaBuilder.exists(subQuery.select(
fromTable3SubQuery.get(c)).where(
subRestrictions.toArray(new Predicate[0]))));
-> 这仅代表了最后一个Exists
部分,它要求 c = '2' 的连接。其他部分类似,但没有subQueryForInExpression
的部分…
英文:
So this is one solution in SQL:
http://tpcg.io/mV3rZ2Of
This is very long as a JPA Criteria Query, so I only show how I translated the last Exists
condition, which was the hardest part:
Subquery<Table3> subQuery = searchQuery.subquery(Table3.class);
Root<Table3> fromTable3SubQuery = subQuery.from(Table3.class);
List<Predicate> subRestrictions = new ArrayList<>();
Subquery<Table2> subQueryForInExpression = searchQuery.subquery(Table2.class);
Root<Table2> fromTable2SubQuery = subQueryForInExpression.from(Table2.class);
subQueryForInExpression.select(fromTable2SubQuery.get(ID)).where(criteriaBuilder.equal(fromTable2SubQuery.get(info_id), root.get(ID)));
subRestrictions.add(criteriaBuilder.equal(fromTable3SubQuery.get(c), '2'));
subRestrictions.add(criteriaBuilder.in(fromTable3SubQuery.get(ecu_id)).value(subQueryForInExpression));
restrictions.add(criteriaBuilder.exists(subQuery.select(
fromTable3SubQuery.get(c)).where(
subRestrictions.toArray(new Predicate[0]))));
-> This represents only the last Exists
part, which is asking for the c = '2' connection. The others are similar, but without the subQueryForInExpression
parts...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论