Criteria API的ORDER BY CASE触发ORA-12704字符集不匹配问题

问题背景 #

使用Criteria API构建带特殊排序逻辑的查询，通过ORDER BY CASE表达式实现。单元测试在H2内存数据库中正常运行，但在Oracle环境执行时抛出SQLException: ORA-12704错误。

实体类定义 #

根实体Foo包含Bar集合，Bar类的目标排序字段定义如下：

public class Bar {
    ...

    @NotBlank
    @javax.validation.constraints.Size(max = 255)
    @Column(name = "MYORDERBYCOL")
    private java.lang.String myOrderByColumn;

    ...
}

出错的Criteria代码 #

用于生成Order对象的代码如下：

private Order buildOrderBy(final CriteriaBuilder cb,
         final Root<Foo> rootEntity, 
         final List<String> somehowSpecialOrderedList) {
            
      final Expression<String> orderByColumn =
            rootEntity.join(Foo_.bars, JoinType.LEFT).get(Bar_.myOrderByColumn);

      CriteriaBuilder.SimpleCase<String, Integer> caseRoot = cb.selectCase(orderByColumn);

      IntStream.range(0, somehowSpecialOrderedList.size())
            .forEach(i -> caseRoot.when(somehowSpecialOrderedList.get(i), i));

      final Expression<Integer> selectCase = caseRoot.otherwise(Integer.MAX_VALUE);

      return cb.asc(selectCase);
}

问题原因分析 #

Oracle数据库中MYORDERBYCOL列类型为NVARCHAR2(255)，但Hibernate生成的SQL中CASE条件值是普通字符串（如'20'），未与NVARCHAR2类型匹配，导致字符集不兼容触发ORA-12704错误。直接执行未转换类型的SQL会报错，将条件值转为NVARCHAR2后可正常运行：

错误SQL：

SELECT FOO.id
FROM FOO
LEFT OUTER JOIN BAR ON FOO.id = BAR.fk_id
ORDER BY
   CASE BAR.MYORDERBYCOL
      WHEN '20' THEN 1
      ELSE           2
   END ASC;

正确SQL：

SELECT FOO.id
FROM FOO
LEFT OUTER JOIN BAR ON FOO.id = BAR.fk_id
ORDER BY
   CASE BAR.MYORDERBYCOL
      WHEN cast('20' as NVARCHAR2(255)) 
      THEN 1
      ELSE           2
   END ASC;

兼容多数据库的调整方案 #

方案1：显式转换匹配值类型（通用方法） #

使用CriteriaBuilder的cast方法，将每个匹配的字符串值转换为与目标列一致的类型。Hibernate会根据数据库方言生成对应的类型转换语法，兼容Oracle、SQL Server、PostgreSQL等主流数据库。

修改后的代码：

private Order buildOrderBy(final CriteriaBuilder cb,
         final Root<Foo> rootEntity, 
         final List<String> somehowSpecialOrderedList) {
            
      final Expression<String> orderByColumn =
            rootEntity.join(Foo_.bars, JoinType.LEFT).get(Bar_.myOrderByColumn);

      CriteriaBuilder.SimpleCase<String, Integer> caseRoot = cb.selectCase(orderByColumn);

      IntStream.range(0, somehowSpecialOrderedList.size())
            .forEach(i -> {
                // 将字符串值转为与orderByColumn相同类型的表达式
                Expression<String> matchedValue = cb.literal(somehowSpecialOrderedList.get(i));
                matchedValue = cb.cast(matchedValue, orderByColumn.getJavaType());
                caseRoot.when(matchedValue, i);
            });

      final Expression<Integer> selectCase = caseRoot.otherwise(Integer.MAX_VALUE);

      return cb.asc(selectCase);
}

方案2：利用Hibernate类型接口指定类型（Hibernate专属） #

如果项目以Hibernate作为JPA实现，可以显式指定字符串类型为NVARCHAR，确保Oracle方言生成正确的类型转换逻辑：

import org.hibernate.type.StringNVarcharType;

// ...

private Order buildOrderBy(final CriteriaBuilder cb,
         final Root<Foo> rootEntity, 
         final List<String> somehowSpecialOrderedList) {
            
      final Expression<String> orderByColumn =
            rootEntity.join(Foo_.bars, JoinType.LEFT).get(Bar_.myOrderByColumn);

      CriteriaBuilder.SimpleCase<String, Integer> caseRoot = cb.selectCase(orderByColumn);

      IntStream.range(0, somehowSpecialOrderedList.size())
            .forEach(i -> {
                String value = somehowSpecialOrderedList.get(i);
                // 显式指定NVARCHAR类型
                Expression<String> typedValue = cb.literal(value, StringNVarcharType.INSTANCE);
                caseRoot.when(typedValue, i);
            });

      final Expression<Integer> selectCase = caseRoot.otherwise(Integer.MAX_VALUE);

      return cb.asc(selectCase);
}

方案3：数据库方言适配（复杂场景可选） #

如果通用方案无法满足需求，可以自定义Hibernate方言，重写CASE表达式的处理逻辑，自动为NVARCHAR列添加类型转换。此方法复杂度较高，仅在特殊场景下考虑。

内容的提问来源于stack exchange，提问作者Chris Brown