QueryDsl orderBy specific string values

Question

I need to sort my database response by three different strings.
Field can take only those four values "A", "B", "C", null.
Is there a way to sort data in custom defined order while retrieving it from DB using QueryDsl?

customOrder = ["B", "A", "C"]

Answer 1

The solution is to assign numbers to cases using CASE and sort those numbers with ASC/DESC.

Solution in SQL:

SELECT *
FROM table
ORDER BY CASE 
   WHEN field = 'B' THEN 1,
   WHEN field = 'A' THEN 2,
   WHEN field = 'C' THEN 3
   ELSE 4
   END

Solution:

private OrderSpecifier<Integer> provideStatusOrder() {
        NumberExpression<Integer> cases = new CaseBuilder()
                                                  .when(field.eq("B"))
                                                  .then(1)
                                                  .when(field.eq("C"))
                                                  .then(2)
                                                  .when(field.eq("A"))
                                                  .then(3)
                                                  .otherwise(4);
        return new OrderSpecifier<>(Order.ASC, cases);
}

Usage:

return query.orderBy(provideStatusOrder())
            .fetch();