0

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.

stalet
  • 1,345
  • 16
  • 24

1 Answers1

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()

Lalit Rao
  • 551
  • 5
  • 22
  • Can i use `order by` when using native query ? – stalet May 07 '15 at 11:35
  • 1
    Yes you can, give it a try man. Hibernate is great implementation of JPA and it provides great features. It worked for me there, it should work for you here – Lalit Rao May 07 '15 at 11:37
  • 1
    @stalet **NativeQuery** term speaks for itself. U can pass plain SQL query as parameter to create JPA's `Query` object and to obtain `List` I – Lalit Rao May 07 '15 at 11:40
  • It seems im getting a MappingException telling me that AggregatedInfo is not an entity. When using native queries i also have to add some mapping configurations... I will try rewriting the lots! – stalet May 07 '15 at 11:47
  • 1
    No issues, try replacing `em.createNativeQuery(hql, AggregatedInfo.class)` with `em.createNativeQuery(hql)`. The output will be same as you want. If that didnt worked for you at the first place, try adding `@Entity` annotation on top of your `class AggregatedInfo`. Though i have no idea if JPA supports annotation. trying adding and then press **CTRL+ SHIFT+ I** tocken to let your IDE find proper package for it – Lalit Rao May 07 '15 at 11:50
  • Changing it to Entity doesnt work. But by adding a `@SqlResultSetMapping` with a `@ConstructorResult` however does fix the mapping problem. Then I used `em.createNativeQuery(hql, "mappingname")`. However with this solution i cannot write JPQL language type queries. I have to change to rewrite to plain sql and map the result in a resultsetmapper. It will solve the problem of sorting but without JPQL. So I am still looking for a way to use ordinary Query with JPQL and order the result. – stalet May 07 '15 at 12:19
  • If no-one comes around and have an answer with the use of createQuery - this is the accepted answer. – stalet May 07 '15 at 12:23