javahibernatejpacriteriacriteria-api

JPA Criteria - Aliases that does not work on aggregation selection

In a project that uses Hibernate 6.4, I am using the Criteria API provided through Jakarta persistence API (a dependency of Hibernate). However, I encountered a bug while using aliases on aggregation functions on selections.

Let's consider that we have an entity MyEntity with the following attributes :

We will assume that the annotated class is correctly written accordingly to this description, and correctly imported during the execution of the application.

What I want to do here is to select the attr1 values and the sum of attr2 values from the table associated to this entity and order the result by the sum of attr2 values.

Here is the code corresponding to what I wrote in my project (I cannot give you the exact code, so I illustrate what I have done with this example) :

public class MyClass {
    public List<Object[]> myMethod(Session session, String orderAttribute) {
        CriteriaBuilder cb = session.getCriteriaBuilder();
        CriteriaQuery<?> cq = cb.createQuery(Object[].class);
        Root<MyEntity> root = cq.from(MyEntity.class);

        List<Selection<?>> selections = new ArrayList<>();

        selections.add(root.get("attr1"));

        Selection<Number> sumAttr2 = cb.sum(root.get("attr2")).alias("sumAttr2");
        selections.add(sumAttr2);

        cq.multiselect(selections);
        cq.orderBy(cb.asc(root.get(orderAttribute)));

        Query<Object[]> query = session.createQuery(cq);
        List<Object[]> result = query.getResultList();

        return result;
    }
}

When I execute that with orderAttribute="attr1", it works as expected. But when I execute the query with orderAttribute="sumAttr2", it fails and give me the following error :

org.hibernate.query.sqm.PathElementException: Could not resolve attribute 'sumAttr2' of 'com.example.MyClass'

I don't have the SQL query generated during the error case because it seems to fail at the generation of the query and not during its execution. However, I have the query of the case orderAttribute="attr1" which correspond to this one :

select me1_0.attr1,sum(me1_0.attr2) from my_entity me1_0 order by 1

I suppose that the issue is due to the alias, because it does not appear in the query (I don't have sum(me1_0.attr2) as sumAttr2), and when I try to order the result according to this missing alias, I have the error because the API "think" it should be an attribute of the entity.

I have tried multiples ways to write the alias, but none of them worked.

Selection<Number> sumAttr2 = cb.sum(root.get("attr2")).alias("sumAttr2");
selections.add(sumAttr2);

Expression<Number> sumAttr2 = criteriaBuilder.sum(root.get("attr2"));
Selection<Number> sumAttr2Selection = sumAttr2.alias("sumAttr2");
selections.add(sumAttr2Selection);

Expression<Number> sumAttr2 = criteriaBuilder.sum(root.get("attr2"));
sumAttr2.alias("sumAttr2");
selections.add(sumAttr2)

Can you help me ? What I want is to get something equivalent to the following query using Criteria API :

select me1_0.attr1,sum(me1_0.attr2) as sumAttr2 from my_entity me1_0 order by sumAttr2
Solution

  • Ok, I found the solution : we have to use the Expression object returned by CriteriaBuilder.sum() method.

    Here is my implementation :

    public class MyClass {
    public List<Object[]> myMethod(Session session, String orderAttribute) {
        CriteriaBuilder cb = session.getCriteriaBuilder();
        CriteriaQuery<?> cq = cb.createQuery(Object[].class);
        Root<MyEntity> root = cq.from(MyEntity.class);

        // Selections
        List<Selection<?>> selections = new ArrayList<>();

        selections.add(root.get("attr1"));

        Expression<Number> sumAttr2 = criteriaBuilder.sum(root.get("attr2"));
        Selection<Number> sumAttr2Selection = sumAttr2.alias("sumAttr2");
        selections.add(sumAttr2Selection);

        cq.multiselect(selections);
        // End of selections

        // Order
        Expression<?> sortIndex;
        switch (criteres.getColonneTri()) {
            case "sumAttr2":
                sortIndex = somme;
                break;
            // Here, other alias cases if any.
            default:
                sortIndex = root.get(orderAttribute);
                break;
        }
        cq.orderBy(cb.asc(sortIndex));
        // End of order

        Query<Object[]> query = session.createQuery(cq);
        List<Object[]> result = query.getResultList();

        return result;
    }
}

    And the corresponding SQL query generated by Hibernate/JPA is :

    select me1_0.attr1,sum(me1_0.attr2) from my_entity me1_0 order by 2

    Two things to note here :