hibernate中的createCretia实现中文排序（转）

最近使用mysql做一个交易网站，使用hibernate作为持久化框架。

当我使用hibernate的Order进行排序的时候，杯具发生了。中文给我乱排了。

mysql中如果需要正常按照中文排序，其中一种处理方法是

//$Id: Order.java,v 1.1 2011/05/29 18:11:15 Surui Exp $   

package org.hibernate.criterion;   

  

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);   

    }   
  
}  

//$Id: Order.java,v 1.1 2011/05/29 18:11:15 Surui Exp $
package org.hibernate.criterion;

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的，所以只要在这里做手脚，就能达到效果。

当然，不赞成直接改源码。

然后就有了GBKOrder：

package comm;   
  

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);   

    }   
}  

package comm;

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);
	}
}