解决Hibernate中MySQL的中文排序
最近使用mysql做一个交易网站，使用hibernate作为持久化框架。
当我使用hibernate的Order进行排序的时候，杯具发生了。中文给我乱排了。
mysql中如果需要正常按照中文排序，其中一种处理方法是
?
SELECT *FROM BZ_COMPANYORDER BY CONVERT( COMPANY_NAME USING GBK ) ASC
?
?
可问题是这样就脱离hibernate了。本打算使用QBC做一些公共的方法的。
然后就去看了下hibernate中Order的实现。
?
hibernate的Order：
?
//$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;@Overridepublic String toString() {return "CONVERT( " + propertyName + " USING " + encoding + " ) " + (ascending ? "asc" : "desc");}@Overridepublic 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 * */@Overridepublic 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);}}?
?
使用例子：
?
public PageControl findPage(final PageControl pageControl, final Object bean) {try {final Class clazz = bean.getClass();List data = (List) this.getHibernateTemplate().execute(new HibernateCallback() {public Object doInHibernate(Session session) throws HibernateException, SQLException {Criteria criteria = session.createCriteria(clazz);criteria.add(Example.create(bean).ignoreCase().enableLike(MatchMode.ANYWHERE));if (pageControl != null) {int start = pageControl.getStart();int limit = pageControl.getLimit();criteria.setFirstResult(start);criteria.setMaxResults(limit);String sort = pageControl.getSort();String dir = pageControl.getDir();if (sort != null && dir != null) {criteria.addOrder(dir.equalsIgnoreCase("ASC") ? GBKOrder.asc(sort) : GBKOrder.desc(sort));}}return criteria.list();}});Long totalCount = getTotalCount(bean);PageControl rt = new PageControl();rt.setData(data);rt.setTotalCount(totalCount);return rt;} catch (RuntimeException e) {log.error("find page failed", e);throw e;}}当然,你有需要的话,不必写死GBK
?
附上效果图：
http://raywithu.iteye.com/admin/blogs/1139332
?
?
