JOIN子查询使用Spring Data JPA规范

Question

经过看似无休止的研究，我终于看到了希望，但在这里问。

我正试图将一个SQL查询连接到一个Spring Data JPASpecification的子查询。JOIN的原因是为了获得foo分组的最新timestamp。使用的技术是SpringDataJPA, Hibernate和PostgreSQL数据库。

Java实体和普通SQL查询如下(省略构造函数、getter、setter和其他字段)。

public class MyClass {
OffsetDateTime timestamp;
Foo foo;
// Some more fields
}

SELECT *
FROM myclass
JOIN(SELECT MAX(timestamp) AS timestamp
FROM myclass
GROUP BY foo) AS in1 ON myclass.timestamp = in1.timestamp;

我已经有了下面的实现，它产生了下面的堆栈跟踪。另外，我不太知道return在这里是什么?

return new Specification<MyClass>() {
@Override
public Predicate toPredicate(Root<MyClass> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
CriteriaQuery<MyClass> query = criteriaBuilder.createQuery(MyClass.class);
Root<MyClass> root = query.from(MyClass.class);
Join<MyClass, MyClass> join = root.join("timestamp");
join.on(criteriaBuilder.equal(join.get("timestamp"), root.get("timestamp")));
return // ... what actually?
}
};

org.hibernate.query.criteria.internal.BasicPathUsageException: Cannot join to attribute of basic type

---

编辑:在JOIN旁边，以下2个备选方案也可以工作。然而，我仍然不知道如何将它们表示为Criteria/Predicate。

替代1:包含多个列的子选择。

SELECT *
FROM myclass
WHERE (timestamp, foo) IN (SELECT MAX(timestamp) AS timestamp, foo
FROM myclass
GROUP BY foo);

替代2:在FROM上选择多表

SELECT *
FROM myclass,
(SELECT MAX(timestamp) AS timestamp, foo
FROM myclass
GROUP BY foo) AS in1
WHERE myclass.timestamp = in1.timestamp
AND myclass.foo = in1.foo;

Answer 1

FROM子句中的子查询不受JPA和Hibernate的支持(Hibernate正在处理这个问题)，所以如果你想坚持使用标准，你只有一个选择，这不是100%正确的，因为它会产生联系，即如果多个对象具有相同的最大时间戳，你会看到所有的子查询:

SELECT e FROM myclass e WHERE EXISTS (
SELECT 1
FROM myclass e2
GROUP BY e2.foo
HAVING e.foo = e2.foo AND e.timestamp = MAX(e2.timestamp)
)

存在的谓词可以用JPA标准建模，如下所示:

return new Specification<MyClass>() {
@Override
public Predicate toPredicate(Root<MyClass> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Subquery<Integer> subquery = query.subquery(Integer.class);
subquery.select(criteriaBuilder.literal(1));
Root<MyClass> subRoot = subquery.from(MyClass.class);
subquery.groupBy(subRoot.get("foo"));
subquery.having(
criteriaBuilder.and(
criteriaBuilder.equal(
root.get("timestamp"), 
criteriaBuilder.max(subRoot.get("timestamp"))
),
criteriaBuilder.equal(
root.get("foo"), 
subRoot.get("foo")
)
)
);
return criteriaBuilder.exists(subquery);
}
};

如果你想要排除关系，你将需要支持限制结果，这在JPA和Hibernate中还不支持子查询。您可以为此目的使用Blaze-Persistence，尽管它支持JPA/Hibernate之上的许多其他高级SQL特性。