JPA CriteriaBuilder，用于加入子查询 [英] JPA CriteriaBuilder for join in subquery

问题描述

我有这样的SQL语句:

I have SQL statement something like this :

SELECT * FROM person inner join (select max(validityId) as maxID from person group by personId) maxID on maxID.maxID = person.validityid;

因此它为我提供了personID的与众不同"行:

So it give me "distinct" row for personID:

如果我有这样的表:

|  personID  |  validitID | value   |
-------------------------------------------
|    1       |    10      | 400     |
|    1       |    11      | 500     |
|    1       |    12      | 600     |
|    2       |    13      | 700     |
|    2       |    14      | 800     |
|    2       |    15      | 900     |
|    4       |    16      | 1000    |

它将返回

|  personID  |  validitID | value   |
-------------------------------------------
|    1       |    12      | 600     |
|    2       |    15      | 900     |
|    4       |    16      | 1000    |

现在，我尝试通过JPA CriteriaBuilder进行此操作.

Now I try to do this by JPA CriteriaBuilder.

我的第一个想法是子查询:

My first thought was sub query :

final CriteriaBuilder cb = this.em.getCriteriaBuilder();
final CriteriaQuery<Person> cq = cb.createQuery(Person.class);
final Root<Person> root = cq.from(Person.class);
cq.select(root);

final Subquery<Long> subquery = cq.subquery(Long.class);
final Root<Person> validityIDSQ = subquery.from(Person.class);
subquery.select(validityIDSQ.get(Person_.validityId));
subquery.groupBy(validityIDSQ.get(Person_.personId));

cb.where(cb.in(root.get(Person_.validityId)).value(subquery));

但这会产生错误

 ERROR: column "person1_.validityid" must appear in the GROUP BY clause or be used in an aggregate function

该怎么走?

Marko

推荐答案

我认为解决方案比看起来简单.您忘记了在CriteriaBuilder子查询中包含cb.max ().以下代码执行您要查找的查询.

I think the solution is simpler than it seems. You forgot to include cb.max () in the CriteriaBuilder subquery. The following code executes the query you are looking for.

final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<Person> cq = cb.createQuery(Person.class);
final Root<Person> root = cq.from(Person.class);
cq.select(root);

final Subquery<Integer> subquery = cq.subquery(Integer.class);
final Root<Person> validityIDSQ = subquery.from(Person.class);
subquery.select(cb.max(validityIDSQ.get(Person_.validityID)));
subquery.groupBy(validityIDSQ.get(Person_.personID));

cq.where(cb.in(root.get(Person_.validityID)).value(subquery));

此代码将创建以下查询:

This code will create the following query:

select
    person0_.id as id1_0_,
    person0_.personID as personID2_0_,
    person0_.validityID as validity3_0_,
    person0_.value as value4_0_
from
    person person0_
where
    person0_.validityID in (
            select
                max(person1_.validityID)
            from
                person person1_
            group by
                person1_.personID)

我认为您正在使用Postgres.如果不使用cd.max()，它将生成您引用的错误，因为您使用GroupBy而不使用聚合函数.我在Postgres和MySQL上进行了测试.两者兼具魅力.

I think you are using Postgres. Without cd.max() it generates the error you cited because you use the GroupBy without using an aggregate function. I tested it on Postgres and MySQL. Runs like a charm on both.

这篇关于JPA CriteriaBuilder，用于加入子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！