JPA标准的基于Criteria的查询,同时对三个查询求交集。

huangapple go评论89阅读模式
英文:

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 的查询,不能使用原生的 SQLJPQL

我们可以执行三个不同的基于 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.
JPA标准的基于Criteria的查询,同时对三个查询求交集。

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&lt;Table3&gt; subQuery = searchQuery.subquery(Table3.class);
Root&lt;Table3&gt; fromTable3SubQuery = subQuery.from(Table3.class);
List&lt;Predicate&gt; subRestrictions = new ArrayList&lt;&gt;();

Subquery&lt;Table2&gt; subQueryForInExpression = searchQuery.subquery(Table2.class);
Root&lt;Table2&gt; 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), &#39;2&#39;));
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 Existspart, which is asking for the c = '2' connection. The others are similar, but without the subQueryForInExpression parts...

huangapple
  • 本文由 发表于 2020年3月17日 00:28:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/60709722.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定