1 使用criteria排序:
需要写一个GbkOrder类添加到当前项目中,继承Order,并重写,criteria.addOrder(GBKOrder.asc(“name”));
order源码:
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的,所以只要在这里做手脚,就能达到效果
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);
}
}
2 使用sql排序:
select * from user order by convert(name useing gbk)
3 使用hql排序
需要写一个MySQL5LocalDialect 类添加到当前项目中,继承MySQL5Dialect ,并重写,
import org.hibernate.dialect.MySQL57InnoDBDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StringType;
public class MySQLLocalDialect extends MySQL57InnoDBDialect {
public MySQLLocalDialect(){
super();
registerFunction("convert",new SQLFunctionTemplate(StringType.INSTANCE, "convert(?1 using ?2)") );
}
}
修改配置使用自己的方言
<bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<property name="configLocations" value="classpath:hibernate.cfg.xml"></property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
<prop key="hibernate.dialect">com.lyf.MySQLLocalDialect </prop>
</props>
</property>
</bean>
使用:“ from User order by convert(name,‘GBK’)”