How to order result of hibernate based on a specific order

Question

I need to send a query to retrieve values that has a specific group of characters as following:

Lets say I am interested in 'XX' so it should search for any field that its value starts with 'XX' or has ' XX' (space XX). For example XXCDEF, PD XXRF and CMKJIEK XX are valid results.

I have following query that returns the correct results but I need to sort them in a way that it first return those with XX at the beginning then other results. As following:

XXABCD
XXPLER
XXRFKF
AB XXAB
CD XXCD
ZZ XXOI
POLO XX

Code

Criteria criteria = session.createCriteria(Name.class, "name")
                .add(Restrictions.disjunction()
                     .add(Restrictions.ilike("name.fname", fname + "%"))
                     .add(Restrictions.ilike("name.fname", "%" + " " + fname + "%"))
                    )
                .setProjection(Projections.property("name.fname").as("fname"));
        List<String> names = (List<String>) criteria.list();

Answer 1

With JPQL (HQL):

select fname from Name
where upper(fname) like :fnameStart or upper(fname) like :fnameMiddle
order by (case when upper(fname) like :fnameStart then 1 else 2 end), fname

query.setParameter("fnameStart", "XX%");
query.setParameter("fnameMiddle", "% XX%");

With Criteria

With Criteria it's much trickier. Firstly, you have to resort to native SQL in the order clause. Secondly, you have to bind the variable.

public class FirstNameOrder extends Order {
    public FirstNameOrder() {
        super("", true);
    }

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return "case when upper(FIRST_NAME) like ? then 1 else 2 end";
    }
}

The case expression syntax and the upper function name should be changed in accordance with your database (and the column name if it's different, of course).

It is easy to add this to the Criteria, but there is no API to bind the parameter.

I tried to trick Hibernate by passing in an unused variable to the custom sql restriction so that it is effectively used for the variable in the order by clause:

Criteria criteria = session.createCriteria(Name.class, "name")
   .add(Restrictions.disjunction()
      .add(Restrictions.ilike("name.fname", fname + "%"))
      .add(Restrictions.ilike("name.fname", "%" + " " + fname + "%")))
   .setProjection(Projections.property("name.fname").as("fname"))
   .add(Restrictions.sqlRestriction("1 = 1", fname + "%", StringType.INSTANCE))
   .addOrder(new FirstNameOrder())
   .addOrder(Order.asc("fname"));

and it works fine.

Obviously, this solution is not recommended and I suggest using JPQL for this query.