jpa注册函数（实现oracle/mysql的中文排序 spring-boot环境）

一、搭建相关的项目环境

二、创建类继承 org.hibernate.dialect.MySQL5InnoDBDialect（mysql）或者org.hibernate.dialect.Oracle12cDialect（oracle）

1 public class MySQLChineseDialect extends MySQL5InnoDBDialect {
2
3     //mysql 使JPQL支持中文排序   
4     public MySQLChineseDialect() {
5         super();
6         registerFunction("convert",new SQLFunctionTemplate(StringType.INSTANCE,"convert(?1 using gbk)"));
7     }
8 }

1 public class OracleChineseDialect extends Oracle12cDialect {
2
3     //oracle  使JPQL支持中文排序    
4     public OracleChineseDialect(){
5         super();
6         registerFunction("convert_gbk", new SQLFunctionTemplate(StandardBasicTypes.STRING, "nlssort(?1,'NLS_SORT=SCHINESE_PINYIN_M')"));
7     }
8
9 }

三、在yml或者properties配置文件加入以下配置，使用我们自定义方言类（重点）

 1 spring:
 2   datasource:
 3     driver-class-name: com.mysql.jdbc.Driver
 4     url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?characterEncoding=utf8
 5     username: root
 6     password: root
 7 #    driver-class-name: oracle.jdbc.driver.OracleDriver
 8 #    url: jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:scott
 9 #    username: scott
10 #    password: scott
11   jpa:
12     properties:
13       hibernate:
14         dialect: com.cn.MySQLChineseDialect
15 #     properties:
16 #       hibernate:
17 #         dialect: com.cn.OracleChineseDialect

四、使用

//mysql环境
    @Query("select o from Organizations o where o.code like :code and o.status in :status and o.belongTo = :findid and (o.department like :name or o.shortcut like :name) order by function('convert',o.department) desc ")
    Page<Organizations> findOrgNoZiMysqldesc(Pageable of,@Param("code") String code,@Param("status")List<Integer> status,@Param("findid")String findid,@Param("name")String name);

//oracle环境
    @Query("select o from Organizations o where o.code like :code and o.status in :status and o.belongTo = :findid and (o.department like :name or o.shortcut like :name) order by function('convert_gbk',o.department) desc")
    Page<Organizations> findOrgNoZiOracledesc(Pageable of,@Param("code") String code,@Param("status")List<Integer> status,@Param("findid")String findid,@Param("name")String name);

说明：

目前我试过几次之后发现无法动态修改传入的参数，也不能在Sort对象使用convert函数，只能够支持在JPQL中写死，如果你们能够发现可以动态修改参数的话，希望留个言，指导一下。