最近使用mysql做一个交易网站,使用hibernate作为持久化框架。
当我使用hibernate的Order进行排序的时候,杯具发生了。中文给我乱排了。
mysql中如果需要正常按照中文排序,其中一种处理方法是
Sql代码
SELECT *
FROM BZ_COMPANY
ORDER BY CONVERT( COMPANY_NAME USING GBK ) ASC
可问题是这样就脱离hibernate了。本打算使用QBC做一些公共的方法的。
然后就去看了下hibernate中Order的实现。
hibernate的Order:
Java代码
//$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 Criteria 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
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
}
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代码
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
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
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);
}
}
使用例子:
Java代码
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