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

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

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

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

 

Sql代码  

1. SELECT *   
2. FROM BZ_COMPANY   
3. ORDER BY CONVERT( COMPANY_NAME USING GBK ) ASC  

SELECT *
FROM BZ_COMPANY
ORDER BY CONVERT( COMPANY_NAME USING GBK ) ASC

 

 

可问题是这样就脱离hibernate了。本打算使用QBC做一些公共的方法的。

然后就去看了下hibernate中Order的实现。

 

hibernate的Order：

 

Java代码  

1. //$Id: Order.java,v 1.1 2011/05/29 18:11:15 Surui Exp $   
2. package org.hibernate.criterion;   
3.   
4. import java.io.Serializable;   
5. import java.sql.Types;   
6.   
7. import org.hibernate.Criteria;   
8. import org.hibernate.HibernateException;   
9. import org.hibernate.engine.SessionFactoryImplementor;   
10. import org.hibernate.type.Type;   
11.   
12. /**  
13.  * Represents an order imposed upon a <tt>Criteria</tt> result set  
14.  * @author Gavin King  
15.  */  
16. public class Order implements Serializable {   
17.   
18.     private boolean ascending;   
19.     private boolean ignoreCase;   
20.     private String propertyName;   
21.        
22.     public String toString() {   
23.         return propertyName + ' ' + (ascending?"asc":"desc");   
24.     }   
25.        
26.     public Order ignoreCase() {   
27.         ignoreCase = true;   
28.         return this;   
29.     }   
30.   
31.     /**  
32.      * Constructor for Order.  
33.      */  
34.     protected Order(String propertyName, boolean ascending) {   
35.         this.propertyName = propertyName;   
36.         this.ascending = ascending;   
37.     }   
38.   
39.     /**  
40.      * Render the SQL fragment  
41.      *  
42.      */  
43.     public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)    
44.     throws HibernateException {   
45.         String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);   
46.         Type type = criteriaQuery.getTypeUsingProjection(criteria, propertyName);   
47.         StringBuffer fragment = new StringBuffer();   
48.         for ( int i=0; i<columns.length; i++ ) {   
49.             SessionFactoryImplementor factory = criteriaQuery.getFactory();   
50.             boolean lower = ignoreCase && type.sqlTypes( factory )[i]==Types.VARCHAR;   
51.             if (lower) {   
52.                 fragment.append( factory.getDialect().getLowercaseFunction() )   
53.                     .append('(');   
54.             }   
55.             fragment.append( columns[i] );   
56.             if (lower) fragment.append(')');   
57.             fragment.append( ascending ? " asc" : " desc" );   
58.             if ( i<columns.length-1 ) fragment.append(", ");   
59.         }   
60.         return fragment.toString();   
61.     }   
62.   
63.     /**  
64.      * Ascending order  
65.      *  
66.      * @param propertyName  
67.      * @return Order  
68.      */  
69.     public static Order asc(String propertyName) {   
70.         return new Order(propertyName, true);   
71.     }   
72.   
73.     /**  
74.      * Descending order  
75.      *  
76.      * @param propertyName  
77.      * @return Order  
78.      */  
79.     public static Order desc(String propertyName) {   
80.         return new Order(propertyName, false);   
81.     }   
82.   
83. }  

//$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：

 

Java代码  

1. package comm;   
2.   
3. import java.sql.Types;   
4.   
5. import org.hibernate.Criteria;   
6. import org.hibernate.HibernateException;   
7. import org.hibernate.criterion.CriteriaQuery;   
8. import org.hibernate.criterion.Order;   
9. import org.hibernate.engine.SessionFactoryImplementor;   
10. import org.hibernate.type.Type;   
11.   
12. public class GBKOrder extends Order {   
13.     private String encoding = "GBK";   
14.     private boolean ascending;   
15.     private boolean ignoreCase;   
16.     private String propertyName;   
17.   
18.     @Override  
19.     public String toString() {   
20.         return "CONVERT( " + propertyName + " USING " + encoding + " ) " + (ascending ? "asc" : "desc");   
21.     }   
22.   
23.     @Override  
24.     public Order ignoreCase() {   
25.         ignoreCase = true;   
26.         return this;   
27.     }   
28.   
29.     /**  
30.      * Constructor for Order.  
31.      */  
32.     protected GBKOrder(String propertyName, boolean ascending) {   
33.         super(propertyName, ascending);   
34.         this.propertyName = propertyName;   
35.         this.ascending = ascending;   
36.     }   
37.   
38.     /**  
39.      * Constructor for Order.  
40.      */  
41.     protected GBKOrder(String propertyName, String dir) {   
42.         super(propertyName, dir.equalsIgnoreCase("ASC") ? true : false);   
43.         ascending = dir.equalsIgnoreCase("ASC") ? true : false;   
44.         this.propertyName = propertyName;   
45.         this.ascending = ascending;   
46.     }   
47.   
48.     /**  
49.      * Render the SQL fragment  
50.      *   
51.      */  
52.     @Override  
53.     public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {   
54.         String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);   
55.         Type type = criteriaQuery.getTypeUsingProjection(criteria, propertyName);   
56.         StringBuffer fragment = new StringBuffer();   
57.         for (int i = 0; i < columns.length; i++) {   
58.             SessionFactoryImplementor factory = criteriaQuery.getFactory();   
59.             boolean lower = ignoreCase && type.sqlTypes(factory)[i] == Types.VARCHAR;   
60.             if (lower) {   
61.                 fragment.append(factory.getDialect().getLowercaseFunction()).append('(');   
62.             }   
63.             fragment.append("CONVERT( " + columns[i] + " USING " + encoding + " )");   
64.             if (lower)   
65.                 fragment.append(')');   
66.             fragment.append(ascending ? " asc" : " desc");   
67.             if (i < columns.length - 1)   
68.                 fragment.append(", ");   
69.         }   
70.         return fragment.toString();   
71.     }   
72.   
73.     /**  
74.      * Ascending order  
75.      *   
76.      * @param propertyName  
77.      * @return Order  
78.      */  
79.     public static Order asc(String propertyName) {   
80.         return new GBKOrder(propertyName, true);   
81.     }   
82.   
83.     /**  
84.      * Descending order  
85.      *   
86.      * @param propertyName  
87.      * @return Order  
88.      */  
89.     public static Order desc(String propertyName) {   
90.         return new GBKOrder(propertyName, false);   
91.     }   
92. }  

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