JOOQ Cast String to Enum with Converter

Question

While looking for a way to cast my String field into an Enum i stubled across the .cast() Method. When called it throws an SQLDialectNotSupportedException.
Dialect has been Set to SQLSERVER2014 in the Context DSLContext create = DSL.using(conn, SQLDialect.SQLSERVER2014);.
The corresponding line:

create.select( ... lecture.DAY_OF_WEEK.cast(DayOfWeek.class), ... );  

The full Error:

org.jooq.exception.SQLDialectNotSupportedException: Type class java.time.DayOfWeek is not supported in dialect null
at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:944)
at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:880)
at org.jooq.impl.AbstractField.cast(AbstractField.java:256)
at de.esteam.lecturedb.jooq.Classes.Startup.getStandardExample(Startup.java:218)
at de.esteam.lecturedb.jooq.Classes.Startup.main(Startup.java:54)

I tried to achieve a conversion to the Enum with a Converter but i cannot get it running.

Is there a way to get the Converter into the cast() or is there another way to get the String into the Enum I cannot find?

Answer 1

You cannot use cast() here because that would require jOOQ to understand how to cast your data type to your custom type in SQL. What you want to do is a client side conversion, and that is achieved ideally using a Converter.

Once you have implemented your Converter, the recommended way to use it is to attach it to generated code using the code generator: https://www.jooq.org/doc/latest/manual/code-generation/custom-data-types

<forcedType>
  <userType>java.time.DayOfWeek</userType>
  <converter>com.example.YourConverter</converter>
  <includeExpression>(?i:DAY_OF_WEEK)</includeExpression>
</forcedType>

If that's not an option, you can create a "converted" field reference as follows:

// I'm assuming you're storing the data as an INTEGER
DataType<DayOfWeek> type = SQLDataType.INTEGER.asConvertedDataType(new YourConverter());
Field<DayOfWeek> field = DSL.field("{0}", type, lecture.DAY_OF_WEEK);

// And now use that instead
create.select(field)...

But I really recommend attaching the converter to the generated code for most convience.