jpa 中文排序

定义mysql函数和关键字

package com.htaw.cs.base.common.contents;

import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Component;
@Component
public class MySQLChineseDialect extends MySQL5Dialect {
    /**
     * 使mysql支持中文排序(mysql版本5.7)
     */
    public MySQLChineseDialect(){
        super();
        registerFunction("convert", new SQLFunctionTemplate(StandardBasicTypes.STRING, "CONVERT(?1 using GBK)"));
        registerKeyword("using");
        registerKeyword("USING");
        registerKeyword("GBK");
        registerKeyword("gbk");
    }
}

添加jpa配置

spring:
	jpa:
		properties:
            hibernate:
                dialect: com.htaw.cs.base.common.contents.MySQLChineseDialect

查询时添加排序

public Specification getSpecification() {
    Specification<LabelPo> specification = (root, query, criteriaBuilder) -> {
    List<Predicate> list = new ArrayList<>();
	Expression<String> convertRealnameFunction = criteriaBuilder.function("convert", String.class, root.get("name"));
            Order nameAsc = criteriaBuilder.asc(convertRealnameFunction);
            query.orderBy(nameAsc );
            return criteriaBuilder.and(list.toArray(new Predicate[0]));
        };
        return specification;
    }