美文网首页
mysql自定义字符串排序规则(对照hibernate实现)

mysql自定义字符串排序规则(对照hibernate实现)

作者: Deo_Prime | 来源:发表于2020-11-12 18:00 被阅读0次

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

    相关文章

      网友评论

          本文标题:mysql自定义字符串排序规则(对照hibernate实现)

          本文链接:https://www.haomeiwen.com/subject/efbpbktx.html