Is it possible to SELECT SUM(x) FROM (Subquery) in recent versions of JPA/Hibernate?

Hibernate ORM
1

Hi there,

Is it possible to perform a query like SELECT SUM(x) FROM (SELECT MAX(X) FROM MyTable) using recent versions of JPA (or hibernate)?

I’m using Release v3.2.3 · spring-projects/spring-boot · GitHub so have access to Hibernate 6.4.4.Final.

I’ve been struggling to put this query together just using the JPA criteria builder. I think this used to be the case with older versions and led me to using native queries instead.

Thanks for any help!

3

I might have answered my own question, this seems to work for me.

	final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
	final CriteriaQuery<BigDecimal> criteriaQuery = cb.createQuery( BigDecimal.class );
	final Subquery<BigDecimal> subquery = criteriaQuery.subquery( BigDecimal.class );
	final Root<MyTableEntity> subRoot = subquery.from( MyTableEntity.class );
	
	subquery.select( cb.max( subRoot.get( "amount" ) ) );
	subquery.select(criteriaBuilder.max(subRoot.get("x")));
        subquery.where(criteriaBuilder.equal(subRoot.get("x"), 123)); // Equals some value
	subquery.groupBy( subRoot.get( "eventId" ), subRoot.get( "rank" ) );
	criteriaQuery.select( cb.sum( subquery ) );

	return entityManager.createQuery(criteriaQuery).getSingleResult();
4

It’s possible, but you seem to have found a different way to reach the same result.

final HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();
final JpaCriteriaQuery<BigDecimal> criteriaQuery = cb.createQuery( BigDecimal.class );
final JpaSubquery<BigDecimal> subquery = criteriaQuery.subquery( BigDecimal.class );
final JpaRoot<MyTableEntity> subRoot = subquery.from( MyTableEntity.class );
	
subquery.select( cb.max( subRoot.get( "amount" ) ).alias( "amount" ) );
subquery.select(criteriaBuilder.max(subRoot.get("x")));
subquery.where(criteriaBuilder.equal(subRoot.get("x"), 123)); // Equals some value
subquery.groupBy( subRoot.get( "eventId" ), subRoot.get( "rank" ) );
final JpaRoot<Tuple> root = criteriaQuery.from( subquery );
criteriaQuery.select( cb.sum( root.get("amount") ) );

return entityManager.createQuery(criteriaQuery).getSingleResult();
5

What is minimum Hibernate version for this to work?

6

ORM 6.1. Also see Hot features of Hibernate ORM 6.1 - In Relation To

1 Like