Criteria API with ODER BY CASE expression throws SQLException: ORA-12704 "character set mismatch"

Question

I am using criteria API to create my query. Because of special sorting algorithm I use an "order by case" expression. My Unit-Tests using in memory H2 DB and are working. In the development stage we are using Oracle DB and there I get an "SQLException: ORA-12704" when executing the query.

Assume my root entity 'Foo' has a Set of 'Bar's. Bar has an attribute 'myOrderByColumn'

public class Bar {
    ...

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

    ...
}

Here is the code which produces the exception. It creates the Order object later used in CriteriaQuery.orderBy(..)

   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(Bars.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);
   }

I took a look into the Oracle DB. The type of the column 'myOrderByColumn' ist NVARCHAR2(255).

I guess the problem here ist that the "when" part in the SQL query must match with the type of the 'MYORDERBYCOL' database column, which is NVARCHAR2. In Java I use Strings. Probably Hibernate is not casting this correctly!?

I can produce the database ORA-12704 error by

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

This SQL works

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

How do I have to adjust my oder by case expression with criteria API so that the query is working with any database? (must later work with at least H2, Oracle, MS SQL, PostgreSQL)

Answer 1

Looks like an Oracle issue to me. Which version are you using? You can try a different approach which might work.

  CriteriaBuilder.SearchedCase<Integer> caseRoot = cb.selectCase();

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

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