Hibernate使用中文排序.

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为该类了.   

hibernate.dialect=com.sangame.ds.ws.util.MySQLExtendDialect
这时就可以在hql中使用convert_gbk函数来替代mysql中的CONVERT函数实现中文排序了.
 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");