JPA Criteria orderBy: unexpected AST node

Question

I have the following criteria query, which retrieves some fields from Anfrage and Sparte entities and also the translated string for the sparte.i18nKey. This works as expected if I dont use orderBy.

Now I have the requirement to sort by the translated string for sparte.i18nKey and using the orderBy as shown below, results in QuerySyntaxException: unexpected AST node

So the problem must be the subselect in the orderBy clause!

select distinct new
  my.domain.model.dto.AnfrageDTO(
      anfrage0.id,
      anfrage0.name,
      anfrage0.sparte.id,
      anfrage0.sparte.i18nKey,
      -- retrieve translated string for sparte.i18nKey
      (select rb0.value from at.luxbau.mis2.domain.model.ResourceBundleEntity as rb0
       where (anfrage0.sparte.i18nKey = rb0.key) and (rb0.language = 'de'))
  )
from my.domain.model.impl.Anfrage as anfrage0
  left join anfrage0.sparte as sparte
order by (
  -- sort by translated string for sparte.i18nKey
  select rb1.value
  from my.domain.model.ResourceBundleEntity as rb1
  where (anfrage0.sparte.i18nKey = rb1.key) and (rb1.language = 'de')
) asc

My Java code looks like this:

private List<AnfrageDTO> getAnfragen() {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<AnfrageDTO> query = cb.createQuery(AnfrageDTO.class);
    Root<Anfrage> anfrage = query.from(Anfrage.class);  
    anfrage.join(Anfrage_.sparte, JoinType.LEFT);

    query.select(cb.construct(AnfrageDTO.class,
        anfrage.get(Anfrage_.id),
        anfrage.get(Anfrage_.name),
        anfrage.get(Anfrage_.sparte).get(Sparte_.id),
        anfrage.get(Anfrage_.sparte).get(Sparte_.i18nKey),
        // create subquery for translated sparte.i18nKey
        createResourceBundleSubQuery(cb, query, anfrage.get(Anfrage_.sparte).get(Sparte_.i18nKey)).getSelection()));

    TypedQuery<AnfrageDTO> tq = entityManager
            .createQuery(query)
            // use subquery to sort by translated sparte.i18nKey
            .orderBy(cb.asc(createResourceBundleSubQuery(cb, query, anfrage.get(Anfrage_.sparte).get(Sparte_.i18nKey))));

    tq.setMaxResults(10);
    List<AnfrageDTO> anfragen = tq.getResultList();
    return anfragen;
}

public Subquery<String> createResourceBundleSubQuery(CriteriaBuilder cb, CriteriaQuery<?> query, <String> expr) {
    Subquery<String> subquery = query.subquery(String.class);
    Root<ResourceBundleEntity> rb = subquery.from(ResourceBundleEntity.class);
    subquery
        .select(rb.get(ResourceBundleEntity_.value))
        .where(cb.and(
                      cb.equal(expr, rb.get(ResourceBundleEntity_.key)),
                      cb.equal(rb.get(ResourceBundleEntity_.language), "de")));
    return subquery;
}

Using a native SQL query with subselect in orderBy works also as expected.

select distinct  
  anfrage0_.id,
  anfrage0_.name,
  anfrage0_.sparte_id,
  sparte4_.i18n_key,
  (select rb3.i18n_value from resource_bundle rb3 where rb3.language_code = 'de' and rb3.i18n_key = sparte4_.i18n_key) as sparte_i18n_value
from
  mis2.anfrage anfrage0_
  left outer join mis2.sparte sparte4_ on anfrage0_.sparte_id = sparte4_.id
order by (
  select rb.i18n_value 
  from mis2.resource_bundle rb    
  where sparte4_.i18n_key = rb.i18n_key and rb.language_code = 'de'
) asc

Also using an alias in the native SQL query works also as expected.

select distinct  
  anfrage0_.id,
  anfrage0_.name,
  anfrage0_.sparte_id,
  sparte4_.i18n_key,
  (select rb3.i18n_value from resource_bundle rb3 where rb3.language_code = 'de' and rb3.i18n_key = sparte4_.i18n_key) as sparte_i18n_value
from
  mis2.anfrage anfrage0_
  left outer join mis2.sparte sparte4_ on anfrage0_.sparte_id = sparte4_.id
order by sparte_i18n_value
asc

It would be great if JPA Criteria API would support using an alias in orderBy clause!

Any hints welcome - Thank you!

My environment is WildFly 11 and PostgreSQL 9.6.

The answer depends on the JPA provider in use. Judging by the SQL generated you use Hibernate. The JPA spec does not specify the use of a SUBQUERY in the ORDER clause. Some JPA providers (e.g DataNucleus) do allow that, though yours seemingly doesn't. — , Apr 18 '18 at 17:31

score 0 · Answer 1 · answered Apr 18 '18 at 15:48

0

JPA doesn't support passing parameter in order by clause, your problem has been asked before: Hibernate Named Query Order By parameter

answered Apr 18 '18 at 15:48

thoai nguyen

66
1
4

He is using a *SUBQUERY*, not a PARAMETER in the ORDER clause. The JPA spec doesn't specify their use anywhere but WHERE/HAVING clauses yes, but it is a SUBQUERY. – Apr 18 '18 at 17:29
Yes, both SUBQUERY and parameter make the ORDER clause dynamic in the query and the JPA doesn't support it. – thoai nguyen Apr 19 '18 at 01:35

JPA Criteria orderBy: unexpected AST node

1 Answers1