需求：根據(jù)人員職務(wù)高低進行排序页响，mysql的查詢語句為：

1. 先說一下純mysql的實現(xiàn)：

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實現(xiàn)

由于hql的實現(xiàn)與sql非常相近篓足，所以不再闡述。
對于criteria闰蚕，需要自定義類繼承org.hibernate.criterion.Order栈拖，并重寫toSqlString方法，我直接貼出我的實現(xiàn)類：

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())
...