JPA/Hibernate CASE in @OrderBy annotation throws exception: unexpected token: CASE

Question

I am having trouble getting an @OrderBy on a @OneToMany List to work with JPA/Hibernate.

Here are the table mappings:

Club entity:

@Entity
@Table(name = "Clubs")
public class Club implements Serializable
{
    private static final long serialVersionUID = 1L;

    @Id
    @Column
    private Integer id;

    ...

    @OrderBy("SUBSTRING( teamTypeCode, 1, 1) ASC, CASE WHEN SUBSTRING( teamTypeCode, 1, 1 ) = 'O' THEN -CAST(SUBSTRING( teamTypeCode, 2, 2 ) AS DECIMAL) ELSE CAST(SUBSTRING( teamTypeCode, 2, 2 ) AS DECIMAL) END DESC, SUBSTRING( teamTypeCode, 4, 1 ) DESC, ordinalNbr")
    @OneToMany(mappedBy = "club")
    private List<Team> teams;

    ...
}

Team entity:

@Entity
@Table(name = "Teams")
@IdClass(TeamId.class)
public class Team implements Serializable
{
    @Id
    @Column(name = "club_id")
    private Integer clubId;

    @Id
    @Column(name = "team_type_code")
    private String teamTypeCode;

    @Id
    @Column(name = "ordinal_nbr")
    private Integer ordinalNbr;

    ...
}

A club has multiple teams, nothing special.

However, I want them to sort by Seniors over 20 first, then Seniors over 30, Seniors over 35, ... up to Seniors over 65, then continuing with Juniors under 20, under 18, etc. until under 08.

In SQL, I can get this by using the query:

SELECT *, SUBSTRING( team_type_code, 1, 1 ), SUBSTRING( team_type_code, 2, 2 ), SUBSTRING( team_type_code, 4, 1 ), ordinal_nbr
FROM bbstats.teams
WHERE club_id = 101
ORDER BY SUBSTRING( team_type_code, 1, 1 ) ASC,
         CASE WHEN SUBSTRING( team_type_code, 1, 1 ) = 'O'
              THEN -CAST(SUBSTRING( team_type_code, 2, 2 ) AS DECIMAL)
              ELSE CAST(SUBSTRING( team_type_code, 2, 2 ) AS DECIMAL)
         END DESC,
         SUBSTRING( team_type_code, 4, 1 ) DESC,
         ordinal_nbr;

The 2nd order by clause is of interest. All I do here is get the first char from a 4-letter code like O20M (meaning OVER 20 MALE), if that means "over" then negate the int-cast value, otherwise just take the int-cast value as is an sort DESC.

In (My)SQL this works without problems (you can see the real data here in the first 3 columns):

In Hibernate 5.3.6 however, translating the same to the @OrderBy annotation:

@OrderBy("SUBSTRING( teamTypeCode, 1, 1) ASC, CASE WHEN SUBSTRING( teamTypeCode, 1, 1 ) = 'O' THEN -CAST(SUBSTRING( teamTypeCode, 2, 2 ) AS DECIMAL) ELSE CAST(SUBSTRING( teamTypeCode, 2, 2 ) AS DECIMAL) END DESC, SUBSTRING( teamTypeCode, 4, 1 ) DESC, ordinalNbr")

gives me an exception when starting the server:

01:50:29,345 ERROR [stderr] (ServerService Thread Pool -- 72) line 1:37: unexpected token: CASE

