需求:根据人员职务高低进行排序,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())
...
网友评论