order by when using Constructor Expressions in JPA 2

Question

As a part of JSR-338 came the feature of using Constructor Expressions. The question is how I can use order by when using Constructor Expressions.

Given the example JPQL:

select com.acme.AggregatedInfo(
    c,
    (select status from Account where ssn = c.ssn)
)
from Customer c 
where m.id in (:customerIdList)    

And the class AggregatedInfo

class AggregatedInfo {

    private final Customer customer;
    private final String status;

    public AggregatedInfo(Customer customer, String status) {...}

    // boilerplate javacode....
}

Im using this from a DAO like this:

public List<AggregatedInfo> getAggregatedResult(final List<Long> customerIdList)
    return em.createQuery(hql, AggregatedInfo.class)
        .setParameters("customerIdList", customerIdList)
        .getResultList();
}

If I want to order by the status - how can this be done via JPQL ?

I have tried the following without success:

select com.acme.AggregatedInfo(
    c,
    (select status from Account where ssn = c.ssn)
) as a
from Customers c 
where m.id in (:customerIdList)    
order by c.status

But this does not work.

Is it doable ? Please explain.

Answer 1

Try the following. It worked for me with the similar implementation in Hibernate. It should work for you too

public List<AggregatedInfo> getAggregatedResult(final List<Long> customerIdList)
    return em.createNativeQuery(hql, AggregatedInfo.class)
        .setParameters("customerIdList", customerIdList)
        .getResultList();
}

Replace entityManager.createQuery() with entityManager.createNativeQuery()