0

In MySQL I can write sql with order by in

select * 
from a
order by a.status in (1,2) desc;

But if I write hql in JPA like (generated by QueryDSL)

select activityInfoEntity
from ActivityInfoEntity activityInfoEntity
order by activityInfoEntity.status in (?1)

will throw

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: in near line 3, column 36 [select activityInfoEntity
from x.x.x.ActivityInfoEntity activityInfoEntity
order by activityInfoEntity.status in (?1) desc]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:284)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:131)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:93)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1836)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)

How can I do this in JPA ?

wener
  • 7,191
  • 6
  • 54
  • 78
  • 2
    I don't think pure JPA supports this, q.v. [here](http://stackoverflow.com/questions/21484176/how-to-use-a-dynamic-parameter-in-a-in-clause-of-a-jpa-named-query), but you could do it in Hibernate or in a native query. – Tim Biegeleisen Nov 02 '16 at 03:02
  • Hibernate don't support this neither, because I execute this JPQL with hibernate. – wener Nov 02 '16 at 03:23
  • if you just hardcode the (1,2) in the ORDER BY clause ? JPQL doesn't allow parameters there, but with the JPA provider I use I can specify IN clauses in the ORDER BY without problem (not that it is in the JPA spec) – Neil Stockton Nov 02 '16 at 08:17
  • I can't hardcode this, must be a parameter.I found a dirty way to make the whole thing work. – wener Nov 02 '16 at 09:12
  • @NeilStockton Even I hardcode (1,2) in ORDER BY, JPQL still throw exception. – wener Nov 02 '16 at 09:26
  • ok, was just a thought ... DataNucleus allows it like that, and they also allow parameters in ORDER BY with latest code I think. Raise an issue on your JPA provider to support it (and use a native query in the short term)? – Neil Stockton Nov 02 '16 at 09:32

0 Answers0