Mysql在使用utf-8字符集的时候,不能正确的以中文进行排序.
所以得强制mysql进行中文排序
select * from table ORDER BY CONVERT(column USING gbk);
但是hql并不支持 CONVERT这个语法.所以得改变hibernate的方言(hibernate.dialect)将它设置为我们自定义的dialect.
package com.sangame.ds.ws.util;
import org.hibernate.Hibernate;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
/**
* Created by Administrator on 2017/7/27.
*/
public class MySQLExtendDialect extends MySQLDialect {
public MySQLExtendDialect(){
super();
registerFunction("convert_gbk",
new SQLFunctionTemplate(Hibernate.STRING, "convert(?1 using gbk)") );
}
}
需要创建一个类去继承MySQLDialect,在无参构造器中使用registerFunction函数,传入第一个参数,用于指定hql可识别的函数.第二参数用于指定函数的功能,这里就可以指定mysql可以
识别的语句,并将它赋予给自定义的hql函数. 这里convert_gbk = convert(?1 using gbk)了(?1为占位符)
创建好Dialect类后,就要指定hibernate的默认dialect为该类了.
这时就可以在hql中使用convert_gbk函数来替代mysql中的CONVERT函数实现中文排序了.hibernate.dialect=com.sangame.ds.ws.util.MySQLExtendDialect
public Page<HashMap> findPlauLogPage(TerminalPlayLogVo search, int currentPage, int pageSize) { StringBuilder hql = new StringBuilder(); hql.append("select new map(itemName as itemName,terminal.terminalGroup.id as terminalGroupId,terminal.terminalName as terminalName,count(*)as playCount) from TerminalPlayLog WHERE 1 = 1"); Long tid = null; Long gid = null; String itemName = null; String terminalName = null; Date playBeginTime =null; Date playEndTime = null; if (search != null) { if (search.getTerminal() != null && search.getTerminal().getId() != null) { hql.append(" and terminal.id = :tid"); tid = search.getTerminal().getId(); } if (search.getTerminal() != null && search.getTerminal().getTerminalGroup() != null && search.getTerminal().getTerminalGroup().getId() != null) { hql.append(" and terminal.terminalGroup.id = :gid"); gid = search.getTerminal().getTerminalGroup().getId(); } if (StringUtils.isNotBlank(search.getItemName())) { hql.append(" and itemName like :itemName"); itemName ="%" + search.getItemName() + "%"; } if (search.getTerminal() != null && StringUtils.isNotBlank(search.getTerminal().getTerminalName()) ) { hql.append(" and terminal.terminalName like :terminalName"); terminalName ="%" + search.getTerminal().getTerminalName() + "%"; } if (search.getPlayBeginTime() != null) { hql.append(" and playBeginTime >= :playBeginTime"); playBeginTime = search.getPlayBeginTime(); } if (search.getPlayEndTime() != null) { hql.append(" and playEndTime <= :playEndTime"); playEndTime = search.getPlayEndTime(); } } hql.append(" GROUP BY itemName,terminal.terminalName,terminal.terminalGroup.id order by convert_gbk(itemName) ASC");