用于加入子查询的JPA CriteriaBuilder

Question

我有这样的SQL语句：

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

因此，它为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进行此操作。

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

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

怎么对这个？

马可

Answer 1

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

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));

此代码将创建以下查询：

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上进行了测试。两者兼具魅力。