文字を数字としてソートする

MySQL的にはこう

ORDER BY cast(文字になっている列名 AS SIGNED) ASC

今回はHibernateで使いたかったので、HibernateのOrderを継承してOrderAsIntegerを作った

使い方はこんな感じ

Criteria criteria = session.createCriteria(Table.class);
criteria.addOrder(OrderUtil.ascAsInteger(文字列になっている列名));

OrderUtil.java

public class OrderUtil{

    public static Order asc(String propertyName) {
        return Order.asc(propertyName);
    }
    public static Order desc(String propertyName) {
        return Order.desc(propertyName);
    }
    public static Order ascAsInteger(String propertyName) {
        return OrderAsInteger.asc(propertyName, true);
    }
    public static Order descAsInteger(String propertyName) {
        return OrderAsInteger.desc(propertyName, false);
    }

}

OrderAsInteger.java

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 OrderAsInteger extends Order{

    private boolean ascending;
    private boolean ignoreCase;
    private String propertyName;

    public OrderAsInteger(String propertyName, boolean ascending) {
        super(propertyName, ascending);
        this.propertyName = propertyName;
        this.ascending    = ascending;
        this.ignoreCase   = false;
    }

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {

     // "cast({propertyName} as {type}) {ascending}";
     String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
     Type type = criteriaQuery.getTypeUsingProjection(criteria, propertyName);
     StringBuilder fragment = new StringBuilder();
     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("cast(");
         fragment.append( columns[i] );
         fragment.append(" as signed)");
         if (lower) fragment.append(')');
         fragment.append( ascending ? " asc" : " desc" );
         if ( i<columns.length-1 ) fragment.append(", ");
     }
     return fragment.toString();
    }

    /**
    * Ascending order
    *
    * @param propertyName
    * @return Order
    */
    public static Order asc(String propertyName) {
     return new OrderAsInteger(propertyName, true);
    }

    /**
    * Descending order
    *
    * @param propertyName
    * @return Order
    */
    public static Order desc(String   propertyName) {
     return new OrderAsInteger(propertyName, false);
    }
}

改良の余地は十分にあるけど、とりあえずはこれで解決。