01:50:29,354 INFO  [org.hibernate.orm.beans] (ServerService Thread Pool -- 72) HHH10005004: Stopping BeanContainer : org.hibernate.resource.beans.container.internal.CdiBeanContainerExtendedAccessImpl@24248b3
01:50:29,355 INFO  [org.hibernate.service.internal.AbstractServiceRegistryImpl] (ServerService Thread Pool -- 72) HHH000369: Error stopping service [class org.hibernate.resource.beans.internal.ManagedBeanRegistryImpl] : java.lang.NullPointerException
01:50:29,355 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 72) MSC000001: Failed to start service jboss.persistenceunit."bbstats-0.8.war#BBStatsPU": org.jboss.msc.service.StartException in service jboss.persistenceunit."bbstats-0.8.war#BBStatsPU": javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:195)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:125)
    at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:650)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:209)
    at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
    at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1985)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1487)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1378)
    at java.lang.Thread.run(Thread.java:748)
    at org.jboss.threads.JBossThread.run(JBossThread.java:485)
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:1016)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:942)
    at org.jboss.as.jpa.hibernate5.TwoPhaseBootstrapImpl.build(TwoPhaseBootstrapImpl.java:44)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:167)
    ... 9 more
Caused by: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.collection.OneToManyPersister
    at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:178)
    at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:140)
    at org.hibernate.metamodel.internal.MetamodelImpl.initialize(MetamodelImpl.java:198)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:295)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939)
    ... 11 more
Caused by: org.hibernate.HibernateException: Unable to parse order-by fragment
    at org.hibernate.sql.ordering.antlr.OrderByFragmentTranslator.translate(OrderByFragmentTranslator.java:55)
    at org.hibernate.sql.Template.translateOrderBy(Template.java:724)
    at org.hibernate.persister.collection.AbstractCollectionPersister.<init>(AbstractCollectionPersister.java:556)
    at org.hibernate.persister.collection.OneToManyPersister.<init>(OneToManyPersister.java:69)
    at sun.reflect.GeneratedConstructorAccessor53.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:152)
    ... 16 more
Caused by: java.lang.NullPointerException
    at org.hibernate.sql.ordering.antlr.OrderByFragmentParser.postProcessSortSpecification(OrderByFragmentParser.java:251)
    at org.hibernate.sql.ordering.antlr.GeneratedOrderByFragmentParser.sortSpecification(GeneratedOrderByFragmentParser.java:314)
    at org.hibernate.sql.ordering.antlr.GeneratedOrderByFragmentParser.orderByFragment(GeneratedOrderByFragmentParser.java:198)
    at org.hibernate.sql.ordering.antlr.OrderByFragmentTranslator.translate(OrderByFragmentTranslator.java:49)
    ... 23 more

01:50:29,355 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("deploy") failed - address: ([("deployment" => "bbstats-0.8.war")]) - failure description: {"WFLYCTL0080: Failed services" => {"jboss.persistenceunit.\"bbstats-0.8.war#BBStatsPU\"" => "javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
    Caused by: javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
    Caused by: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.collection.OneToManyPersister
    Caused by: org.hibernate.HibernateException: Unable to parse order-by fragment
    Caused by: java.lang.NullPointerException"}}

According to How to Use CASE, WHEN in JPA however, this is supposed to work.

QUESTION

What's wrong? Is there any alternative trick how to get it done via @OrderBy? Is it a bug? (note, I could still work around it using a manual sort via a Comparator, but I'd like to get it done without - if possible)

---

EDIT:

Using the Hibernate-specific annotation, which uses an SQL clause, results in the same exception:

@org.hibernate.annotations.OrderBy( clause= "SUBSTRING( team_type_code, 1, 1 ) ASC, CASE WHEN SUBSTRING( team_type_code, 1, 1 ) = 'O' THEN -CAST(SUBSTRING( team_type_code, 2, 2 ) AS DECIMAL) ELSE CAST(SUBSTRING( team_type_code, 2, 2 ) AS DECIMAL) END DESC, SUBSTRING( team_type_code, 4, 1 ) DESC, ordinal_nbr" )

Answer 1

According to documentation, you can't use OrderBy annotations like you would. https://docs.oracle.com/javaee/7/api/javax/persistence/OrderBy.html

In you example, it show how to use in query scenario.

Edit: You can still use your 'order by' when querying data, it should work but i'm not sure if it's what you want.