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

?

?

?