How to Convert Optional<String> to a Fixed UUID Value for Nulls in jOOQ During INSERT Operations?

I am working with a database that handles null values very poorly. Instead of using nullable strings, I want to use a fixed, very rare value (essentially a UUID) as a placeholder for null.

My goal is to use Optional instead of String for fields in Java, and whenever I bind an Optional.empty() to an INSERT INTO statement, I want jOOQ to automatically convert this to my UUID that I use as a flag for an empty optional. When the optional has a value, I want jOOQ to use the provided value as usual.

I tried setting up a converter, but it only seems to work during SELECT operations and not during the insertion phase. I suspect I might be missing something.

Here is what I have so far:

package com.res.jooq.neo4j;

public class Neo4jOptionalUtils {

    public static final String EMPTY_STRING = "empty-string-optional-ac4ea8cd-9128-4ee3-bb2c-48a1c239ffd2";

    private Neo4jOptionalUtils() {
        // private constructor to hide the implicit public one
    }

    public static String optional(String value) {
        return value == null ? EMPTY_STRING : value;
    }

    public static String fromOptional(String optional) {
        return EMPTY_STRING.equals(optional) ? null : optional;
    }
}

I am using this utility class to convert null values to my fixed UUID and vice versa.

How can I configure jOOQ to apply this conversion automatically during INSERT operations?

Is there a way to ensure that both SELECT and INSERT operations respect this conversion logic without manually converting each value?

EDIT: thanks to Lukas' answer I managed to get it done, here a gist https://gist.github.com/bonelli/9f3c24a30218be71c26a335c0ab17cc1

Solution

So, this question seems to assume that there's a difference between using Field<U> references in INSERT or SELECT (where U is a converted user-defined type):

I tried setting up a converter, but it only seems to work during SELECT operations and not during the insertion phase. I suspect I might be missing something.

How can I configure jOOQ to apply this conversion automatically during INSERT operations?

The answer here is that no, you don't have to do anything special. Converted fields work everywhere, out of the box. You probably simply didn't use the converted field. Assuming you're using jOOQ's code generator, just use the field everywhere:

List<MyType> list =
ctx.select(T.CONVERTED)
   .from(T)
   .fetch(T.CONVERTED);

ctx.insertInto(T)
   .columns(T.CONVERTED)
   .values(new MyType()) // Works out of the box
   .execute();

I'll be happy to answer a follow-up question about what you did specifically, to help you find where you went wrong.