http://raywithu.iteye.com/blog/1139332 转载请注明出处
最近使用mysql做一个交易网站,使用hibernate作为持久化框架。
当我使用hibernate的Order进行排序的时候,杯具发生了。中文给我乱排了。
mysql中如果需要正常按照中文排序,其中一种处理方法是
- SELECT *
- FROM BZ_COMPANY
- ORDER 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;
- @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);
- }
- }
使用例子:
- 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