any way to force a particular order when sorting by a String field with jpa

Question

within our domain/entity object we have an enum field, but the enum value is stored as a string in db

@Column(name = "priority")
@Enumerated(EnumType.STRING)
public Priority getPriority() {
    return priority;
}

so when we try to order by this field with jpa findall method

order by
   priority

in can only order it alphabetically! However we need to assign order to the various values there.

public enum Priority { 
    Critical, 
    Major, 
    Minor, 
    Informational
}

Is there anyway to define an order here that jpa will follow when ordering/sorting?

i know its possible with pure sql

order by (case priority when 'Critical' then 0 when 'Major' then 1 when 'Minor' then 2 else 3 end);

seems like there should be a way todo it with jpa

Answer 1

JPQL supports CASE too. With Hibernate 5.6.x as the persistence provider, I was able to run the following queries successfully:

// define the mapping from priority name to value in the SELECT clause
Query q = em.createQuery("SELECT a, CASE " +
    "WHEN a.priority='Critical' THEN 4 " +
    "WHEN a.priority='Major' THEN 3 " +
    "WHEN a.priority='Minor' THEN 2 " +
    "ELSE 1 END AS pri " +
  "FROM AnEntity a ORDER BY pri");
// remember this returns an Object[]!
@SuppressWarnings("unchecked")
List<Object[]> results = q.getResultList();
// the results are in the correct order, the first element is the AnEntity

I was surprised to see that the case works in the ORDER BY clause too - but this may be a Hibernate-specific extension (see below):

Query q = em.createQuery("SELECT a FROM AnEntity a ORDER BY " +
    "CASE WHEN a.priority='Critical' THEN 4 " +
    "WHEN a.priority='Major' THEN 3 " +
    "WHEN a.priority='Minor' THEN 2 " +
    "ELSE 1 END");

The JPA 2.1 specification, ch.4.6.17.4 defines Case Expressions as Scalar Expressions. In ch.4.6.17 "Scalar Expressions", it says:

Scalar expressions may be used in the SELECT clause of a query as well as in the WHERE and HAVING clauses.

So using it in the ORDER BY may be Hibernate-specific, but I would give it a try and then fall back to the first option.