文字を数字としてソートする
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); } }
改良の余地は十分にあるけど、とりあえずはこれで解決。