需求：根据人员职务高低进行排序，mysql的查询语句为：

1. 先说一下纯mysql的实现：

SELECT
    * 
FROM
    t_user
ORDER BY
CASE
    WHEN title = '理事长' THEN 1
    WHEN title = '副理事长' THEN    2
    WHEN title = '副理事长兼秘书长' THEN    3
    WHEN title = '无' THEN   4
    ELSE 5 END
ASC

注意else语句最后的end

2. 使用criteria实现

由于hql的实现与sql非常相近，所以不再阐述。
对于criteria，需要自定义类继承org.hibernate.criterion.Order，并重写toSqlString方法，我直接贴出我的实现类：

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Order;

public class CwlshOrder extends Order {

    /**
     * 
     */
    private static final long serialVersionUID = 501447529420578962L;
    
    private String propertyName;

    protected CwlshOrder(String propertyName, boolean ascending) {
        super(propertyName, ascending);
        this.propertyName = propertyName;
    }
    
    public static CwlshOrder newInstance() {
        return new CwlshOrder("title", true);
    }
    
    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        
        String key = criteriaQuery.getSQLAlias(criteria) + "." + propertyName.toUpperCase();
        
        StringBuffer buffer = new StringBuffer();
        buffer.append(" case ");
        buffer.append(" when " + key + " = '理事长' then 1 ");
        buffer.append(" when " + key + " = '副理事长' then 2 ");
        buffer.append(" when " + key + " = '副理事长兼秘书长' then 3 ");
        buffer.append(" when " + key + " = '无' then 4 ");
        buffer.append(" else 5 ");
        buffer.append(" end asc ");
        return buffer.toString();
    }

}

使用时：

...
criteria.addOrder(CwlshOrder.newInstance())
...