Custom sort order for an enum field

Question

Is it possible to change alphabetical sorting in JPA to self defined?

I have this data in column division:

BRONZE
SILVER
GOLD
DIAMOND

And I have mapped this to an entity field:

public enum Division {
    BRONZE,
    SILVER,
    GOLD,
    DIAMOND
}

@Entity
public class MyEntity {

  @Enumerated(EnumType.STRING)
  private Division division;

  ...
}

If I use default sorting I get an alphabetical order:

• BRONZE
• DIAMOND
• GOLD
• SILVER

But I want to have sorting by quality of ore (in the order of the enum).

Answer 1

Sorting (or ordering) takes place on the database level. So it is not possible to define a custom Comparator or similar - if that was your intention. Instead the database will need to know the quality somehow to use it as order criteria.

One option is to sort by the ordinal of the enum. That is only possible if you store it in the DB - instead of the name:

@Enumerated(EnumType.ORDINAL) // The default
private Division division;

A second option is to convert the enum to an Entity and store the quality as new integer field explicitly. The disadvantage of that is, that you can't use constant values any more.

A third option is a Formula field with the explicit name to quality mapping (you can order by formula fields as well):

@Enumerated(EnumType.STRING)
private Division division;

@Formula("case division when 'BRONZE' then 0 when 'SILVER' then 1 ... end")
private int divisionQuality;

This is the slowest option - if you have many rows in your table.