Is this SQL query possible in JPA2 critera API?

0
votes

SELECT * FROM tbl WHERE version = (SELECT MAX(version) FROM tbl WHERE t2.entityId = t1.entityId)

Tbl describes different versions of entities where each combination of entityId and version is unique. I want to find the highest version of each entityId(the whole row, not just the version number). Is this possible with Criteria API? The only uses of subqueries I have seen only involves using "IN" rather than comparing values. I've also thought of converting the subquery to a complex LEFT JOIN, but Criteria API doesn't support custom ON-statements.

Is this possible? I'd rather avoid using JPQL (if it is even possible with JPQL) due to the query being very dynamic.

1
hibernatejpa-2.0criteria-api

1 Answers

0
votes

Solved using criteriaBuilder.in(path).value(subquery).