MySQL相关——MySQL针对varchar排序问题

来源：互联网发布：淘宝服装拍照技巧编辑：程序博客网时间：2024/05/29 13:15

最近做项目，在设计数据库表的时候一时疏忽把用于排序的字段设为了varchar类型，最后查询的时候发现了问题，也懒着改数据库了，就研究如何把varchar类型转成number再排序

写SQL语句比较简单，最省事的处理：

select * from table order by (varcharOrderCol + 0);

比较文艺一点的：

select * from table order by CONVERT(varcharOrderCol, SIGNED);

问题是我项目中用的都是hibernate的Criteria做查询，这样并不能直接用，后来又研究了一下

可以重写一下org.hibernate.criterion.Order，代码如下：

import java.sql.Types;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Order;
import org.hibernate.engine.SessionFactoryImplementor;
import org.hibernate.type.Type;

public class VarcharOrder extends Order {

private static final long serialVersionUID = 1L;
private boolean ascending;
    private boolean ignoreCase;
    private String propertyName;

    @Override
    public String toString() {
        return "CONVERT( " + propertyName + ", SIGNED) " + (ascending ? "asc" : "desc");
    }

    @Override
    public Order ignoreCase() {
        ignoreCase = true;
        return this;
    }


    protected VarcharOrder(String propertyName, boolean ascending) {
        super(propertyName, ascending);
        this.propertyName = propertyName;
        this.ascending = ascending;
    }


    protected VarcharOrder(String propertyName, String dir) {
        super(propertyName, dir.equalsIgnoreCase("ASC") ? true : false);
        ascending = dir.equalsIgnoreCase("ASC") ? true : false;
        this.propertyName = propertyName;
    }


    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
        Type type = criteriaQuery.getTypeUsingProjection(criteria, propertyName);
        StringBuffer fragment = new StringBuffer();
        for (int i = 0; i < columns.length; i++) {
            SessionFactoryImplementor factory = criteriaQuery.getFactory();
            boolean lower = ignoreCase && type.sqlTypes(factory)[i] == Types.VARCHAR;
            if (lower) {
                fragment.append(factory.getDialect().getLowercaseFunction()).append('(');
            }
            fragment.append("CONVERT( " + columns[i] + ", SIGNED)");
            if (lower)
                fragment.append(')');
            fragment.append(ascending ? " asc" : " desc");
            if (i < columns.length - 1)
                fragment.append(", ");
        }
        return fragment.toString();
    }


    public static Order asc(String propertyName) {
        return new VarcharOrder(propertyName, true);
    }


    public static Order desc(String propertyName) {
        return new VarcharOrder(propertyName, false);
    }
}

使用的时候：

criteria.addOrder(VarcharOrder.asc(field));

这样就OK了！

0 0