Search code examples
oraclejpacastingeclipselinkcriteria-api

How do I insert a native Oracle call or cast (to NVARCHAR2) in a criteria api expression?

I have a complex dynamic query in Eclipselink with a case expression that involves two different columns, one of VARCHAR2 and one of NVARCHAR2.

It needs to be a case expression, because I also want to be able to sort by that result column, so I can't just query both. In Java, both are just mapped as a String, so you don't even see there's a difference.

For my query, Eclipselink creates the following select expression:

CASE
  WHEN (t9.STRINGVALUE IS NOT NULL)
  THEN t9.STRINGVALUE
  ELSE t10.OTHERSTRINGVALUE
END ,

The criteria code is:

Expression<String> str = firstRoot.get("stringValue");
Expression<String> strExp = cb.<String> selectCase().when(cb.isNotNull(str), str)
.otherwise(otherRoot.<String> get("otherStringValue"));
q.multiselect(..., strExp, ...);

which causes Oracle to fail with ORA-12704: character set mismatch. I'd like to modify the code to result in

cast(t10.OTHERSTRINGVALUE as NVARCHAR2(50),

but I cannot find out how.

I tried a converter on the Entity's field, or a .as(String.class) on the .get()-expressions for both fields.

So the question: is there a way to pass an Oracle type like NVARCHAR2 to the .as() expression? Can I otherwise insert a call to CAST(... as NVARCHAR2) with criteria API? Is there any other way to have it generate custom SQL, because I REALLY cannot rewrite the whole query, just because JPA or EL don't provide for the possibility that you might need some custom SQL...

Solution

  • The only way to do it in criteria API is to create a new PathImpl from the otherRoot. get("otherStringValue") path, passing in an EclipseLink native cast expression as the expression node. something like:

    PathImpl path = (PathImpl)otherRoot.<String> get("otherStringValue");
Path castPath = new PathImpl(path, em.getMetamodel(), path.getJavaType(), path.getCurrentNode().cast("NVARCHAR2"), path.getModel());

Expression<String> str = firstRoot.get("stringValue");
Expression<String> strExp = cb.<String> selectCase().when(cb.isNotNull(str), str)
.otherwise(castPath );
q.multiselect(..., strExp, ...);