sql语句对中文排序

1 使用criteria排序：

需要写一个GbkOrder类添加到当前项目中，继承Order，并重写，criteria.addOrder(GBKOrder.asc(“name”));

order源码：

import java.io.Serializable;  
import java.sql.Types;  
  
import org.hibernate.Criteria;  
import org.hibernate.HibernateException;  
import org.hibernate.engine.SessionFactoryImplementor;  
import org.hibernate.type.Type;  
  
/** 
 * Represents an order imposed upon a <tt>Criteria</tt> result set 
 * @author Gavin King 
 */  
public class Order implements Serializable {  
  
    private boolean ascending;  
    private boolean ignoreCase;  
    private String propertyName;  
      
    public String toString() {  
        return propertyName + ' ' + (ascending?"asc":"desc");  
    }  
      
    public Order ignoreCase() {  
        ignoreCase = true;  
        return this;  
    }  
  
    /** 
     * Constructor for Order. 
     */  
    protected Order(String propertyName, boolean ascending) {  
        this.propertyName = propertyName;  
        this.ascending = ascending;  
    }  
  
    /** 
     * Render the SQL fragment 
     * 
     */  
    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( columns[i] );  
            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 Order(propertyName, true);  
    }  
  
    /** 
     * Descending order 
     * 
     * @param propertyName 
     * @return Order 
     */  
    public static Order desc(String propertyName) {  
        return new Order(propertyName, false);  
    }  
  
}  

重点就在toSqlString上了，QBC的Criteria也是toSqlString产生对应sql的，所以只要在这里做手脚，就能达到效果

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 GBKOrder extends Order {  
    private String encoding = "GBK";  
    private boolean ascending;  
    private boolean ignoreCase;  
    private String propertyName;  
  
    @Override  
    public String toString() {  
        return "CONVERT( " + propertyName + " USING " + encoding + " ) " + (ascending ? "asc" : "desc");  
    }  
  
    @Override  
    public Order ignoreCase() {  
        ignoreCase = true;  
        return this;  
    }  
  
    /** 
     * Constructor for Order. 
     */  
    protected GBKOrder(String propertyName, boolean ascending) {  
        super(propertyName, ascending);  
        this.propertyName = propertyName;  
        this.ascending = ascending;  
    }  
  
    /** 
     * Constructor for Order. 
     */  
    protected GBKOrder(String propertyName, String dir) {  
        super(propertyName, dir.equalsIgnoreCase("ASC") ? true : false);  
        ascending = dir.equalsIgnoreCase("ASC") ? true : false;  
        this.propertyName = propertyName;  
        this.ascending = ascending;  
    }  
  
    /** 
     * Render the SQL fragment 
     *  
     */  
    @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] + " USING " + encoding + " )");  
            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 GBKOrder(propertyName, true);  
    }  
  
    /** 
     * Descending order 
     *  
     * @param propertyName 
     * @return Order 
     */  
    public static Order desc(String propertyName) {  
        return new GBKOrder(propertyName, false);  
    }  
}  

2 使用sql排序：

select * from user order by convert（name useing gbk）

3 使用hql排序

需要写一个MySQL5LocalDialect 类添加到当前项目中，继承MySQL5Dialect ，并重写，

import org.hibernate.dialect.MySQL57InnoDBDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StringType;
 
public class MySQLLocalDialect extends MySQL57InnoDBDialect {
 
    public MySQLLocalDialect(){
        super();
        registerFunction("convert",new SQLFunctionTemplate(StringType.INSTANCE, "convert(?1 using ?2)") );
    }
}
 

修改配置使用自己的方言

<bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"></property>
    <property name="configLocations" value="classpath:hibernate.cfg.xml"></property>
	<property name="hibernateProperties">
		<props>
             <prop key="hibernate.show_sql">true</prop>
             <prop key="hibernate.format_sql">true</prop>
             <prop key="hibernate.hbm2ddl.auto">update</prop>
             <prop key="hibernate.dialect">com.lyf.MySQLLocalDialect </prop>
         </props>
	</property>
</bean>

使用：“ from User order by convert(name,‘GBK’)”