As part of our upgrade from Hibernate 5.x to 6.x, we are converting legacy Criteria queries to JPA Criteria. However, we are facing some challenges.
In our current implementation, we use DetachedCriteria in subqueries. Specifically, we apply a min projection on a column, which can be of any type. In SQL, MIN() is valid for VARCHAR columns as well, but while converting to JPA Criteria, we discovered that it is not allowed.
Since we use this as a subquery, we decided to replace DetachedCriteria with subqueries. However, JPA Criteria restricts MIN() usage in subqueries when the result type is a String, enforcing that the result type must be a Number. In our case, the column type is determined dynamically at runtime, and it could be a VARCHAR.
To work around this, we considered using ORDER BY ASC to retrieve the first result, but ordering is also not supported in subqueries.
Given these constraints, what alternatives do we have for replacing DetachedCriteria in this scenario? Would it be possible to create a regular JPA Criteria query instead?
JPA Criteria has 2 functions for the JPQL/HQL min function due to the way the Java type system works. CriteriaBuilder#min is for numbers and CriteriaBuilder#least is for comparable types, which includes string types.
“Thanks! I think the MIN() issue on a String column can be fixed using CriteriaBuilder#least. However, we have other use cases where we add ORDER BY to detached queries. As I mentioned before, we use detached criteria for subqueries, but in JPA Criteria, ORDER BY is not allowed in subqueries. Could you help me with this issue as well? Thanks in advance!”
JPA Criteria does not allow order by in a subquery because it makes no sense with the features JPA Criteria offers. Ordering in subqueries only matters if you can use limit/offset and Hibernate ORM does add support for that.
For every JPA Criteria interface, Hibernate ORM has an extension interface e.g. JpaSubQuery for Subquery, JpaExpression for Expression etc. These extension interfaces provide Hibernate ORM supported features on top of the JPA Criteria contracts.
Use HibernateCriteriaBuilder which is the extension of the CriteriaBuilder interface, where every method is covariantly overridden to return the extension type.